SQL Query Result Cache

PL/SQL Function Cache here

The SQL query result cache enables explicit caching of query result sets and query fragments in database memory. A dedicated memory buffer stored in the shared pool can be used for storing and retrieving the cached results (SQL query result cache). The query results stored in this cache become invalid when data in the database objects being accessed by the query is modified.

Although the SQL query cache can be used for any query, good candidate statements are the ones that need to access a very high number of rows to return only a fraction of them. This is mostly the case for data warehousing applications.

In the graphic shown in the slide, if the first session executes a query, it retrieves the data from the database and then caches the result in the SQL query result cache. If a second session executes the exact same query, it retrieves the result directly from the cache instead of using the disks.

The query optimizer manages the result cache mechanism depending on the settings of the RESULT_CACHE_MODE parameter in the initialization parameter file.

You can use this parameter to determine whether or not the optimizer automatically sends the results of queries to the result cache. You can set the RESULT_CACHE_MODE parameter at the system, session, and table level. The possible parameter values are MANUAL, and FORCE:

When set to MANUAL (the default), you must specify, by using the RESULT_CACHE hint, that a particular result is to be stored in the cache. 

When set to FORCE, all results are stored in the cache

Managing the SQL Query Result Cache

You can alter various parameter settings in the initialization parameter file to manage the SQL query result cache of your database.

By default, the database allocates memory for the result cache in the shared pool inside the SGA. The memory size allocated to the result cache depends on the memory size of the SGA as well as the memory management system.

You can change the memory allocated to the result cache by setting the RESULT_CACHE_MAX_SIZE parameter. The result cache is disabled if you set its value to 0. The value of this parameter is rounded to the largest multiple of 32 KB that is not greater than the specified value. If the rounded value is 0, then the feature is disabled.

Use the RESULT_CACHE_MAX_RESULT parameter to specify the maximum amount of cache memory that can be used by any single result.

The default value is 5%, but you can specify any percentage value between 1 and 100. This parameter can be implemented at the system and session level.

Use the RESULT_CACHE_REMOTE_EXPIRATION parameter to specify the time (in number of minutes) for which a result that depends on remote database objects remains valid. The default value is 0, which implies that results using remote objects should not be cached. Setting this parameter to a nonzero value can produce stale answers. For example, if the remote table used by a result is modified at the remote database.

Using the RESULT_CACHE Hint

If you want to use the query result cache and the RESULT_CACHE_MODE initialization parameter is set to MANUAL, you must explicitly specify the RESULT_CACHE hint in your query.

This introduces the ResultCache operator into the execution plan for the query. When you execute the query, the ResultCache operator looks up the result cache memory to check whether the result for the query already exists in the cache. If it exists, the result is retrieved directly out of the cache. If it does not yet exist in the cache, the query is executed, the result is returned as output, and is also stored in the result cache memory.

If the RESULT_CACHE_MODE initialization parameter is set to FORCE, and you do not want to store the result of a query in the result cache, you must then use the NO_RESULT_CACHE hint in your query.

For example, when the RESULT_CACHE_MODE value equals FORCE in the initialization parameter file, and you do not want to use the result cache for the EMPLOYEES table, then use the NO_RESULT_CACHE hint.

Note: Use of the [NO_] RESULT_CACHE hint takes precedence over the parameter settings.

Using the DBMS_RESULT_CACHE Package

You can use the DBMS_RESULT_CACHE package to perform various operations such as viewing the status of the cache (OPEN or CLOSED), retrieving statistics on the cache memory usage, and flushing the cache. For example, to view the memory allocation statistics, use the following SQL procedure:

SQL> set serveroutput on
SQL> execute dbms_result_cache.memory_report

R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1024 bytes
Maximum Cache Size  = 720896 bytes (704 blocks)
Maximum Result Size = 35840 bytes (35 blocks)
[Memory]
Total Memory = 46284 bytes [0.036% of the Shared Pool]
... Fixed Memory = 10640 bytes [0.008% of the Shared Pool]
... State Object Pool = 2852 bytes [0.002% of the Shared Pool]
... Cache Memory = 32792 bytes (32 blocks) [0.025% of the Shared Pool]
....... Unused Memory = 30 blocks
....... Used Memory = 2 blocks
........... Dependencies = 1 blocks
........... Results = 1 blocks
............... SQL = 1 blocks

Viewing SQL Query Result Cache Information

SQL Query Result Cache: Considerations

Any user-written function used in a function-based index must have been declared with the DETERMINISTIC keyword to indicate that the function always returns the same output value for any given set of input argument values.

·       The purge works only if the cache is not in use; disable (close) the cache for flush to succeed.

·       With bind variables, cached result is parameterized with variable values. Cached results can be found only for the same variable values. That is, different values or bind variable names cause cache miss.

 

OCI Client Query Cache

You can enable caching of query result sets in client memory with Oracle Call Interface (OCI) Client Query Cache in Oracle Database 11g.

The cached result set data is transparently kept consistent with any changes done on the server side. Applications leveraging this feature see improved performance for queries that have a cache hit.

Additionally, a query serviced by the cache avoids round trips to the server for sending the query and fetching the results. Server CPU, which would have been consumed for processing the query, is reduced thus improving server scalability.

Client-side caching is useful when you have applications that produce repeatable result sets, small result sets, static result sets, or frequently executed queries on database objects that do not change often.

Client and server result caches are autonomous; each can be enabled/disabled independently.
Note: You can monitor the client query cache using the client_result_cache_stats$ view.

The following two parameters can be set in your initialization parameter file:

·       CLIENT_RESULT_CACHE_SIZE: A nonzero value enables the client result cache. This is the maximum size of the client per-process result set cache in bytes. All OCI client processes get this maximum size and can be overridden by the OCI_RESULT_CACHE_MAX_SIZEparameter.

·       CLIENT_RESULT_CACHE_LAG: Maximum time (in milliseconds) since the last round-trip to the server, before which the OCI client query executes a round-trip to get any database changes related to the queries cached on client.

A client configuration file is optional and overrides the cache parameters set in the server initialization parameter file. Parameter values can be part of a sqlnet.ora file. When parameter values shown in the slide are specified, OCI client caching is enabled for OCI client processes using the configuration file.

OCI_RESULT_CACHE_MAX_RSET_SIZE/ROWS denotes the maximum size of any result set in bytes/rows in the per-process query cache.

OCI applications can use application hints to force result cache storage. The application hints can be SQL hints:

·       /*+ result_cache */

·       /*+ no_result_cache */