Oracle 11g新特性:SQL Query Result Cache

妖狐艹你老母 2022-05-28 09:45 177阅读 0赞

Oracle 在 Shared\_Pool 中增加了 Server Result Cache 结构，新的 SQL query Result Cache 技术，解决了很多重复查询语句导致资源开销过大的典型问题！极大提高了查询效率。

SQL Query Result Cache：存储查询和查询片段的结果。

PL/SQL Function Result Cache：存储函数的结果集。

Result Cache技术适合的场景：

1) 查询的记录数很多，但返回结果数据较少的应用

2) 重复查询频度比较高

3) 数据相对静态，变化量不大

例如，数据仓库系统的各种统计运算就是比较典型的应用场景。

1、 Result Cache原理

存储SQL查询结果以备重用（跨语句、跨会话），如果数据发生改变，Result Cache中相应数据将变成INVALID状态,直到下次查询，再重新从硬盘取数据存储到Result Cache。

Result cache与buffer cache的不同：

Buffer cache缓存的是数据，再次select需要到内存中访问并整理出结果集。

Result cache缓存的就是select结果!（即无需再大量逻辑读）

例如：

\--从buffer cache读时，逻辑读始终是6，而使用Result Cache，每次逻辑读为0

<table style="color:rgb(102,102,102);font-family:'宋体', Arial;font-size:12px;line-height:26px;border:none;"> 
 <tbody> 
 <tr> 
 <td style="margin:0px;padding:0px;border:1pt solid;">SQL&gt; select job_id,avg(min_salary) from jobs group by job_id; Execution Plan----------------------------------------------------------Plan hash value: 2795457283------------------------------------------------------------------------------------------| Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; | Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Rows&nbsp; | Bytes | Cost (%CPU)| Time&nbsp;&nbsp; |------------------------------------------------------------------------------------------|&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 19 |&nbsp;&nbsp; 228 |&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp; (0)| 00:00:01 ||&nbsp;&nbsp; 1 |&nbsp; SORT GROUP BY NOSORT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 19 |&nbsp;&nbsp; 228 |&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp; (0)| 00:00:01 ||&nbsp;&nbsp; 2 |&nbsp;&nbsp; TABLE ACCESS BY INDEX ROWID| JOBS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 19 |&nbsp;&nbsp; 228 |&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp; (0)| 00:00:01 ||&nbsp;&nbsp; 3 |&nbsp;&nbsp;&nbsp; INDEX FULL SCAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; | JOB_ID_PK |&nbsp;&nbsp;&nbsp; 19 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp; (0)| 00:00:01 |------------------------------------------------------------------------------------------Statistics----------------------------------------------------------&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 0&nbsp; recursive calls&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 0&nbsp; db block gets&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;6&nbsp; consistent gets&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 0&nbsp; physical reads&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 0&nbsp; redo size&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 888&nbsp; bytes sent via SQL*Net to client&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 431&nbsp; bytes received via SQL*Net from client&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 3&nbsp; SQL*Net roundtrips to/from client&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 0&nbsp; sorts (memory)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 0&nbsp; sorts (disk)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;19&nbsp; rows processed</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;padding:0px;border:1pt solid;">SQL&gt; select&nbsp;/*+ RESULT_CACHE */&nbsp;job_id,avg(min_salary) from jobs group by job_id;&nbsp;Execution Plan----------------------------------------------------------Plan hash value: 2795457283------------------------------------------------------------------------------------------------------------| Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; | Rows&nbsp; | Bytes | Cost (%CPU)| Time&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; |------------------------------------------------------------------------------------------------------------|&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;|&nbsp; 19 |&nbsp;&nbsp; 228 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp; (0)| 00:00:01 ||&nbsp;&nbsp; 1 |&nbsp;&nbsp;RESULT CACHE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | gh2hs3c3kyq7q731frs1zgnpja |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; |&nbsp; &nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; ||&nbsp;&nbsp; 2 |&nbsp;&nbsp; SORT GROUP BY NOSORT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;|&nbsp; 19 |&nbsp;&nbsp; 228 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp; (0)| 00:00:01 ||&nbsp;&nbsp; 3 |&nbsp;&nbsp;&nbsp; TABLE ACCESS BY INDEX ROWID| JOBS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; |&nbsp; 19 |&nbsp;&nbsp; 228 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp; (0)| 00:00:01 ||&nbsp;&nbsp; 4 |&nbsp;&nbsp;&nbsp;&nbsp; INDEX FULL SCAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | JOB_ID_PK&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; |&nbsp; 19 |&nbsp;&nbsp; &nbsp;&nbsp; |&nbsp; 1&nbsp;&nbsp; (0)| 00:00:01 |------------------------------------------------------------------------------------------------------------Result Cache Information (identified by operation id):------------------------------------------------------&nbsp;&nbsp;&nbsp; 1 - column-count=2; dependencies=(HR.JOBS); parameters=(nls); name="select /*+ RESULT_CACHE */ job_id,avg(min_salary) from jobs&nbsp;group by job_id"&nbsp;Statistics----------------------------------------------------------&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 0&nbsp; recursive calls&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 0&nbsp; db block gets&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;0&nbsp; consistent gets&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 0&nbsp; physical reads&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 0&nbsp; redo size&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 888&nbsp; bytes sent via SQL*Net to client&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 431&nbsp; bytes received via SQL*Net from client&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 3&nbsp; SQL*Net roundtrips to/from client&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 0&nbsp; sorts (memory)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 0&nbsp; sorts (disk)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;19&nbsp; rows processed</td> 
 </tr> 
 </tbody> 
