Oracle Database 11g: SQL Query Result Set Caching

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