Caching results, an interesting 11g Feature        Dec 26 th

In 11g, if you run I/O statement time and time again, you can cache the results so the future runs would run faster.

11g introduced the notion of caching query result.

The administrator can configure the SGA (shared Pool) to allocate the memory that will be used for query results caching.

As a developer, there are two ways you can cache results

1) using the  /*+ RESULT_CACHE */ hint in individual queries

2) using ALTER SESSION SET result_cache_mode = FORCE;  which will attempt to cache the result for all queries

The explain plan stats show if caching is used or not

Example:

 SQL> select /*+ RESULT_CACHE */ sum(sal) from emp,dept

the execution plan indicates Result cache as shown in Yellow

--------------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------------
| 0 | SELECT STATEMENT
| 1 |     RESULT CACHE      2nga574155m6a3x33j36czkkss |
| 2 |         SORT AGGREGATE
| 3 |             MERGE JOIN CARTESIAN
| 4 |                 INDEX FULL SCAN | PK_DEPT |
| 5 |                 BUFFER SORT
| 6 |                     TABLE ACCESS FULL | EMP |
--------------------------------------------------------------
 

SQL>select /*+NO_RESULT_CACHE */ sum(sal) from emp,dept

The execution plan does not indicating Result cache

----------------------------------------
| Id | Operation | Name
----------------------------------------
| 0 | SELECT STATEMENT |
| 1 |     SORT AGGREGATE |
| 2 |         MERGE JOIN CARTESIAN|
| 3 |             INDEX FULL SCAN | PK_DEPT
| 4 |             BUFFER SORT |
| 5 |                 TABLE ACCESS FULL | EMP
----------------------------------------

Note:  For the administrator , there are  primarily two parameters that you need to know about

result_cache_max_size:  the amount of memory that can be used out of the Shared_pool  for caching query results

result_cache_max_result:  The max percentage that SGA memory to be used for a single Cashed query result

for the related init.ora parameter, check the show parameters command

SQL> show parameters result_cache

NAME TYPE VALUE
------------------------------------ -----------     ------------
client_result_cache_lag               big integer     3000
client_result_cache_size              big integer     0
result_cache_max_result               integer         5
result_cache_max_size                 big integer     1376K
result_cache_mode                     string          MANUAL
result_cache_remote_expiration        integer         0

Finally, in order to find out objects that are in cache (similar to V$SQLAREA), you can query V$Result_cache_objects

Exmaple

SQL> SELECT name ,type ,cache_id ,row_count FROM v$result_cache_objects

NAME                                                         TYPE             CACHE_ID                                             ROW_COUNT
----------------------------------------                 ----------         -------------------------                             ----------
SCOTT.EMP                                                 Dependency     SCOTT.EMP                                         0
SCOTT.DEPT                                                Dependency     SCOTT.DEPT                                       0
select /*+ RESULT_CACHE */ sum(sal) from     Result               2nga574155m6a3x33j36czkks                1
emp,dept s
select /*+ RESULT_CACHE */ count(*) from     Result                7zqau32dq2a41fvsvbw8spgc8              1
emp, dept b

It would seem logical to assume that cached result remain in the SGA Shared Pool until they are flushed by the LRU algorithm