温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

oracle 11.2 result_cache说明

发布时间:2020-08-14 15:27:30 来源:网络 阅读:876 作者:snowhill 栏目:关系型数据库

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
[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

删除所有现有结果并清空高速缓存:
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_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 执行计划查看
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系统/报表系统中使用

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI