您好,登錄后才能下訂單哦!
1 相關(guān)參數(shù)
RESULT_CACHE_MAX_RESULT:指定任一結(jié)果可使用的最大高速緩存量,默認(rèn)值為5%,但可指定1 到100 之間的任一百分比值,可在系統(tǒng)和會(huì)話(huà)級(jí)別上實(shí)施此參數(shù);
result_cache_max_size :32k的整數(shù)倍,如果將結(jié)果高速緩存的值設(shè)為0,則會(huì)禁用此結(jié)果高速緩存,不能超過(guò)共享池的75%;
RESULT_CACHE_REMOTE_EXPIRATION:可以指定依賴(lài)于遠(yuǎn)程數(shù)據(jù)庫(kù)對(duì)象的結(jié)果保持有效的時(shí)間(以分鐘為單位),默認(rèn)值為0,表示不會(huì)使用高速緩存使用遠(yuǎn)程對(duì)象的結(jié)果;
result_cache_mode :默認(rèn)為 MANUAL,即加hint /+ result_cache / 才使用result cache
啟用result cache:
alter system set result_cache_max_result=5;
alter system set result_cache_max_size=20m;
2 相關(guān)存儲(chǔ)過(guò)程
查詢(xún)內(nèi)存分配情況:
SQL>set serveroutput 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 = 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
刪除所有現(xiàn)有結(jié)果并清空高速緩存:EXECUTE DBMS_RESULT_CACHE.FLUSH;
檢查:select * from GV$RESULT_CACHE_OBJECTS
使特定結(jié)果失效:
begin
DBMS_RESULT_CACHE.INVALIDATE('SH','SALES');
end;
select cache_id,lru_number,db_link,status,bucket_no,hash,name,namespace,type,status
from GV$RESULT_CACHE_OBJECTS where name like '%sales%';
CACHE_ID LRU_NUMBER DB_LINK STATUS BUCKET_NO HASH NAME NAMESPACE TYPE STATUS
3gqafv8xzpk9t535y6dgfmyhjt 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 執(zhí)行計(jì)劃查看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"
可以看出為result cache;不使用result cache:
select 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 | 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 |
也可以強(qiáng)制對(duì)象使用result cache:alter table sales result_cache(mode force);
取消:alter table sales result_cache(mode default);
4 相關(guān)視圖
(G)V$RESULT_CACHE_STATISTICS : 列出各種高速緩存設(shè)置和內(nèi)存使用量統(tǒng)計(jì)信息
select from V$RESULT_CACHE_STATISTICS
(G)V$RESULT_CACHE_MEMORY : 列出所有內(nèi)存塊和相應(yīng)的統(tǒng)計(jì)信息
select from V$RESULT_CACHE_MEMORY
(G)V$RESULT_CACHE_OBJECTS: 列出所有對(duì)象(高速緩存結(jié)果和依賴(lài)性)及其屬性
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: 列出高速緩存結(jié)果之間的依賴(lài)性詳細(xì)信息及依賴(lài)性
select b.owner,b.object_name,a.* from V$RESULT_CACHE_DEPENDENCY a,all_objects b where a.object_no=b.object_id;
5 注意事項(xiàng)
1 綁定變量不同,不可以命中
2 最適用于需要訪問(wèn)大量行卻僅返回其中一少部分的語(yǔ)句,建議使用在OLAP系統(tǒng)/報(bào)表系統(tǒng)中使用
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。