3 Result Cache

When the SQL comes into oracle instance, oracle would look in the library cache to see if the same query has been executed before. If the query is found, it would use the same query plan and only check for permission. This is called ‘Soft parse’. If the query is not found, the oracle instance would parse the query , create the query plan…etc and store into the shared sql area. This is called Hard parse.

From 11g, oracle add the result cache.  When the query comes in, oracle would not just look for the query plan, but also look for the result cache. If the same query has been executed before, it would get the result from the cache directly. Oracle would also detect the result’s underline table changes. If the data has been changes which cause the result is not accurate. Oracle would mark the query result invalid and rerun the query when next time query is executed , then store it into the result cache.

Result cache store the final result set for the query. The database buffer cache store the data that may still need to be parse ( either soft/hard parse ) by the optimizer . If result cache can satisfy the query , then there is no need to parse the query at all. If the result set is not able to satisfy , then the optimizer  look for the library cache to find the query plan and try to use data buffer cache to build the result set. If db buffer cache does not have the all the data block, then it become the physical IO and needs to go to the database file.

Below picture is from here

Show result cache parameter

show parameter result_cache

• result_cache_max_size: The size of the result cache. 0 means disable. the max size can not exceed the 75% of the total SHARED_POOL . Below is the recommend value from oracle

AMM	MEMORY_TARGET is set	0.25% * MEMORY_TARGET
ASMM	SGA_TARGET is set	0.5% * SGA_TARGET
others	No MEMORY_TARGET/SGA_TARGET	1%^ SHARED_POOL_SIZE

• result_cache_max_result: specifies the percentage of RESULT_CACHE_MAX_SIZE that any single result can use. default is 5% .  If the value is 0. then the feature is disable.
• result_cache_mode: MANUAL,AUTO
  • AUTO: Let Oracle decide whether the query needs to store in the result cache.
  • FORCE: All query store in the result cache without the query hint.
  • MANUAL: Let user decide. The query needs to have RESULT_CACHE hint in order to store in the result cache
• result_cache_remote_expiration: specifies the number of minutes that a result using a remote object is allowed to remain valid. 0 means that results using remote objects does does not store in the cache.

How to check whether result_cache is enable?

query DBMS_RESULT_CACHE.status

SELECT DBMS_RESULT_CACHE.status FROM dual;

How to enable result_cache

Result Cache is Enterprise Edition ONLY features.

• Shared_pool_size must be explicitly set ( CAN NOT be 0 , even in ASMM)
• result_cache_max_size must greater than 0.

After playing around with shared cache for a while, at some point, even I set the result_cache_max_size , but the value would become 0 after instance bounce. and even before the bounce. the result cache still not work. Then I found this and oracle case  1088172.1. It explain the issue.

After setting the result_cache_max_size from 0 to other value, we must bounce the instance in order to take effective.

Setting up the environment

Since the result_cache does not work for sysdba, we have to create the normal user and grant PLUSTRACE role. Refer here for PLUSTRACE.

• Create normal user
• Install PLUSTRACE.sql
• Grant permission
• Create test table
• Enable collect statistic

create user PO identified by oracle

default tablespace users

quota unlimited on users;

grant connect,resource,PLUSTRACE to PO;

create table po.table1 as select * from dba_objects;

exec dbms_stats.gather_table_stats( 'PO','table1');

Enable the traceonly and run the test query

Connect as normal user

sqlplus PO/oracle@192.168.56.101/orcl

set autotrace traceonly

set timing on

select owner,count(*) from table1 group by owner order by count(*);

“consistent gets” means number of times a consistent read (logical IO) was requested to get data from a data block. Below screen shot shows the query have 1073 logical IO and take 0.11 sec to execute.

Use /*+ RESULT_CACHE */ query hint.

select  /*+ RESULT_CACHE */  owner,count(*) from table1 group by owner order by count(*);

The logical read is almost identical . The RESULT_CACHE ‘g2rcpup10saqu4kc0jr1n977f9’ is created.

Lets run the same query again, the query time reduce and the logical read is only 2. In the ideal world , this should be 0. The query is getting result from result cache. In order for the query use the rsult_cache, we must use query hint, this is because the result_cache_mode is set to MANUAL.

Check the Cache

col Name format a40

select name,id,status from v$result_cache_objects;

Clean the cache

This would clean the result_cache

exec dbms_result_cache.flush;

Result_cache_mode

Once the result_cache_mode set to auto, than all the query would use the result_cache even without the query hint.

alter system set result_cache_mode=auto;

V$result_cache_dependency;

select * from v$result_cache_dependency;

 

V$result_cache_statistics:

Show the result cache usage statistics

select * from V$result_cache_statistics;

DBMS_RESULT_CACHE.memory_report

Show the memory usage for the result cache

set SERVEROUTPUT on

exec DBMS_RESULT_CACHE.memory_report

 

Reference

Result Cache: http://www.oracle-developer.net/display.php?id=503

http://www.oracle.com/technetwork/articles/datawarehouse/vallath-resultcache-rac-284280.html