oracle 11.2 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_report
R e s u l t C a c h e M e m o r y R e p o r t
Block>
Maximum Cache>
Maximum Result>
Total Memory = 202160 bytes
... Fixed Memory = 5352 bytes
... Dynamic Memory = 196808 bytes
....... 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
使特定结果失效：
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_NUMBERDB_LINK STATUSBUCKET_NO HASH NAME NAMESPACE TYPE STATUS
3gqafv8xzpk9t535y6dgfmyhjt0 NoInvalid 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
--------------------------------------------------------------------------------------------------------------------
|>
| 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>
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
----------------------------------------------------------------------------------------------
|>
----------------------------------------------------------------------------------------------
| 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 : 列出所有内存块和相应的统计信息
selectfrom 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系统/报表系统中使用
页:
[1]