oracle 11.2 result_cache說明

標籤：oracle result cache

1 相關參數
RESULT_CACHE_MAX_RESULT：指定任一結果可使用的最大快取量，預設值為5%，但可指定1 到100 之間的任一百分比值，可在系統和會話層級上實施此參數；
result_cache_max_size :32k的整數倍，如果將結果快取的值設為0，則會禁用此結果快取，不能超過共用池的75%;
RESULT_CACHE_REMOTE_EXPIRATION：可以指定依賴於遠端資料庫對象的結果保持有效時間（以分鐘為單位），預設值為0，表示不會使用快取使用遠程對象的結果；
result_cache_mode ：預設為 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 相關預存程序
查詢記憶體配置情況：

SQL>set serveroutput on ;SQL> exec dbms_result_cache.memory_reportR 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 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

刪除所有現有結果並清空快取：
EXECUTE DBMS_RESULT_CACHE.FLUSH;
檢查：
select * from GV$RESULT_CACHE_OBJECTS
使特定結果失效：

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 執行計畫查看
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 |

也可以強制對象使用result cache:
alter table sales result_cache(mode force);
取消：
alter table sales result_cache(mode default);
4 相關視圖
(G)V$RESULT_CACHE_STATISTICS : 列出各種快取設定和記憶體使用量量統計資訊
select from V$RESULT_CACHE_STATISTICS
(G)V$RESULT_CACHE_MEMORY : 列出所有記憶體塊和相應的統計資訊
select from V$RESULT_CACHE_MEMORY
(G)V$RESULT_CACHE_OBJECTS: 列出所有對象（快取結果和依賴性）及其屬性
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: 列出快取結果之間的依賴性詳細資料及依賴性
select b.owner,b.object_name,a.* from V$RESULT_CACHE_DEPENDENCY a,all_objects b where a.object_no=b.object_id;

5 注意事項
1 綁定變數不同，不可以命中
2 最適用於需要訪問大量行卻僅返回其中一少部分的語句，建議使用在OLAP系統/報表系統中使用

oracle 11.2 result_cache說明