</table>

物化视图和Result Cache不同：  
1）MV把结果记录在硬盘上，是永久的记录，而Result Cache则存储在内存里。  
   当数据库关闭或者result cache空间不足，这些信息会被删除。  
2）MV具有静态特性，当query\_rewrite\_integrity=stale\_tolerated时，如果不手动刷新MV，用户通过MV查询到的数据可能会不正确。  
   而Sql Result Cache存储的sql发生变化时，cache刷新是不可避免的。  
3）Result Cache的限制：系统表和临时表、sequence.nextval, sequence.currval、sysdate、systimestamp、所有非确定性Pl/Sql函数。

2、Result Cache使用

1)  初始化参数

\--RESULT\_CACHE\_MODE

?  MANUAL: 使用RESULT\_CACHE hint 方式来指定结果存储到Result cache中。（缺省值）

?  FORCE:   所有结果都存储到Result Cache中。（不建议采取该策略!）

\--RESULT\_CACHE\_MAX\_SIZE

该参数设置Result Cache的最大容量。如果设置为0，则将关闭Result Cache功能。该参数的缺省值，依赖于内存管理模式和相关参数配置。例如：

?  当只设置memory\_target参数时，RESULT\_CACHE\_MAX\_SIZE = memory\_target\*0.25%。

?  当设置sga\_target参数时，RESULT\_CACHE\_MAX\_SIZE = sga\_target\*0.5%。

?  当设置shared\_pool\_size参数时，RESULT\_CACHE\_MAX\_SIZE = shared\_pool\_size\*1%。

该参数最大不能超过shared\_pool\_size的75%。

注：需重启库生效，（SQL> select dbms\_result\_cache.status from dual;）

\--RESULT\_CACHE\_MAX\_RESULT

该参数为单个SQL查询语句设置可使用的最大Result Cache容量，

缺省为RESULT\_CACHE\_MAX\_SIZE的5%。

\--RESULT\_CACHE\_REMOTE\_EXPIRATION

该参数表示当SQL语句访问远程数据库对象时，允许远程对象数据发生变化的过期时间。

缺省值为0，表示一旦远程对象数据发生变化，相关查询的Result Cache数据变为INVALID。

2) 使用

\--加hint使用

select /\*+ RESULT\_CACHE \*/ job\_id,avg(min\_salary) from jobs group by job\_id;

select /\*+ NO\_RESULT\_CACHE \*/ job\_id,avg(min\_salary) from jobs group by job\_id;

\--子查询中使用hint

即存储查询片段到Result Cache，不会受外围条件变化的影响。

\--表级控制使用Result Cache

CREATE TABLE jobs (...) RESULT\_CACHE (MODE DEFAULT);

ALTER TABLE jobs RESULT\_CACHE (MODE FORCE);

3）管理

==包的使用

\--Result Cache状态查询：

\--Result Cache使用情况查询：

\--清空Result Cache

\--将指定表的Result Cache设置为INVALID

==视图的使用

\--查询Result Cache内存统计信息

\--查看Result cache内存块的相关统计信息

\--显示Result Cache中缓存的对象，包括结果集和依赖的表相关数据

\--查询结果集数据与依赖表的关联关系

3、注意事项如下：

4、Result Cache相关

Result Cache与RAC

Result Cache与并行处理

5、客户端Result Cache技术

暂略。。。

**6 常用查询表**  
V$RESULT\_CACHE\_STATISTICS：列出服务端缓存设置和内存使用的统计信息  
V$RESULT\_CACHE\_MEMORY：列出所有服务端内存块缓存和相应的统计结果  
V$RESULT\_CACHE\_OBJECTS：列出服务端缓存的对象，以及属性  
V$RESULT\_CACHE\_DEPENDENCYL：列出服务端缓存依赖的细节  
CLIENT\_RESULT\_CACHE\_STATS$  
DBA\_TABLES, USER\_TABLES, ALL\_TABLES  
  
  
SELECT NAME, VALUE  FROM   V$RESULT\_CACHE\_STATISTICS;   --列出服务端缓存的统计信息  
  
SELECT STAT\_ID, SUBSTR(NAME,1,20), VALUE, CACHE\_ID  FROM   CLIENT\_RESULT\_CACHE\_STATS$  ORDER BY CACHE\_ID, STAT\_ID; --监控客户端缓存的统计信息

检查result cache配置是否生效  
1.select dbms\_result\_cache.status from dual;  
2.dbms\_result\_cache.MEMORY\_REPORT  
3.查看sql执行计划

**注意:**  
11g的active dataguard的备库是不能使用result cache的，这是oracle的一个bug，因为涉及到内码的问题，所以oracle一直没有修复，result cache目前可以使用在单节点主库和rac环境。

关于这个新特性的参考链接:  
http://docs.oracle.com/cd/E11882\_01/server.112/e41573/memory.htm\#PFGRF983  
http://www.ningoo.net/2007/08/22/oracle11g\_new\_feature\_server\_result\_cache2.htm  
http://yangtingkun.itpub.net/post/468/391015  
http://yangtingkun.itpub.net/post/468/391560

检查result cache配置是否生效  
1.select dbms\_result\_cache.status from dual;  
2.dbms\_result\_cache.MEMORY\_REPORT  
3.查看sql执行计划