Ulfet`s Oracle Blog: Oracle 11g new features: Result cache

One of the best feature for Oracle 11g is setting result cache. Result cache reside in Shared Pool area in SGA. Now database can serves result for queries from the cache instead of re-execute query again and again. But when depending database object is modified cache result stays invalid. Using result cache you can improve performance of your query. It is reduce I/O, sorts, computations.

There are some restrictions for result cache which could not store data:

Data dictionary objects;
Temporary tables;
Queries with bind variables (if different) ;
Flashback query`s result;
Some SQL functions : current_date, current/local_timestamp, sysdate etc;
Function which has any OUT or IN OUT parameter;
Function has IN parameter that are CLOB, BLOB, REF CURSOR etc;
etc

How to configure result cache
New initialize parameters RESULT_CACHE_MAX_SIZE and RESULT_CACHE_MODE exists on Oracle 11g. You can enable/disable result cache using RESULT_CACHE_MAX_SIZE parameter.
If value of RESULT_CACHE_MAX_SIZE = 0 it means this feature is disabled.
Memory allocate for the result cache is taken from the shared pool.

There are three different modes you can use and they are managed with RESULT_CACHE_MODE init parameter. This parameter is dynamic and can be changed with ALTER SYSTEM and ALTER SESSION. Default is MANUAL.

Using result_cache hint you can use to manually cache a result of query to override the default setting of there parameter in a session level. Also there is has no_result_cache hint.

There are has several views:

V$RESULT_CACHE_STATISTICS	Lists various server result cache settings and memory usage statistics.
V$RESULT_CACHE_MEMORY	Lists all the memory blocks in the server result cache and their corresponding statistics.
V$RESULT_CACHE_OBJECTS	Lists all the objects whose results are in the server result cache along with their attributes.
V$RESULT_CACHE_DEPENDENCY	Lists the dependency details between the results in the server cache and dependencies among these results.
CLIENT_RESULT_CACHE_STATS$	Stores cache settings and memory usage statistics for the client result caches obtained from the OCI client processes.

Check resulting our database`s parameters for result cache

SQL> col name format a30
SQL> col value format a20
SQL> select name,value from v$parameter where name like 'result_cache%';

NAME VALUE
------------------------------ --------------------
result_cache_mode MANUAL
result_cache_max_size 1605632
result_cache_max_result 5
result_cache_remote_expiration 0

Now check memory utilization in SHARED POOL:

SQL> select * from v$sgastat where pool='shared pool' and name like 'Result%';

POOL NAME BYTES
------------ -------------------------- ----------
shared pool Result Cache: State Objs 2852
shared pool Result Cache: Memory Mgr 124
shared pool Result Cache: Bloom Fltr 2048
shared pool Result Cache: Cache Mgr 4416

SQL> select * from V$RESULT_CACHE_DEPENDENCY;

no rows selected

SQL> desc dbms_result_cache;

PROCEDURE BYPASS