Oracle Server Result Cache

This post is about the Oracle Server Result Cache, a free feature of Oracle Database Enterprise Edition.  It was first introduced in Oracle 11g.  There were minor changes over time and it basically works the same in Oracle 21c.  Oracle 21c added some tuning knobs including these three new init.ora parameters, two of which support caching results of queries against temporary objects:

• RESULT_CACHE_EXECUTION_THRESHOLD
• RESULT_CACHE_MAX_TEMP_SIZE
• RESULT_CACHE_MAX_TEMP_RESULT

What is the Oracle Server Result Cache?

The Oracle Server Result Cache allows you to cache the results of queries and PL/SQL function calls in database server memory so that future executions of an identical query or function can immediately obtain results from the cache instead of executing and fetching data to re-formulate the results.  Without this feature, Oracle caches the SQL execution plan so it knows how to fetch the data, and it caches some or all of the data, but your query must still assemble the data blocks, sort the data, performing grouping, etc.  The Oracle Server Result Cache allows you to skip all of that work.

Result cached in memory are available to all users.  Users running the same query and want the same data can quickly pull the result set from the cache rather than actually running the query and fetching source data.  Since the result set it stored as a thing, there’s no wait time for it to be assembled.  Oracle just grabs the result set and sends it to the user.

Where is the Oracle Server Result Cache?

The Oracle Server Result Cache sits in a sub-region of the Oracle SGA's Shared Pool called the “Result Cache”.  The SGA is Oracle's entire shared memory region.  Within the SGA are numerous areas of which the Shared Pool is well known to Oracle DBAs.  Within the Shared Pool is a little known region called the Result Cache.

Within the Result Cache are two sub-areas: the Query Result Cache and the PL/SQL Function Result Cache.  These two sub-areas are auto sized by Oracle at run-time and cannot be sized by the administrator.

The Result Cache might or might not exist by default.  That varies by platform.  The cache is sized using the database initialization parameter RESULT_CACHE_MAX_SIZE.  On some platforms the default value is 0 bytes, so the cache does not exist.  On other platforms the default is a non-zero integer, so the cache does exist.  To see the current value run the following SQL statement:

SQL> show parameter result_cache_max_size;

The feature is Oracle Server Result Cache enabled by allocating memory to the cache.  The feature can be disabled by resetting the cache size to 0.  If memory has been allocated, then you should find the feature is already enabled:

SQL> select dbms_result_cache.status() from dual;

DBMS_RESULT_CACHE.STATUS()
-------------------------------------------------
ENABLED

Shared Pool memory is consumed by the Result Cache.  You should increase the Shared Pool size to avoid starving other features that use the Shared Pool.  Also, in many databases the parameter shared_pool_size is not set and defaults to 0 which means auto-managed.  This causes a problem for the Result Cache: if parameter shared_pool_size is 0 then Oracle's memory transfer fails.

Enabling the Oracle Server Result Cache

To enable the feature allocate it some memory and bounce the instance.  However, if the Shared Pool is not explicitly sized then an error can occur as discussed earlier.  Therefore, in the below example I first size the Shared Pool and then I size the Result Cache:

ALTER SYSTEM SET SHARED_POOL_SIZE=4G SCOPE=SPFILE;
ALTER SYSTEM SET RESULT_CACHE_MAX_SIZE=1G SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

You can verify the feature is enabled with the following query:

SQL> select dbms_result_cache.status() from dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------
ENABLED

Bouncing the instance is only required when enabling or disabling the feature.  You can resize it without a bounce as shown here:

ALTER SYSTEM SET RESULT_CACHE_MAX_SIZE=1500M;

Once parameter RESULT_CACHE_MAX_SIZE is set Oracle allocates memory as needed up to the maximum size defined by the parameter.  Oracle does not pre-allocate all of the memory specified by the init.ora parameter.  If you lower the parameter value, then Oracle starts to immediately release that memory back to the Shared Pool.

Oracle Server Result Cache Memory Notes

The Oracle Server Result Cache is part of the Shared Pool and consumes memory from the Shared Pool.  You can see this in the below output from a call to 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 = 950272 bytes (928 blocks)
Maximum Result Size = 47104 bytes (46 blocks)
[Memory]
Total Memory = 46340 bytes [0.048% of the Shared Pool]
... Fixed Memory = 10696 bytes [0.011% of the Shared Pool]
... State Object Pool = 2852 bytes [0.003% of the Shared Pool]
... Cache Memory = 32792 bytes (32 blocks) [0.034% of the Shared Pool]
....... Unused Memory = 30 blocks
....... Used Memory = 2 blocks
........... Dependencies = 1 blocks
........... Results = 1 blocks
............... SQL = 1 blocks

PL/SQL procedure successfully completed.

For Oracle 21c on most platforms the Oracle Server Result Cache is enabled by default and sized to either 1% of the Shared Pool or one-half of 1% of the SGA target.  In other words,

