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
morethan
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 inListing1.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:

How
does setting the result cache mode to FORCE affect the current contents
of the SQL Query Results Cache? As the code shown inListing
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:

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:

Table 1-1.
DBMS_RESULT_CACHE Functions and Procedures

Function /
Procedure

Description

STATUS

Returns
the current status of the
Result Cache. Values include:

ENABLED: The result cache is enabled.

DISABLED: The result cache has been disabled.

BYPASSED: The result cache is temporarily unavailable.

SYNC: The result cache is available, but is currently being resynchronized with other RAC nodes.

MEMORY_REPORT

Lists
either a summary (by default)
or detailed report of Result
Cache memory usage

FLUSH

Flushes the entire contents of the Result Cache

INVALIDATE

Invalidates
a cached result for a specific object
in the Result Cache

INVALIDATE_OBJECT

Invalidates
a specific Result Cache based
on its Cache ID

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:

Table 1-2. SQL Result
Cache Metadata

View

Description

V$RESULT_CACHE_STATISTICS

Lists
the various cache settings and memory usage statistics

V$RESULT_CACHE_MEMORY

Lists
all memory blocks and corresponding statistics

V$RESULT_CACHE_OBJECTS

Lists
all the objects (cached results and dependencies) along with their attributes

V$RESULT_CACHE_DEPENDENCY

Lists
the dependency details between the cached results and dependencies

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: