Oracle Database 11g: SQL Query Result Set Caching

July 30, 2008

Synopsis. Oracle Database 11gR1 offers several new performance enhancements that limit “round trips” between database server, application servers, and applications when the identical data is required to answer queries, provide lookup data, or return deterministic values from a function. This article – the first in this series – delves into how the result set from a SQL query result set can be retained in the database’s Shared Pool for later access by a similar or identical query in the immediate future.

As the Oracle RDBMS evolved over the last three decades, it’s provided database administrators with several options to retain often-queried data in memory to limit or eliminate unnecessary data reads from a database’s I/O subsystem. Here’s a quick review of some of the features that a DBA has on her tool belt to maintain fine-grained control of query-related data in database memory.

Caching Database Blocks Permanently. The CACHE storage option (also provided as the +CACHE optimizer hint) provides a method to retain all database blocks for a table, once that table has been table-scanned into the database buffer cache. Once cached via this method, the corresponding buffers never age out of the buffer cache until the database instance is terminated. This strategy has some drawbacks because it relies upon the DBA’s intimate knowledge of which tables can most benefit from caching; it also has the tendency to be overused as a panacea for code tables and reference data.

Influencing Storage Retention of Database Blocks. Oracle also offers the ability to influence a buffer’s retention in the KEEP buffer pool of the database buffer cache by either creating or altering a table or index so that it’s kept within that pool. Oracle treats this as a simple promise to hold onto the buffers in the KEEP pool for as long as possible; essentially, they’re placed closer to the most recently used (MRU) end of the buffer cache. However, nothing is forever, and it’s not impossible that a buffer in the KEEP buffer pool may eventually be aged out when large queries need enormous amounts of buffer cache to complete.

Retaining Result Sets Permanently. Materialized views (MVs) offer a way to hold onto often-queried data: the capability to retain a specific set of rows for an extended period of time by creating a view with an underlying table. If properly configured, Oracle will “rewrite” an existing query so that it queries the MV instead of the underlying database tables directly. Moreover, an MV can be set up so that a change to its underlying base data automatically forces a refresh of the dependent MVs. However, this most obvious advantage of an MV is also its potential bane: Since it needs to be refreshed periodically to insure that its data is kept current, the actual refresh of the data may take excessive time, and may even occur at a most inopportune time.

What I really need is something less permanent than any of these (still excellent!) features: a section of memory that stores just the results of a query, and that could be shared with any other query that needs similar data. For example, a “code table” that captures the list of U.S. states and possessions may only span a handful of rows and columns and hardly ever change, so it would need to be refreshed at extremely infrequent intervals. Nevertheless, when that set of results is no longer valid, I’d like it to refresh itself without any intervention on my part. And what about a query that regularly gets executed by several users, but not frequently enough to take advantage of a materialized view’s query rewrite capabilities? MVs are not necessarily trivial to create, configure and refresh, so this feature needs to be simpler to set up than an MV, and must be able to refresh itself with only minimum performance overhead.

SQL Query Result Set Caches

Oracle Database 11g offers result set caching to provide this functionality. A SQL Query Result Set will be cached within a subsection of a database instance’s shared pool in the Shared Global Area (SGA) depending on the setting for several new initialization parameters.

RESULT_CACHE_MODE. This new parameter accepts one of three values, and it can be set at either the database (ALTER SYSTEM) or individual session (ALTER SESSION) level:

• When set to MANUAL (the default), a SQL query result set will only be considered for potential caching if the query itself specifies the +RESULT_CACHE optimizer hint.
• If this parameter is set to FORCE, however, a query’s result set will always be cached unless the query specifies the +NO_RESULT_CACHE optimizer hint.
• Finally, if this parameter is set to AUTO, Oracle 11g uses an unpublished internal algorithm to automatically determine whether a query’s result set should be cached based on how often the result set could benefit future statement execution. Only if the query specifies the +NO_RESULT_CACHE optimizer hint will it be ignored.

Controlling Result Set Cache Memory Utilization. Oracle 11g also provides several methods to limit precisely the amount of memory that may be allocated for SQL query result set caching:

RESULT_CACHE_MAX_SIZE. To reserve an appropriate amount of SGA memory for all local result caches, the DBA can specify a value for the RESULT_CACHE_MAX_SIZE initialization parameter. Oracle 11g automatically rounds the supplied value to the nearest 32K boundary.

If no value is supplied, then Oracle 11g uses the following algorithm to allocate memory for Result Caches:

