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
Rolex propose une replique rolex montres large gamme de modèles, des montres professionnelles fausses rolex cosmograph daytona montres aux montres classiques, qui s'adaptent à tous les poignets. Choisissez votre modèle préféré, la matière, la lunette, le cadran et le bracelet pour explorer la collection Rolex et trouver la montre qui vous convient.
ReplyDeletehttps://www.cgmimm.com/articles/hands-on-rolex-gmt-master-ii-126710blnr-blue-black-bezel-watch/
ReplyDeletehttps://www.keepandshare.com/discuss3/4708/hands-on-rolex-gmt-master-ii-126710blnr-blue-black-bezel-watch/
https://fytfyuguy.seesaa.net/
https://tinyurl.com/y24dcn5l/
https://tinyurl.com/yy5b33af/
https://tinyurl.com/y257stjm/
https://rb.gy/vbpj49/
https://rb.gy/ugy1j4/
https://rb.gy/1pc6uz/
cheap balenciaga trainers is a Spanish luxury fashion house founded in 1917 by the cheap balenciaga allover logo triple s sneaker Basque designer Val Balenciaga. Balenciaga has a reputation as a strict standard for fashion designers, and is known as the "Master of All America" Dior. His bubble skirt and peculiar, feminine but "modern" silhouette became the trademark of the house.
ReplyDelete