Query result cache in 11g

In 11g its possible to store the result of the SQL in a special section of shared pool called RESULT CACHE.

As the name suggests, this cache stores results. This makes subsequent SQLs run damn fast resulting in better performance.

To me, this features sounds like Materialized view - because those also when introduced boosted the performance of queries which used to take a lot of time while computing the result. The resultant data of mview was stored in table (i.e. segment/disk). And this case the resultant data is stored in memory. Though memory not being persistent, this feature still helps in subsequent runs of the same query.

The feature is broadly governed by init.ora parameter - RESULT_CACHE_MODE - which can be changed using "alter system" or "alter session". The default value of this parameter is MANUAL. You can switch it to FORCE (not recommended).

Now after this parameter has taken effect, you can modify your SQL to see its affect.

SQL> select /*+ result_cache */ deptno,sum(sal) from emp group by deptno;

DEPTNO SUM(SAL)
---------- ----------
30 9400
20 10875
10 8750

NOw run this query again - with hint
and this time with autotrace traconly explain option:

SQL> set autotrace traceonly exp
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 4067220884

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 21 | 4 (25)| 00:00:01 |
| 1 | RESULT CACHE | bw8bd7rpb6h0sg1rcrwyx3rz0x | | | | |
| 2 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Here you see data being fetched from result cache.

There are some dynamic views which can give more insight.

SQL> select table_name from dict where lower(table_name) like '%result%';

TABLE_NAME
------------------------------
DBA_METHOD_RESULTS
USER_METHOD_RESULTS
ALL_METHOD_RESULTS
V$CLIENT_RESULT_CACHE_STATS
GV$RESULT_CACHE_DEPENDENCY
GV$RESULT_CACHE_MEMORY
GV$RESULT_CACHE_OBJECTS
GV$RESULT_CACHE_STATISTICS
V$RESULT_CACHE_DEPENDENCY
V$RESULT_CACHE_MEMORY
V$RESULT_CACHE_OBJECTS

TABLE_NAME
------------------------------
V$RESULT_CACHE_STATISTICS
GV$CLIENT_RESULT_CACHE_STATS
CLIENT_RESULT_CACHE_STATS$



I used V$RESULT_CACHE_OBJECTS:

1* select id,status,block_count,name from V$RESULT_CACHE_OBJECTS
SQL> /

ID STATUS BLOCK_COUNT
---------- --------- -----------
NAME
----------------------------------------------------------------------------

0 Published 1
SCOTT.EMP

1 Published 1
select /*+ result_cache */ deptno,sum(sal) from emp group by deptno


Similar to the hint "result_cache" - there is another hint - "no_result_cache"

>> no_result_cache is to be used when you have set database parameter: RESULT_CACHE_MODE to FORCE - which means oracle will try to cache the result of all queries.

Ex: select /*+ no_result_cache */ deptno,sum(sal) from emp group by deptno

To see the memory utlization - there are few dynamic views, database functions available.

SQL> set serverout on
SQL> exec 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 = 1K bytes
Maximum Cache Size = 3488K bytes (3488 blocks)
Maximum Result Size = 174K bytes (174 blocks)
[Memory]
Total Memory = 136340 bytes [0.064% of the Shared Pool]
... Fixed Memory = 5140 bytes [0.002% of the Shared Pool]
... Dynamic Memory = 131200 bytes [0.061% of the Shared Pool]
....... Overhead = 65664 bytes
....... Cache Memory = 64K bytes (64 blocks)
........... Unused Memory = 29 blocks
........... Used Memory = 35 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 34 blocks
................... SQL = 34 blocks (34 count)

PL/SQL procedure successfully completed.

There is again an init.ora parameter governing the size of max result cache size i.e result_cache_max_size

This value is also seen in dbms_result_cache.memory_report output in "Maximum Cache Size " header.

Conclusion: SQL Result cache seems to be a great feature which can immensly improve the sql performance of many applications SQLs. I wish there are no major bugs in this :)

Note: Its just the begining that I have started looking into these features. If you happen to research something which I may have missed, please add to the note.