Shulou(Shulou.com)06/01 Report--

1 related parameters

RESULT_CACHE_MAX_RESULT: specify the maximum amount of cache that can be used by any result. The default value is 5%, but any 100-point ratio between 1 and 100 can be specified. This parameter can be implemented at the system and session level.

Result_cache_max_size: an integer multiple of 32k. If the result cache is set to 0, the result cache will be disabled, which cannot exceed 75% of the shared pool.

RESULT_CACHE_REMOTE_EXPIRATION: you can specify how long the results that depend on remote database objects remain valid (in minutes). The default value is 0, which means that the results of remote objects will not be cached.

Result_cache_mode: defaults to MANUAL, that is, adding hint / + result_cache / before using result cache

Enable result cache:

Alter system set result_cache_max_result=5;alter system set result_cache_max_size=20m

2 related stored procedures

Query memory allocation:

SQL > set serveroutput on; SQL > exec dbms_result_cache.memory_reportR esult Cache Memory Re por t [parameters] Block Size = 1K bytesMaximum Cache Size = 1280K bytes (1280 blocks) Maximum Result Size = 64K bytes (64 blocks) [Memory] Total Memory = 202160 bytes [0.110% of the Shared Pool]. Fixed Memory = 5352 bytes [0.003% of the Shared Pool]. Dynamic Memory = 196808 bytes [0.107 of the Shared Pool]. Overhead = 131272 bytes. Cache Memory = 64K bytes (64 blocks). Unused Memory = 29 blocks. Used Memory = 35 blocks. Dependencies = 10 blocks (10 count). Results = 25 blocks. SQL = 20 blocks (20 count). Invalid = 5 blocks (5 count) SQL > select dbms_result_cache.status from dual; ENABLED

Delete all existing results and clear the cache:

EXECUTE DBMS_RESULT_CACHE.FLUSH

Check:

Select * from GV$RESULT_CACHE_OBJECTS

To invalidate a particular result:

BeginDBMS_RESULT_CACHE.INVALIDATE ('SH','SALES'); end;select cache_id,lru_number,db_link,status,bucket_no,hash,name,namespace,type,statusfrom GV$RESULT_CACHE_OBJECTS where name like'% sales%' CACHE_ID LRU_NUMBER DB_LINK STATUS BUCKET_NO HASH NAME NAMESPACE TYPE STATUS3gqafv8xzpk9t535y6dgfmyhjt 0 No Invalid 2055 571566087 "select / * + result_cache * / prod_id,sum (amount_sold) from sh.sales group by prod_id order by prod_id" SQL Result** Invalid**

3 execution plan view

SQL > select / * + result_cache * / prod_id,sum (amount_sold) from sales group by prod_id order by prod_id

Execution Plan

Plan hash value: 4109827725- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop | | 0 | SELECT STATEMENT | 72 | 648 | 680 (24) | 00:00:09 | 1 | RESULT CACHE | g23n3fafz6vxs65351bmca3jq8 | | | 2 | SORT GROUP BY | | 72 | 648 | 680 (24) | 00:00:09 | 3 | PARTITION RANGE ALL | | 918K | 8075K | 557 (8) | 00:00:07 | 1 | 28 | 4 | TABLE ACCESS FULL | SALES | | | 918K | 8075K | 557 (8) | 00:00:07 | 1 | 28 | Result Cache Information (identified by operation id): 1-column-count=2 | Dependencies= (SH.SALES); name= "select / * + result_cache * / prod_id,sum (amount_sold) from sales group by prod_id order by prod_id"

You can see that result cache; does not use result cache:

Select prod_id,sum (amount_sold) from sales group by prod_id order by prod_id Execution Plan---Plan hash value: 4109827725 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |-- -- | 0 | SELECT STATEMENT | | 72 | 648 | 680 (24) | 00:00:09 | | 1 | SORT GROUP BY | | 72 | 648 | 680 (24) | 00:00:09 | | | 2 | PARTITION RANGE ALL | | 918K | 8075K | 557 (8) | 00:00:07 | 1 | 28 | 3 | TABLE ACCESS FULL | SALES | 918K | 8075K | 557 (8) | 00:00:07 | 1 | 28 | |

You can also force objects to use result cache:

Alter table sales result_cache (mode force)

Cancel:

Alter table sales result_cache (mode default)

4 related views

(G) V$RESULT_CACHE_STATISTICS: lists various cache settings and memory usage statistics

Select from V$RESULT_CACHE_STATISTICS

(G) V$RESULT_CACHE_MEMORY: lists all memory blocks and corresponding statistics

Select from V$RESULT_CACHE_MEMORY

(G) V$RESULT_CACHE_OBJECTS: lists all objects (cache results and dependencies) and their properties

Select cache_id,lru_number,db_link,status,bucket_no,hash,name,namespace,type,status

From GV$RESULT_CACHE_OBJECTS where name like'% sales%'

(G) V$RESULT_CACHE_DEPENDENCY: lists the details and dependencies between cached results

Select b.owner.b.objectpapername.a.* from V$RESULT_CACHE_DEPENDENCY arecedicalobjects b where a.object_no=b.object_id

5 points for attention

1 the binding variable is different and cannot be hit

2 is most suitable for statements that need to access a large number of rows but return only a small portion of them. It is recommended to use them in OLAP / report systems.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.