• If a value has been specified for the new Oracle 11g MEMORY_TARGET parameter (i.e. the total memory allocated to both SGA and PGA for the database instance), then Oracle sets RESULT_CACHE_MAX_SIZE to 0.25% of MEMORY_TARGET.
• If no value for MEMORY_TARGET has been set, but a value for SGA_TARGET has been set, then Oracle 11g sets RESULT_CACHE_MAX_SIZE to 0.5% of SGA_TARGET.
• Finally, if neither a value for MEMORY_TARGET or SGA_TARGET has been set, then Oracle sets RESULT_CACHE_MAX_SIZE to 1.0% of the memory allocated to the Shared Pool based on the setting for SHARED_POOL_SIZE.

Regardless of which calculation method is used, note that Oracle 11g will never set RESULT_CACHE_MAX_SIZE to more than 75% of SHARED_POOL_SIZE. Moreover, note that if the DBA wants to deactivate SQL Result Caching features completely, she merely needs to set the size of this memory allocation area to zero (0) to tell Oracle 11g to reserve absolutely no memory for results caching.

RESULT_CACHE_MAX_RESULT. This parameter tells Oracle 11g how much of the result cache should be allowed for any individual query. Its default value of 5% of the entire result cache should usually be sufficient, but it can also be set between 0% and 100%.

RESULT_CACHE_REMOTE_EXPIRATION. If a query depends on a remote database, then this parameter determines the number of minutes for which a result set should be retained. The default value of zero (0) minutes serves as a reminder that any changes to a remote database table can’t be detected at the local database, and therefore stale result sets might remain for an unduly long period of time. This parameter can be set globally (ALTER SYSTEM) or on a per-session basis (ALTER SESSION).

Creating SQL Query Result Caches: A Brief Demonstration

For a practical demonstration of how to use SQL Query Results Caching features in MANUAL mode, I’ve provided the code shown in Listing1.1:

• I first purged the results cache using DBMS_RESULT_CACHE.PURGE (see next section for more details), activated MANUAL results caching, and then sized the results cache relatively small at only 1MB.
• I then issued a SQL query to capture a summary-level presentation of total and average promotion costs from the contents of the Sales History (SH) schema’s PROMOTIONS table. The resulting row set that’s captured contains less than 10 rows captured from over 500 rows in that source table, so it’s a relatively good candidate for SQL query results caching.
• I then issued an EXPLAIN PLAN against the original query, including the +RESULT_CACHE hint so that I could determine if the result cache just created would be utilized by future queries. I also created a report that shows in detail how the result cache’s memory has been utilized. Here’s a sample of this output:

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          = 1K bytes
Maximum Cache Size  = 1M bytes (1K blocks)
Maximum Result Size = 10K bytes (10 blocks)
[Memory]
Total Memory = 103528 bytes [0.073% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.004% of the Shared Pool]
....... Cache Mgr  = 108 bytes
....... Memory Mgr = 124 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2852 bytes
... Dynamic Memory = 98396 bytes [0.069% of the Shared Pool]
....... Overhead = 65628 bytes
........... Hash Table    = 32K bytes (4K buckets)
........... Chunk Ptrs    = 12K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 8284 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks

How does setting the result cache mode to FORCE affect the current contents of the SQL Query Results Cache? As the code shown in Listing 1.2 illustrates:

• I first activated FORCE mode for the results cache, and I then sized the results cache relatively large at 20MB and allowed the maximum size for any individual result cache to one-half of that value (10MB).
• Next, I issued a simple SQL query to capture the names of all Vendors from table AP.VENDORS in the Accounts Payable (AP) test data I originally generated in my previous article series on Database Capture and Replay. Since this query doesn’t include the +NO_RESULT_CACHE optimizer directive, the result set will be cached immediately.
• I then issued a SQL query to capture a more complex, summary-level presentation of Accounts Payable (AP) test data. Since the resulting row set incorporates the +NO_RESULT_CACHE optimizer directive, the result set will not be cached at all.
• My final step is to issue an EXPLAIN PLAN against these two queries to see the impact on any future result set that might be likewise generated. I also recreated the detailed report on the result cache’s memory to see if anything has changed there:

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          = 1K bytes
Maximum Cache Size  = 20M bytes (20K blocks)
Maximum Result Size = 10M bytes (10K blocks)
[Memory]
Total Memory = 103528 bytes [0.073% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.004% of the Shared Pool]
....... Cache Mgr  = 108 bytes
....... Memory Mgr = 124 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2852 bytes
... Dynamic Memory = 98396 bytes [0.069% of the Shared Pool]
....... Overhead = 65628 bytes
........... Hash Table    = 32K bytes (4K buckets)
........... Chunk Ptrs    = 12K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 8284 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 24 blocks
........... Used Memory = 8 blocks
............... Dependencies = 2 blocks (2 count)
............... Results = 6 blocks
................... SQL     = 6 blocks (2 count)

EXPLAIN PLAN FOR
SELECT /*SQRC_1.2*/ 
     vendor_id
    ,name
  FROM ap.vendors
