11g 新特性之--Query cache result

1、 Oracle 11g 新特性之-Query Cache Result该特性是11gR1引入的，关于query result cache特性，主要有2种：1. PL/SQL Function Result Cache -针对plsql而言 2. Query Result Cache -顾名思义针对重复执行的sql我们都知道oracle通常是通过参数来进行控制某个功能的，当然这个也不例外，首先我们来介绍跟该特性有关的几个参数(包括隐含参数）：SQL select * from v$version where rownum show parameter resultNAME TYPE VALUE-

2、 - -_client_result_cache_bypass boolean FALSE_result_cache_auto_execution_threshold integer 1 _result_cache_auto_size_threshold integer 100_result_cache_auto_time_distance integer 300_result_cache_auto_time_threshold integer 1000_result_cache_block_size integer 1024_result_cache_global boolean TRUE_

3、result_cache_timeout integer 10_xsolapi_sql_result_set_cache_size integer 32client_result_cache_lag big integer 3000client_result_cache_size big integer 0result_cache_max_result integer 5result_cache_max_size big integer 960Kresult_cache_mode string MANUALresult_cache_remote_expiration integer 0几个重要

4、的参数：result_cache_mode该参数是最为重要的，其属性有manual和force 两种。manual是默认属性，也就是说我们要启用该特性，那么必须通过hint来实现，不然oracle的优化器是无法认知的，那么是什么hint呢？ 如下：SQL select name,version from v$sql_hint 2 where name like %RESULT%NAME VERSION- -RESULT_CACHE 11.1.0.6NO_RESULT_CACHE 11.1.0.6当设置为force时，oracle 优化就能自动识别了，不需要使用hint，相反，如果当设置为for

5、ce时，同时你又不想某个sql或应用使用该特性，那么可以使用NO_RESUIT_CACHE hint来进行避规。该参数其实还可以设置为auto，不过文档中未提及。至于说，当启动该特性时，oracle是如何来实现的？这个问题需要进一步研究。result_cache_max_size 该参数控制着使用该特性的内存大小，当该参数设置为0，那么也就意味着关闭了该特性。该部分内存是从SGA中分配的，至于分配的比例关系，metalink提供了如下的数据：0.25% of MEMORY_TARGET or0.5% of SGA_TARGET or1% of SHARED_POOL_SIZE上面的关系应该是一

6、目了然了，如何解释？我暂且不说，给大家留个问题。result_cache_max_result该参数是控制单个result所能占据query cache的大小比例，注意是一个百分比。该参数默认是是5%，取值范围当然是1% 100% 了。result_cache_remote_expiration该参数的作用是根据远程数据库对象设置缓存过期的时间，默认值为0.也就是说，默认情况下，远程数据库对象不会被进行cache的。_result_cache_global顾名思义，该参数肯定是针对Rac集群而设计的，这样可以大大的降低经典的gc等待。下面通过相关的实验操作来进行详细的说明:SQL create

7、 table ht01 as select owner,object_name,object_id from 2 dba_objects where object_id create index idx_ht01_id on ht01(object_id);Index created.SQL set autot traceonlySQL set timing onSQL select owner,object_name from ht01 where object_id=888;Elapsed: 00:00:00.20Execution Plan-Plan hash value: 267115

8、5529-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 1 | 96 | 2 (0)| 00:00:01 | 1 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 96 | 2 (0)| 00:00:01 |* 2 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 |-Predicate Information (identified by operation id):

9、- 2 - access(OBJECT_ID=888)Note- - dynamic sampling used for this statement (level=2)Statistics- 406 recursive calls 4 db block gets 64 consistent gets 0 physical reads 0 redo size 501 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 s

10、orts (memory) 0 sorts (disk) 1 rows processedSQL select /*+ RESULT_CACHE */ owner,object_name 2 from ht01 where object_id=888;Elapsed: 00:00:00.17Execution Plan-Plan hash value: 2671155529-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 1 | 96 | 2 (0)| 00:00:0

11、1 | 1 | RESULT CACHE | 0mn43k8b004mrgacy3snrb9ff7 | | | | | 2 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 96 | 2 (0)| 00:00:01 |* 3 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 |-Predicate Information (identified by operation id):- 3 - access(OBJECT_ID=888)Result Cache Information (identif

12、ied by operation id):- 1 - column-count=2; dependencies=(ROGER.HT01); attributes=(ordered); name=select /*+ RESULT_CACHE */ owner,object_namefrom ht01 where object_id=888Note- - dynamic sampling used for this statement (level=2)Statistics- 7 recursive calls 0 db block gets 14 consistent gets 0 physi

13、cal reads 0 redo size 493 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedSQL 发现第一使用hint解析时，消耗较高，下面我们再次执行，看看结果。 大家注意前面的执行计划，红色部分，这里的意思可以理解为oracle首先在执行 该sql执行之前，会到query cache里面去寻找是否有这个sql语句

14、的信息。 如果没有，那么将进行解析，跟以前的理解完全一样。 SQL select owner,object_name from ht01 where object_id=888;Elapsed: 00:00:00.02Execution Plan-Plan hash value: 2671155529-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 1 | 96 | 2 (0)| 00:00:01 | 1 | TABLE ACCESS BY INDEX ROWID|

15、HT01 | 1 | 96 | 2 (0)| 00:00:01 |* 2 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 |-Predicate Information (identified by operation id):- 2 - access(OBJECT_ID=888)Note- - dynamic sampling used for this statement (level=2)Statistics- 0 recursive calls 0 db block gets 4 consistent gets 0 ph

16、ysical reads 0 redo size 501 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedSQL select /*+ RESULT_CACHE */ owner,object_name 2 from ht01 where object_id=888;Elapsed: 00:00:00.02Execution

17、Plan-Plan hash value: 2671155529-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 1 | 96 | 2 (0)| 00:00:01 | 1 | RESULT CACHE | 0mn43k8b004mrgacy3snrb9ff7 | | | | | 2 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 96 | 2 (0)| 00:00:01 |* 3 | INDEX RANGE SCAN | IDX_H

18、T01_ID | 1 | | 1 (0)| 00:00:01 |-Predicate Information (identified by operation id):- 3 - access(OBJECT_ID=888)Result Cache Information (identified by operation id):- 1 - column-count=2; dependencies=(ROGER.HT01); attributes=(ordered); name=select /*+ RESULT_CACHE */ owner,object_namefrom ht01 where

19、 object_id=888Note- - dynamic sampling used for this statement (level=2)Statistics- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 493 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0

20、sorts (disk) 1 rows processedSQL 第2次执行，我们发现消耗非常小。这里大家可以跟前面执行的语句，select owner,object_name from ht01 where object_id=888; 进行对比，即使执行过相同的sql语句，再次执行，那么也仍然有4个逻辑读，为什么呢？答案就是软解析。我们可以发现使用了query cache result特性后，逻辑读为0. 效率明显高很多。这里为什么query cache result这么强大，其他他这里就是发现cache里面已经存在了，那么连软解析就不用了，直接从cache里面返回结果给客户端。下面我们将

21、该参数设置为force，来看看情况如何。 SQL show user USER is ROGER SQL alter session set result_cache_mode = force; Session altered. Elapsed: 00:00:00.07 SQL select owner,object_name from ht01 where object_id=666; Elapsed: 00:00:00.04 Execution Plan - Plan hash value: 2671155529 - | Id | Operation | Name | Rows | Byt

22、es | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 1 | 96 | 2 (0)| 00:00:01 | | 1 | RESULT CACHE | 6u1h1qaku8rv6bp04nj91w3vvh | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 96 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 | - Predicate Information (identified by operation id): - 3 - access(OBJECT_ID=666) Result Cache Information (identified by operation id): - 1 - column-count=2; dependencies=(ROGER.HT01); attributes=(ordered); name=select owner,object_name from ht01 where object_id=66