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
- 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_SIZEthat 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?
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
Enable the traceonly and run the test query
Connect as normal user
“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.
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
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;
Show the result cache usage statistics
Show the memory usage for the result cache
Result Cache: http://www.oracle-developer.net/display.php?id=503