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.
