Using the Result Cache
You can improve the response times of frequently executed SQL queries by using the result cache. The result cache stores results of SQL queries and PL/SQL functions in a new component of the SGA called the Result Cache Memory. The first time a repeatable query executes, the database caches its results. On subsequent executions, the database simply fetches the results from the result cache instead of executing the query again. The database manages the result cache. You can turn result caching on only at the database level. If any of the objects that are part of a query are modified, the database invalidates the cached query results. Ideal candidates for result caching are queries that access many rows to return a few rows, as in many data warehousing solutions.
The result cache consists of two components, the SQL Query Result Cache that stores SQL query results and the PL/SQL Function Result Cache that stores the values returned by PL/SQL functions, with both components sharing the same infrastructure. I discuss the two components of the result cache in the following sections.
Managing the Result Cache
The result cache is always enabled by default, and its size depends on the memory the database allocates to the shared pool. If you specify the MEMORY_TARGET parameter for allocating memory, Oracle allocates 0.25% of the MEMORY_TARGET parameter value to the result cache. If you specify the SGA_TARGET parameter instead, Oracle allocates 0.5% of the SGA_TARGET value to the result cache. You can change the memory allocated to the result cache by setting the RESULT_CACHE_MAX_SIZE initialization parameter. This parameter can range from a value of zero to a system-dependent maximum. You disable result caching by setting the parameter to zero, as shown here:
SQL> ALTER SYSTEM SET result_cache_max_size=0;
Since result caching is enabled by default, it means that the RESULT_CACHE_MAX_SIZE parameter has a positive default value as well, based on the size of the MEMORY_TARGET parameter (or the SGA_TARGET parameter if you have that parameter instead). In addition to the RESULT_CACHE_MAX_SIZE parameter, two other initialization parameters have a bearing on the functioning of the result cache: the RESULT_CACHE_MAX_RESULT parameter specifies the maximum amount of the result cache a single result can use. By default, a single cached result can occupy up to 5 percent of the result cache, and you can specify a percentage between 1 and 100. The RESULT_CACHE_REMOTE_EXPIRATION parameter determines the length of time for which a cached result that depends on remote objects is valid. By default, this parameter is set to zero, meaning you aren’t supposed to use the result cache for queries involving remote objects. The reason for this is over time remote objects could be modified, leading to invalid results in the cache.
Setting the RESULT_CACHE_MODE Parameter
Whether the database caches a query result or not depends on the value of the RESULT_CACHE_MODE initialization parameter, which can take two values: MANUAL or FORCE. Here’s how the two values affect result caching behavior in the database:
If you set the parameter to FORCE, the database will try to use the cache for all results, wherever it’s possible to do so. You can, however, skip the cache by specifying NO_RESULT_CACHE hint within a query.
If you set the parameter to MANUAL, the database caches the results of a query only if you include the RESULT_CACHE hint in the query.
By default, the RESULT_CACHE_MODE parameter is set to MANUAL and you can change the value dynamically as shown here:
SQL> alter session set result_cache_mode=force scope=spfile;
Using the RESULT_CACHE and NO_RESULT_CACHE Hints
Using the RESULT_CACHE hint as a part of a query adds the Result Cache operator to a query’s execution plan. The Result Cache operator will search the result cache to see whether there’s a stored result in there for the query. It retrieves the result if it’s already in the cache; otherwise, the Result Cache operator will execute the query and store its results in the result cache. The no_result_cache operator works the opposite way. If you add this hint to a query, it’ll lead the Result Cache operator to bypass the result cache and re execute the query to get the results.
Note: The RESULT_CACHE and the NO_RESULT_CACHE hints always take precedence over the value you set for the RESULT_CACHE_MODE initialization parameter.
You can use the following views to manage the result cache:
- V$RESULT_CACHE_STATISTICS: Lists cache settings and memory usage statistics
- V$RESULT_CACHE_OBJECTS: Lists all cached objects and their attributes
- V$RESULT_CACHE_DEPENDENCY: Lists the dependency information between the cached results and dependencies
- V$RESULT_CACHE_MEMORY: Lists all memory blocks and their statistics
- V$RESULT_CACHE_OBJECTS: Lists both cached results and all dependencies
Restrictions on Using the SQL Query Result Cache
You can’t cache results in the SQL Query Result Cache for the following objects:
- Temporary tables.
- Dictionary tables.
- Nondeterministic PL/SQL functions.
- The curval and nextval pseudo functions.
- The SYSDATE, SYS_TIMESTAMP, CURRENT_DATE, CURRENT_TIMESTAMP, LOCAL_TIMESTAMP, USERENV, SYS_CONTEXT, and SYS_QUID functions.
- You also won’t be able to cache sub queries, but you can use the RESULT_CACHE hint in an inline view.
The Client Query Result Cache
If you are using any OCI applications and drivers such as JDBC and ODP.NET, you can also use Oracle’s client-side caching of SQL result sets in the Client Query Result Cache that’s located on the server. The database keeps the result sets consistent with changes in session attributes. If you’ve frequently repeated statements in your applications, client-side caching could offer tremendous improvement in query performance benefits. Since the database caches results on the clients, server round-trips are minimized and scalability improves as a result, with lower I/O and CPU load.
Unlike server-side caching, client-side caching isn’t enabled by default. If your applications produce small result sets that are static over a period of time, client-side caching may be a good thing to implement. Frequently executed queries and queries involving lookup tables are also good candidates for client-side caching.
Enabling and Disabling the Client Query Result Cache
As with server-side caching, you use the RESULT_CACHE_MODE initialization parameter to enable and disable client-side caching. The RESULT_CACHE and the NO_RESULT_CACHE hints work the same way as they do for server-side caching. If you choose to specify the MANUAL setting for the RESULT_CACHE_MODE parameter, you must use the RESULT_CACHE hint in a query for the query’s results to be cached. Also, the two hints override the setting of the RESULT_CACHE_MODE parameter, as in the case of server-side caching. You pass the RESULT_CACHE and the NO_RESULT_CACHE hints to SQL statements by using the OCIStatementPrepare() and the OCIStatementPrepare2() calls.
Managing the Client Result Cache
There are two initialization parameters that control how the Client Query Result Cache works. Here’s a brief description of these parameters:
CLIENT_RESULT_CACHE_SIZE: Determines the maximum client per-process result set cache size (in bytes). If you set this parameter to zero, you disable the Client Query Result Cache. The database allocates the maximum-size memory to every OCI client process by default.
Note: You can override the setting of the CLIENT_RESULT_CACHE_SIZE parameter with the server-side parameter.
OCI_RESULT_CACHE_MAX_SIZE: By setting the latter to zero, you can disable the Client Query Result Cache.
CLIENT_RESULT_CACHE_LAG: Determines the Client Query Result Cache lag time. A low value means more round-trips to the database from the OCI client library. Set this parameter to a low value if your application accesses the database infrequently.
You can’t cache queries that use the following types of objects, even though you may be able to cache them in a server-side result cache:
- Remote objects.
- Complex types in the select list.
- Flashback queries.
- Queries that include PL/SQL functions.
- Queries that reference VPD policies on the tables.
AUTOMATIC MEMORY MANAGEMENT
This is the new feature in 11g which enables DBA to manage both SGA and PGA automatically by setting MEMORY_TARGET and MEMORY_MAX_TARGET parameters.
MEMORY_TARGET = SGA_TARGET + PGA_AGGREGATE_TARGET.
MEMORY_TARGET is dynamic parameter, so the value can be changed at any time, where as MEMORY_MAX_TARGET is static.
We can check memory sufficiency and tune it by taking advice from v$MEMORY_TARGET_ADVICE.
Note: More about AMM.