;
SELECT * 
  FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL));

----------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2620802014
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |   164 |  3772 |     3   (0)| 00:00:01 |
|   1 |  RESULT CACHE      | 89gqh0j9248q8d0w79w0fcwhw2 |       |       |            |          |
|   2 |   TABLE ACCESS FULL| VENDORS                    |   164 |  3772 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=2; dependencies=(AP.VENDORS); name="SELECT /*SQRC_1.2*/
     vendor_id
    ,name
  FROM ap.vendors
SQL> EXPLAIN PLAN FOR
SELECT /*+NO_RESULT_CACHE SQRC_1.3*/
     I.customer_id
    ,C.cust_last_name || ', ' || C.cust_first_name AS customer_fullname
    ,SUM(ID.extended_amt) total_detail
  FROM
     ap.vendors V
    ,ap.invoices I
    ,ap.invoice_items ID
    ,oe.customers C
    ,oe.product_information P
 WHERE ID.invoice_id = I.invoice_id
   AND I.vendor_id = V.vendor_id
   AND I.customer_id = C.customer_id
   AND ID.product_id = P.product_id
   AND I.active_ind = 'Y'
 GROUP BY 
     I.customer_id
    ,C.cust_last_name || ', ' || C.cust_first_name
 ORDER BY
     I.customer_id
    ,C.cust_last_name || ', ' || C.cust_first_name
;
SELECT * 
  FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL));
  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23  
Explained.
SQL>   2  
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
Plan hash value: 500053926
----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |   956 | 34416 |    11  (19)| 00:00:01 |
|   1 |  SORT GROUP BY                 |                   |   956 | 34416 |    11  (19)| 00:00:01 |
|*  2 |   HASH JOIN                    |                   |   956 | 34416 |    10  (10)| 00:00:01 |
|   3 |    NESTED LOOPS                |                   |       |       |            |          |
|   4 |     NESTED LOOPS               |                   |    25 |   700 |     6   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL         | CUSTOMERS         |   319 |  6061 |     5   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN          | INVOICES_CUST_IDX |    25 |       |     0   (0)| 00:00:01 |
|*  7 |     TABLE ACCESS BY INDEX ROWID| INVOICES          |     1 |     9 |     1   (0)| 00:00:01 |
|   8 |    TABLE ACCESS FULL           | INVOICE_ITEMS     |   975 |  7800 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"."INVOICE_ID"="I"."INVOICE_ID")
   6 - access("I"."CUSTOMER_ID"="C"."CUSTOMER_ID")
       filter("I"."CUSTOMER_ID">0)
   7 - filter("I"."ACTIVE_IND"='Y')

Controlling SQL Query Result Set Caching With DBMS_RESULT_CACHE

Oracle 11g also supplies the DBMS_RESULT_CACHE package to interrogate the status of and precisely control the contents of the SQL result cache. Here’s a brief summary of its capabilities:

Listing 1.3 shows some additional examples of how to use these packaged procedures and functions.

Results Cache Metadata

Four dynamic views provide information about existing Results Cache contents, memory usage, and the database objects on which Result Caches depend:

See Listing 1.4 for several queries I’ve created against the single-instance (V$) views for this article; it’s a relatively simple task to expand these queries to the global resource view (GV$) for Real Application Clusters databases. In Listing 1.5 I’ve also reproduced the results from the query against the V$RESULT_CACHE_OBJECTS view to demonstrate what metadata it contains for cached result sets.

Next Steps

Oracle 11g’s new result caching capabilities offer an Oracle DBA several simple yet elegant tools to capture, retain, monitor and manage SQL Query Result Caches that speed application access to relatively persistent data. In the final article in this series, I’ll illustrate how to take advantage of result set caching within PL/SQL functions, as well as how to cache result sets at the application server level for faster client application access to frequently-accessed reference data.

References and Additional Reading

While I’m hopeful that I’ve given you a thorough grounding in the technical aspects of the features I’ve discussed in this article, I’m also sure that there may be better documentation available since it’s been published. I therefore strongly suggest that you take a close look at the corresponding Oracle documentation on these features to obtain crystal-clear understanding before attempting to implement them in a production environment. Please note that I’ve drawn upon the following Oracle Database 11gR1 documentation for the deeper technical details of this article:

B28320-01 Oracle Database 11gR1 Reference Guide

B28274-01 Oracle Database 11gR1 Performance Tuning Guide

B28279-02 Oracle Database 11gR1 New Features Guide

B28419-02 Oracle Database 11gR1 PL/SQL Packages and Types Reference

Also, the following MetaLink documents help clarify this feature set:

453567.1 11g New Feature: SQL Query Result Cache

563828.1 Result Cache Could Not Be Enabled

» See All Articles by Columnist Jim Czuprynski