• If sga_target is set, then result_cache_max_size = sga_target * 0.005.
• If sga_target is not set, then result_cache_max_size = shared_pool_size * 0.01.

The maximum size of the Server Result Cache is always limited to 75% of the Shared Pool (i.e., shared_pool_size * 0.75).  If the user sets parameter result_cache_max_size too high, then Oracle stops allocating memory at the 75% mark.

The amount of this memory that can be used by a single result set is determined by init.ora parameter RESULT_CACHE_MAX_RESULT.  This is a percentage of the Server Result Cache memory, so the default value 5 means each result set can use up to 5% of the memory.

Configuring Oracle Server Result Cache Behavior

By default, the Server Result Cache only caches results of queries with hint /*+ RESULT_CACHE */.  There are two ways to alter the default behavior: change init.ora parameter RESULT_CACHE_MODE and alter the table's MODE attribute.

Let's first look at init.ora parameter RESULT_CACHE_MODE.  The default value is MANUAL.  It can be set differently for each PDB and for each RAC instance.  This default value MANUAL tells Oracle to only cache results of queries having the hint.  The other allowable value FORCE tells Oracle to cache all query results even if no hint was used.

The init.ora parameter has no effect on caching PL/SQL function results.  To enable caching of PL/SQL function results you must include the RESULT_CACHE clause in its definition.

Let's next look at the table attribute.  You can set the result caching mode per table using an ALTER TABLE statement and including the MODE clause like the below example.  Any table where you don't do this will inherit the mode from the init.ora parameter.  There are two allowable values: DEFAULT means result caching only occurs when init.ora parameter RESULT_CACHE_MODE=FORCE, or if that parameter is set to MANUAL and the hint /*+ RESULT_CACHE */ is specified in the query.  The below example causes Oracle to cache all query results from table foo regardless of the init.ora parameter:

ALTER TABLE FOO RESULT_CACHE (MODE FORCE);

There is also a negative query hint to block caching results.  The hint /*+ NO_RESULT_CACHE */ in your query prevents result caching even if the init.ora parameter and table attribute are both set to FORCE.

So, there are three ways to configure Oracle's behavior.  Let's wrap-up by looking at precedence:

• The query hint takes precedence over all other settings.
• If the query has no hint and the init.ora parameter is set to FORCE, this takes precedence over the table attribute.
• If the query has no hint and the init.ora parameter is not FORCE, then the table attribute is used.

Using the Oracle Server Result Cache

The same query hints /*+ RESULT_CACHE */ and /*+ NO_RESULT_CACHE */ control the caching of query results and the fetching of results from the cache. 

To cache query results, include the hint /*+ RESULT_CACHE */ in your query.  Consider the following query which shows the hint:

SQL> select /*+ RESULT_CACHE */ id, fname, lname from emp order by 3,2,1;

To prevent caching of query results, include the hint /*+ NO_RESULT_CACHE */ in your query.  Consider the following query which shows the hint:

SQL> select /*+ NO_RESULT_CACHE */ id, fname, lname from emp order by 3,2,1;

To fetch a result set from the cache your query needs the same hint /*+ RESULT_CACHE */ that is used to populate the cache.  When a user runs a query with the hint, the following order of events occurs:

• The Oracle Optimizer determines if the same query has been run previously (all queries have a unique ID tracked in the Oracle Library Cache) and if so checks the result cache for the query results. 
• If the query has not been previously executed, or if there is no result set in the cache, then the query is run from scratch and the result set is cached. The results are then copied to the user.
• If the query has been previously run but the result set in the cache is stale, then the query is run from scratch and the result set is cached. The results are then copied to the user.
• If the query has been previously run and the result set in the cache are fresh, those results are returned to the user.  The query doesn't really execute or fetch data in a traditional sense.

If you don't want to retrieve result sets, then simply omit the hints from your query.  If your application is designed to always have a hint, then you can use the /*+ NO_RESULT_CACHE */ hint to prevent fetching cached results.

You can verify queries are using the Oracle Server Result Cache with EXPLAIN statements.  The below example from the Oracle docs (ref) shows the query will use result set 8fpza04gtwsfr6n595au15yj4y rather than actually fetching data from storage.

--------------------------------------------------------------
| Id | Operation          | Name                       |Rows
--------------------------------------------------------------
| 0 | SELECT STATEMENT    |                            | 11
| 1 |  RESULT CACHE       | 8fpza04gtwsfr6n595au15yj4y |
| 2 |   HASH GROUP BY     |                            | 11
| 3 |    TABLE ACCESS FULL| EMPLOYEES                  | 107
--------------------------------------------------------------

Each result set have a unique ID.  You can find these in view V$RESULT_CACHE_OBJECTS.

You can also monitor memory utilization within the Result Cache with the below command:

SET SERVEROUTPUT ON
EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT;