Oracle Client Result Cache

This post describes the Oracle Client Result Cache, which allows OCI clients to cache query results in local memory. This feature is also known as the Client Query Cache, Client Side Query Cache, and the OCI Consistent Client Cache.  The feature was first introduced in Oracle 11g.

Earlier I talked about the Server Result Cache.  It can cache the resulting rowsets from queries and function calls in a central location to be reused by all clients.  Here, the Client Results Cache stores the resulting rowsets locally where they can be reused only by that client.  That's the down side.  On the up side, all sessions managed by the given client can access the cache.

A benefit of the Client Results Cache over the Server Result Cache is it avoids network calls.  The data is cached locally.  When using the Server Result Cache the client must put in a call over the network and wait for the data to be returned over the network.

The Client Result Cache is a modification to the OCI stack that allows clients to store query results in local memory (in the client’s process memory space). If the same query is repeated within “lag milliseconds”, then the results can be fetched from local memory rather than going back to the database and waiting to re-execute the query.  Consider the following type of query: “select all flights from airport DCA to MCO tomorrow between 4 PM and 7 PM”.  This same query might be run dozens of times a day.

The feature might be more useful with lookup tables than data tables.  Many applications have fixed queries against lookup tables, and each transaction might run one of those queries.  The lookup table queries might be the bulk of what gets cached by the client.

Before using this feature you must satisfy three prerequisites: the database must be Enterprise Edition, the client must use Oracle Call Interface (OCI), and you must enable caching in your OCI application.

Enabling the Oracle Client Result Cache requires just one database initialization parameter:   CLIENT_RESULT_CACHE_SIZE.  This mandatory parameter enables / disables the feature and specifies a maximum cache size.  The default value 0 means there is no cache.  Specify a non-zero value like 1G to enable caching, and this will be the default cache size used by all OCI clients.  Each client can override this by setting OCI_RESULT_CACHE_MAX_SIZE in sqlnet.ora.

Therefore, to enable the Oracle Client Result Cache, just allocate it some memory and bounce the instance:

ALTER SYSTEM SET CLIENT_RESULT_CACHE_SIZE=1024M SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

This parameter is static, which means you cannot change it at run time.  You must set it to the SPFILE and bounce the instance.

You can set the parameter at the PDB level.  That makes sense, because most PDB map to different application clients.

There are two optional database initialization parameters you should know about.  They are tuning parameters and have default values, so they are not required for enabling or using the feature.

  • CLIENT_RESULT_CACHE_LAG defines the maximum time in milliseconds since the last round trip to the server before the client makes another round trip to pick-up changes.  The default value 3000 means "every 3 seconds".
  • RESULT_CACHE_MODE defines which query result sets are cached.  The default value MANUAL tells all OCI clients to cache the results of any query that includes hint /*+ RESULT_CACHE */.  To prevent caching results, just don't add the hint to your queries.  Setting this parameter to FORCE tells OCI clients to cache all query results whether or not the hint was included.

Pleases note parameter RESULT_CACHE_MODE is used by both the Client Result Cache and Server Result Cache features, so be careful when setting it as it effects the behavior of both caching solutions.

Each init.ora parameter has two overrides that can be set at the client level.  Oracle first reads the database parameter, then overrides it with any value set in the client's sqlnet.ora, and then overrides it with any value set in the clients oraaccess.xml.

The first layer of overrides can optionally be set for each client like a WebLogic server in file sqlnet.ora ($ORACLE_HOME/network/admin/sqlnet.ora).  This file applies to all clients on the computer who are using the given Oracle Home software.  There is a separate sqlnet.ora in each Oracle Home.  Values set in this file override the database initialization parameter settings.

  • OCI_RESULT_CACHE_MAX_SIZE - this optional parameter is used to override the global value set by database init.ora parameter CLIENT_RESULT_CACHE_SIZE.  The minimum allowed size is 32K and the maximum allowed size is 2G.
  • OCI_RESULT_CACHE_MAX_RSET_SIZE - this optional parameter defines the maximum size of a cached rowset.  Each rowset can be up to this size.  If omitted, then it's unlimited.
  • OCI_RESULT_CACHE_MAX_RSET_ROWS - this optional parameter defines the maximum number of rows in a cached rowset.  Each rowset can have up to this many rows.  If omitted, then it's unlimited.

