Oracle Result Cache

Oracle Result Cache

Available only in Enterprise Edition

Documentation quote:

The result cache stores the results of SQL queries and PL/SQL functions in an area called Result Cache Memory in the shared pool. When these queries and functions are executed repeatedly, the results are retrieved directly from the cache memory. This results in a faster response time. The cached results stored become invalid when data in the dependent database objects is modified. The use of the result cache is database-wide decision. Result cache itself is instance specific and can be sized differently on different instances. To disable the result cache in a cluster, you must explicitly set the RESULT_CACHE_MAX_SIZE initialization parameter to 0 during every instance startup.

How to enable:

check the settings:

SQL> show parameter result_cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
client_result_cache_lag              big integer 3000
client_result_cache_size             big integer 0
result_cache_max_result              integer     10
result_cache_max_size                big integer 2G
result_cache_mode                    string      AUTO
result_cache_remote_expiration       integer     0

AUTO is meaningless (you can find this setting in SE as default)

RESULT_CACHE_MODE

Property Description

Parameter type String

Syntax RESULT_CACHE_MODE = { MANUAL | FORCE }

Default value MANUAL

Modifiable ALTER SESSION, ALTER SYSTEM

Basic No

SQL> alter system set result_cache_mode=FORCE scope=memory;

SQL> alter system set result_cache_max_size=2G scope=memory;
--> Note: Result Cache is a part of SGA.

How to monitor:
SQL> select DBMS_RESULT_CACHE.status from dual;
STATUS
----------------------------
ENABLED

Generate report:
SQL> set serveroutput on
SQL> execute DBMS_RESULT_CACHE.MEMORY_REPORT(TRUE);
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          = 0 bytes
Maximum Cache Size  = 0 bytes (0 blocks)
Maximum Result Size = 0 bytes (0 blocks)
[Memory]
Total Memory = 5184 bytes [0.005% of the Shared Pool]
... Fixed Memory = 5184 bytes [0.005% of the Shared Pool]
....... Memory Mgr = 132 bytes
....... Cache Mgr  = 152 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2852 bytes
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.