The second layer of overrides can optionally be set in configuration file oraaccess.xml ($ORACLE_HOME/network/admin/oraaccess.xml).   These parameters override equivalent parameters set in sqlnet.ora.

  • MAX_SIZE - this parameter override sqlnet.ora parameter OCI_RESULT_CACHE_MAX_SIZE.  The minimum allowed size is 32K and the maximum allowed size is 2G.
  • MAX_RSET_SIZE - this parameter determines the maximum size of any one rowset to be cached.  If omitted, then it defaults to the value set by sqlnet.ora parameter OCI_RESULT_CACHE_MAX_RSET_SIZE.
  • MAX_RSET_ROWS - this optional parameter determines the maximum number of rows in any one rowset that can be cached.  If omitted, then it defaults to the value set by sqlnet.ora parameter OCI_RESULT_CACHE_MAX_RSET_ROWS.

Note some parameter names repeat in file oraaccess.xml and we are only talking about the ones in section <result_cache>.  The section of the file we are editing looks like this but with your values:

<result_cache>
      <max_rset_rows>10000</max_rset_rows>
      <max_rset_size>64M</max_rset_size>
      <max_size>640M</max_size>
</result_cache>

All of the parameters in sqlnet.ora and oraaccess.xml are applied per-process.  If you set the max size to 2 GB have 10 processes, then total memory used on your client can be 20 GB.

That's basically it for enabling and configuring the Oracle Client Result Cache.

To monitor the Oracle Client Result Cache, use the Oracle view CLIENT_RESULT_CACHE_STATS$.  

There are quite a number of views available, so refer to the Oracle Docs (ref). 

Comments

Post a Comment

Popular posts from this blog

Using DBGen to Generate TPC-H Test Data

Image
In this post I describe the Data Base Generator (DBGen) utility in terms of what it is, how to install it, and how to use it.   This post assumes a Linux operating system.   There are different build procedures for DBGen on Windows, but the concepts carry over. DBGen is used to create a TPC-H schema (i.e., it provides the CREATE TABLE statements) and to generate the data.   DBGen is used in official TPC-H benchmarking, but it can also be used in informal TPC-H-like benchmarking.   The schema and data is compatible with most 3 rd party benchmarking tools like HammerDB, so even if you plan to run a TPC-H-like test using HammerDB you may still use DBGen to create the schema and data. The  CREATE TABLE   statements are ANSI standard SQL and can be run in any DBMS without editing.   There are no SQL statements for indexes or foreign key constraints provided by DBGen since those types of objects are not required.   The data is created as delimited flat files, with one file for each of
Read more

Oracle Database Smart Flash Cache (DSFC)

The Oracle Database Smart Flash Cache solution, or simply DSFC, accelerates primarily OLTP workloads by caching frequently accessed blocks in a hybrid memory area that spans from the Oracle database buffer cache to one or more flash memory storage devices.  The feature is recommended for any Oracle database stored entirely on disk, which is well known for higher wait times and lower throughput than flash. This feature is only available on Oracle branded operating systems including Oracle Linux and Solaris.   It does not require the Oracle branded kernel known as UEK.   If you are not using an Oracle operating system, then consider moving specific tablespaces to a flash storage device. Physically, the DSFC is just a file.   The file can span one or more flash storage devices or partitions.   This spanning of devices is inherent to DSFC, which acts like software defined RAID 0.   You may use logical volume management or Oracle ASM disk groups to do the same thing, but that's over
Read more

Understanding TPC and TPC-like Database Benchmarks

This post is Part 1 in a series about running benchmarks to measure the performance of relational databases.  This post provides a very brief introduction to the popular industry standard benchmarks TPC-C, E, H, and DS and the software programs you can use to run a variation of each benchmark. The discussion here is generic and can be applied to any relational database like Oracle, SQL Server, MySQL, and PostgreSQL.   The TPC has many benchmarks for non-relational databases not discussed here.   The discussion here is also intended for informal benchmarking without publication.   Why Benchmark? Benchmark tools can answer a variety of questions and help predict success or failure.   Consider the following examples of why organizations perform internal benchmarking of database systems: Predict change impact .   Organizations run benchmarks to see how a system change impacts performance.   Run a benchmark to establish a baseline, make a system or database change, and then re-run t
Read more