11g 新特性之–query result cache(1)
<P>本文原链接，个人站点：<a href="http://www.killdb.com/?p=205" target="_blank">http://www.killdb.com/?p=205</A></P><P>该特性是11gR1引入的，关于query result cache特性，主要有2种：<BR>1. PL/SQL Function Result Cache --针对plsql而言 <BR>2. Query Result Cache --顾名思义针对重复执行的sql</P>
<P>我们都知道oracle通常是通过参数来进行控制某个功能的，当然这个也不例外，<BR>首先我们来介绍跟该特性有关的几个参数(包括隐含参数）：<BR>SQL> select * from v$version where rownum <2;</P>
<P>BANNER<BR>--------------------------------------------------------------------------------<BR>Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production</P>
<P>SQL> show parameter result</P>
<P>NAME TYPE VALUE<BR>------------------------------------ ----------- ------------------------------<BR>_client_result_cache_bypass boolean FALSE<BR>_result_cache_auto_execution_threshold integer 1 <BR>_result_cache_auto_size_threshold integer 100<BR>_result_cache_auto_time_distance integer 300<BR>_result_cache_auto_time_threshold integer 1000<BR>_result_cache_block_size integer 1024<BR>_result_cache_global boolean TRUE<BR>_result_cache_timeout integer 10<BR>_xsolapi_sql_result_set_cache_size integer 32<BR>client_result_cache_lag big integer 3000<BR>client_result_cache_size big integer 0<BR>result_cache_max_result integer 5<BR>result_cache_max_size big integer 960K<BR>result_cache_mode string MANUAL<BR>result_cache_remote_expiration integer 0</P>
<P>几个重要的参数：</P>
<P>result_cache_mode</P>
<P>该参数是最为重要的，其属性有manual和force 两种。<BR>manual是默认属性，也就是说我们要启用该特性，那么必须通过hint来实现，不然oracle的优化器<BR>是无法认知的，那么是什么hint呢？ 如下：<BR>SQL> select name,version from v$sql_hint<BR> 2 where name like '%RESULT%'</P>
<P>NAME VERSION<BR>---------------------------------------- -------------------------<BR>RESULT_CACHE 11.1.0.6<BR>NO_RESULT_CACHE 11.1.0.6</P>
<P>当设置为force时，oracle 优化就能自动识别了，不需要使用hint，相反，如果当设置为force时，同时<BR>你又不想某个sql或应用使用该特性，那么可以使用NO_RESUIT_CACHE hint来进行避规。</P>
<P>至于说，当启动该特性时，oracle是如何来实现的？这个问题需要进一步研究。</P>
<P><BR>result_cache_max_size </P>
<P>该参数控制着使用该特性的内存大小，当该参数设置为0，那么也就意味着关闭了该特性。<BR>该部分内存是从SGA中分配的，至于分配的比例关系，metalink提供了如下的数据：<BR>0.25% of MEMORY_TARGET or<BR>0.5% of SGA_TARGET or<BR>1% of SHARED_POOL_SIZE<BR>上面的关系应该是一目了然了，如何解释？我暂且不说，给大家留个问题。</P>
<P>result_cache_max_result</P>
<P>该参数是控制单个result所能占据query cache的大小比例，注意是一个百分比。<BR>该参数默认是是5%，取值范围当然是1% ~ 100% 了。</P>
<P>result_cache_remote_expiration</P>
<P>该参数的作用是根据远程数据库对象设置缓存过期的时间，默认值为0.<BR>也就是说，默认情况下，远程数据库对象不会被进行cache的。</P>
<P><BR>_result_cache_global</P>
<P>顾名思义，该参数肯定是针对Rac集群而设计的，这样可以大大的降低经典的gc等待。</P>
<P>下面通过相关的实验操作来进行详细的说明:<BR>SQL> create table ht01 as select owner,object_name,object_id from<BR> 2 dba_objects where object_id <1000;</P>
<P>Table created.</P>
<P>SQL> create index idx_ht01_id on ht01(object_id);</P>
<P>Index created.</P>
<P>SQL> set autot traceonly<BR>SQL> set timing on<BR>SQL> select owner,object_name from ht01 where object_id=888;</P>
<P>Elapsed: 00:00:00.20</P>
<P>Execution Plan<BR>----------------------------------------------------------<BR>Plan hash value: 2671155529</P>
<P>-------------------------------------------------------------------------------------------<BR>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR>-------------------------------------------------------------------------------------------<BR>| 0 | SELECT STATEMENT | | 1 | 96 | 2 (0)| 00:00:01 |<BR>| 1 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 96 | 2 (0)| 00:00:01 |<BR>|* 2 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 |<BR>-------------------------------------------------------------------------------------------</P>
<P>Predicate Information (identified by operation id):<BR>---------------------------------------------------</P>
<P> 2 - access("OBJECT_ID"=888)</P>
<P>Note<BR>-----<BR> - dynamic sampling used for this statement (level=2)</P>
<P><BR>Statistics<BR>----------------------------------------------------------<BR> 406 recursive calls<BR> 4 db block gets<BR> 64 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 501 bytes sent via SQL*Net to client<BR> 415 bytes received via SQL*Net from client<BR> 2 SQL*Net roundtrips to/from client<BR> 6 sorts (memory)<BR> 0 sorts (disk)<BR> 1 rows processed</P>
<P><BR>SQL> select /*+ RESULT_CACHE */ owner,object_name<BR> 2 from ht01 where object_id=888;</P>
<P>Elapsed: 00:00:00.17</P>
<P>Execution Plan<BR>----------------------------------------------------------<BR>Plan hash value: 2671155529</P>
<P>-----------------------------------------------------------------------------------------------------------<BR>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR>-----------------------------------------------------------------------------------------------------------<BR>| 0 | SELECT STATEMENT | | 1 | 96 | 2 (0)| 00:00:01 |<BR>| 1 | RESULT CACHE | 0mn43k8b004mrgacy3snrb9ff7 | | | | |<BR>| 2 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 96 | 2 (0)| 00:00:01 |<BR>|* 3 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 |<BR>-----------------------------------------------------------------------------------------------------------</P>
<P>Predicate Information (identified by operation id):<BR>---------------------------------------------------</P>
<P> 3 - access("OBJECT_ID"=888)</P>
<P>Result Cache Information (identified by operation id):<BR>------------------------------------------------------</P>
<P> 1 - column-count=2; dependencies=(ROGER.HT01); attributes=(ordered); name="select /*+ RESULT_CACHE */ owner,object_name<BR>from ht01 where object_id=888"</P>
<P><BR>Note<BR>-----<BR> - dynamic sampling used for this statement (level=2)</P>
<P><BR>Statistics<BR>----------------------------------------------------------<BR> 7 recursive calls<BR> 0 db block gets<BR> 14 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 493 bytes sent via SQL*Net to client<BR> 415 bytes received via SQL*Net from client<BR> 2 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 1 rows processed</P>
<P>SQL> </P>
<P> 发现第一使用hint解析时，消耗较高，下面我们再次执行，看看结果。<BR> 大家注意前面的执行计划，红色部分，这里的意思可以理解为oracle首先在执行<BR> 该sql执行之前，会到query cache里面去寻找是否有这个sql语句的信息。<BR> 如果没有，那么将进行解析，跟以前的理解完全一样。<BR> <BR>SQL> select owner,object_name from ht01 where object_id=888;</P>
<P>Elapsed: 00:00:00.02</P>
<P>Execution Plan<BR>----------------------------------------------------------<BR>Plan hash value: 2671155529</P>
<P>-------------------------------------------------------------------------------------------<BR>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR>-------------------------------------------------------------------------------------------<BR>| 0 | SELECT STATEMENT | | 1 | 96 | 2 (0)| 00:00:01 |<BR>| 1 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 96 | 2 (0)| 00:00:01 |<BR>|* 2 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 |<BR>-------------------------------------------------------------------------------------------</P>
<P>Predicate Information (identified by operation id):<BR>---------------------------------------------------</P>
<P> 2 - access("OBJECT_ID"=888)</P>
<P>Note<BR>-----<BR> - dynamic sampling used for this statement (level=2)</P>
<P><BR>Statistics<BR>----------------------------------------------------------<BR> 0 recursive calls<BR> 0 db block gets<BR> 4 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 501 bytes sent via SQL*Net to client<BR> 415 bytes received via SQL*Net from client<BR> 2 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 1 rows processed</P>
<P>SQL> select /*+ RESULT_CACHE */ owner,object_name<BR> 2 from ht01 where object_id=888;</P>
<P>Elapsed: 00:00:00.02</P>
<P>Execution Plan<BR>----------------------------------------------------------<BR>Plan hash value: 2671155529</P>
<P>-----------------------------------------------------------------------------------------------------------<BR>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR>-----------------------------------------------------------------------------------------------------------<BR>| 0 | SELECT STATEMENT | | 1 | 96 | 2 (0)| 00:00:01 |<BR>| 1 | RESULT CACHE | 0mn43k8b004mrgacy3snrb9ff7 | | | | |<BR>| 2 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 96 | 2 (0)| 00:00:01 |<BR>|* 3 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 |<BR>-----------------------------------------------------------------------------------------------------------</P>
<P>Predicate Information (identified by operation id):<BR>---------------------------------------------------</P>
<P> 3 - access("OBJECT_ID"=888)</P>
<P>Result Cache Information (identified by operation id):<BR>------------------------------------------------------</P>
<P> 1 - column-count=2; dependencies=(ROGER.HT01); attributes=(ordered); name="select /*+ RESULT_CACHE */ owner,object_name<BR>from ht01 where object_id=888"</P>
<P><BR>Note<BR>-----<BR> - dynamic sampling used for this statement (level=2)</P>
<P><BR>Statistics<BR>----------------------------------------------------------<BR> 0 recursive calls<BR> 0 db block gets<BR> 0 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 493 bytes sent via SQL*Net to client<BR> 415 bytes received via SQL*Net from client<BR> 2 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 1 rows processed</P>
<P>SQL> </P>
<P>第2次执行，我们发现消耗非常小。这里大家可以跟前面执行的语句，<BR>select owner,object_name from ht01 where object_id=888; 进行对比，即使执行过相同<BR>的sql语句，再次执行，那么也仍然有4个逻辑读，为什么呢？答案就是软解析。</P>
<P>我们可以发现使用了query cache result特性后，逻辑读为0. 效率明显高很多。<BR>这里为什么query cache result这么强大，其他他这里就是发现cache里面已经存在了，<BR>那么连软解析就不用了，直接从cache里面返回结果给客户端。</P>
<P><BR>下面我们将该参数设置为force，来看看情况如何。<BR> <BR>SQL> show user <BR>USER is "ROGER" <BR>SQL> alter session set result_cache_mode = force; <BR> <BR>Session altered. <BR> <BR>Elapsed: 00:00:00.07 <BR>SQL> select owner,object_name from ht01 where object_id=666; <BR> <BR>Elapsed: 00:00:00.04 <BR> <BR>Execution Plan <BR>---------------------------------------------------------- <BR>Plan hash value: 2671155529 <BR> <BR>----------------------------------------------------------------------------------------------------------- <BR>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | <BR>----------------------------------------------------------------------------------------------------------- <BR>| 0 | SELECT STATEMENT | | 1 | 96 | 2 (0)| 00:00:01 | <BR>| 1 | RESULT CACHE | 6u1h1qaku8rv6bp04nj91w3vvh | | | | | <BR>| 2 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 96 | 2 (0)| 00:00:01 | <BR>|* 3 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 | <BR>----------------------------------------------------------------------------------------------------------- <BR> <BR>Predicate Information (identified by operation id): <BR>--------------------------------------------------- <BR> <BR> 3 - access("OBJECT_ID"=666) <BR> <BR>Result Cache Information (identified by operation id): <BR>------------------------------------------------------ <BR> <BR> 1 - column-count=2; dependencies=(ROGER.HT01); attributes=(ordered); name="select owner,object_name from ht01 where object_id=666"<BR> <BR>Note <BR>----- <BR> - dynamic sampling used for this statement (level=2) <BR> <BR> <BR>Statistics <BR>---------------------------------------------------------- <BR> 9 recursive calls <BR> 0 db block gets <BR> 14 consistent gets <BR> 0 physical reads <BR> 0 redo size <BR> 493 bytes sent via SQL*Net to client <BR> 415 bytes received via SQL*Net from client <BR> 2 SQL*Net roundtrips to/from client <BR> 0 sorts (memory) <BR> 0 sorts (disk) <BR> 1 rows processed <BR> <BR>SQL> select owner,object_name from ht01 where object_id=999; <BR> <BR>Elapsed: 00:00:00.02 <BR> <BR>Execution Plan <BR>---------------------------------------------------------- <BR>Plan hash value: 2671155529 <BR> <BR>----------------------------------------------------------------------------------------------------------- <BR>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | <BR>----------------------------------------------------------------------------------------------------------- <BR>| 0 | SELECT STATEMENT | | 1 | 96 | 2 (0)| 00:00:01 | <BR>| 1 | RESULT CACHE | 4gj5xks5wnjmk752h1fz18jprp | | | | | <BR>| 2 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 96 | 2 (0)| 00:00:01 | <BR>|* 3 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 | <BR>----------------------------------------------------------------------------------------------------------- <BR> <BR>Predicate Information (identified by operation id): <BR>--------------------------------------------------- <BR> <BR> 3 - access("OBJECT_ID"=999) <BR> <BR>Result Cache Information (identified by operation id): <BR>------------------------------------------------------ <BR> <BR> 1 - column-count=2; dependencies=(ROGER.HT01); attributes=(ordered); name="select owner,object_name from ht01 where object_id=999"<BR> <BR>Note <BR>----- <BR> - dynamic sampling used for this statement (level=2) <BR> <BR> <BR>Statistics <BR>---------------------------------------------------------- <BR> 7 recursive calls <BR> 0 db block gets <BR> 13 consistent gets <BR> 0 physical reads <BR> 0 redo size <BR> 492 bytes sent via SQL*Net to client <BR> 415 bytes received via SQL*Net from client <BR> 2 SQL*Net roundtrips to/from client <BR> 0 sorts (memory) <BR> 0 sorts (disk) <BR> 1 rows processed <BR> <BR>SQL> <BR>SQL> select /*+ NO_RESULT_CACHE */ owner,object_name<BR> 2 from ht01 where object_id=666;</P>
<P>Elapsed: 00:00:00.03</P>
<P>Execution Plan<BR>----------------------------------------------------------<BR>Plan hash value: 2671155529</P>
<P>-------------------------------------------------------------------------------------------<BR>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR>-------------------------------------------------------------------------------------------<BR>| 0 | SELECT STATEMENT | | 1 | 96 | 2 (0)| 00:00:01 |<BR>| 1 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 96 | 2 (0)| 00:00:01 |<BR>|* 2 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 |<BR>-------------------------------------------------------------------------------------------</P>
<P>Predicate Information (identified by operation id):<BR>---------------------------------------------------</P>
<P> 2 - access("OBJECT_ID"=666)</P>
<P>Note<BR>-----<BR> - dynamic sampling used for this statement (level=2)</P>
<P><BR>Statistics<BR>----------------------------------------------------------<BR> 7 recursive calls<BR> 0 db block gets<BR> 14 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 501 bytes sent via SQL*Net to client<BR> 415 bytes received via SQL*Net from client<BR> 2 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 1 rows processed</P>
<P>SQL> select owner,object_name from ht01 where object_id=666; </P>
<P>Elapsed: 00:00:00.02</P>
<P>Execution Plan<BR>----------------------------------------------------------<BR>Plan hash value: 2671155529</P>
<P>-----------------------------------------------------------------------------------------------------------<BR>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR>-----------------------------------------------------------------------------------------------------------<BR>| 0 | SELECT STATEMENT | | 1 | 96 | 2 (0)| 00:00:01 |<BR>| 1 | RESULT CACHE | 6u1h1qaku8rv6bp04nj91w3vvh | | | | |<BR>| 2 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 96 | 2 (0)| 00:00:01 |<BR>|* 3 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 |<BR>-----------------------------------------------------------------------------------------------------------</P>
<P>Predicate Information (identified by operation id):<BR>---------------------------------------------------</P>
<P> 3 - access("OBJECT_ID"=666)</P>
<P>Result Cache Information (identified by operation id):<BR>------------------------------------------------------</P>
<P> 1 - column-count=2; dependencies=(ROGER.HT01); attributes=(ordered); name="select owner,object_name from ht01 where object_id=666"</P>
<P>Note<BR>-----<BR> - dynamic sampling used for this statement (level=2)</P>
<P><BR>Statistics<BR>----------------------------------------------------------<BR> 0 recursive calls<BR> 0 db block gets<BR> 0 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 493 bytes sent via SQL*Net to client<BR> 415 bytes received via SQL*Net from client<BR> 2 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 1 rows processed<BR>SQL> <BR>SQL> show parameter cursor</P>
<P>NAME TYPE VALUE<BR>------------------------------------ ----------- ------------------------------<BR>_close_cached_open_cursors boolean FALSE<BR>_cursor_bind_capture_area_size integer 400<BR>_cursor_bind_capture_interval integer 900<BR>_cursor_cache_time integer 1800<BR>_cursor_db_buffers_pinned integer 44<BR>_cursor_features_enabled integer 2<BR>_cursor_plan_enabled boolean TRUE<BR>_cursor_plan_hash_version integer 1<BR>_cursor_plan_unparse_enabled boolean TRUE<BR>_cursor_stats_bucket integer 15<BR>_cursor_stats_heap integer 4<BR>_dump_cursor_heap_sizes boolean FALSE<BR>_fast_cursor_reexecute boolean FALSE<BR>_kks_free_cursor_stat_pct integer 10<BR>_optimizer_adaptive_cursor_sharing boolean TRUE<BR>_optimizer_extended_cursor_sharing string UDO<BR>_optimizer_extended_cursor_sharing_r string SIMPLE<BR>el<BR>_px_slaves_share_cursors integer 0<BR>_row_cache_cursors integer 20<BR>cursor_sharing string EXACT<BR>cursor_space_for_time boolean FALSE<BR>open_cursors integer 300<BR>session_cached_cursors integer 50<BR>SQL> </P>
<P>到这里，或许有人会有点迷惑了，最开始执行的是object_id=888,后面执行是object_id为666，<BR>为啥也能使用该特性且生效呢？因为query cache result特性不仅仅是根据文本来匹配，只要执行计划<BR>或部分执行计划一样，那么就会共享，也就是说就能避免软解析直接返回结果。</P>
<P>这样要简单的提及一下的是，我知道mysql 也有query cache的特性，开始我以为oracle跟mysql这<BR>功能完全一样，现在发现其实不一样，oracle 这里比mysql先进多了，为啥这么说呢？ 因为mysql的<BR>query cache 仅仅是对文本进行匹配，如果这里换成是mysql，那么object_id为666和888的2个sql语句，<BR>是无法进行共享的，除非使用绑定变量。</P>
<P>到最后，可能有朋友为问道，如果使用了该特性，那么想过的几个视图记录的信息岂不是会不断变大吗？<BR>确实是这样的，但是oracle提供了一个新的dbms包，可以对query cache result进行操作。</P>
<P>SQL> desc dbms_result_cache<BR>PROCEDURE BYPASS<BR> Argument Name Type In/Out Default?<BR> ------------------------------ ----------------------- ------ --------<BR> BYPASS_MODE BOOLEAN IN<BR> SESSION BOOLEAN IN DEFAULT<BR>FUNCTION DELETE_DEPENDENCY RETURNS NUMBER<BR> Argument Name Type In/Out Default?<BR> ------------------------------ ----------------------- ------ --------<BR> OWNER VARCHAR2 IN<BR> NAME VARCHAR2 IN<BR>PROCEDURE DELETE_DEPENDENCY<BR> Argument Name Type In/Out Default?<BR> ------------------------------ ----------------------- ------ --------<BR> OWNER VARCHAR2 IN<BR> NAME VARCHAR2 IN<BR>FUNCTION DELETE_DEPENDENCY RETURNS NUMBER<BR> Argument Name Type In/Out Default?<BR> ------------------------------ ----------------------- ------ --------<BR> OBJECT_ID BINARY_INTEGER IN<BR>PROCEDURE DELETE_DEPENDENCY<BR> Argument Name Type In/Out Default?<BR> ------------------------------ ----------------------- ------ --------<BR> OBJECT_ID BINARY_INTEGER IN<BR>FUNCTION FLUSH RETURNS BOOLEAN<BR> Argument Name Type In/Out Default?<BR> ------------------------------ ----------------------- ------ --------<BR> RETAINMEM BOOLEAN IN DEFAULT<BR> RETAINSTA BOOLEAN IN DEFAULT<BR> GLOBAL BOOLEAN IN DEFAULT<BR>PROCEDURE FLUSH<BR> Argument Name Type In/Out Default?<BR> ------------------------------ ----------------------- ------ --------<BR> RETAINMEM BOOLEAN IN DEFAULT<BR> RETAINSTA BOOLEAN IN DEFAULT<BR> GLOBAL BOOLEAN IN DEFAULT<BR>FUNCTION INVALIDATE RETURNS NUMBER<BR> Argument Name Type In/Out Default?<BR> ------------------------------ ----------------------- ------ --------<BR> OWNER VARCHAR2 IN<BR> NAME VARCHAR2 IN<BR>PROCEDURE INVALIDATE<BR> Argument Name Type In/Out Default?<BR> ------------------------------ ----------------------- ------ --------<BR> OWNER VARCHAR2 IN<BR> NAME VARCHAR2 IN<BR>FUNCTION INVALIDATE RETURNS NUMBER<BR> Argument Name Type In/Out Default?<BR> ------------------------------ ----------------------- ------ --------<BR> OBJECT_ID BINARY_INTEGER IN<BR>PROCEDURE INVALIDATE<BR> Argument Name Type In/Out Default?<BR> ------------------------------ ----------------------- ------ --------<BR> OBJECT_ID BINARY_INTEGER IN<BR>FUNCTION INVALIDATE_OBJECT RETURNS NUMBER<BR> Argument Name Type In/Out Default?<BR> ------------------------------ ----------------------- ------ --------<BR> ID BINARY_INTEGER IN<BR>PROCEDURE INVALIDATE_OBJECT<BR> Argument Name Type In/Out Default?<BR> ------------------------------ ----------------------- ------ --------<BR> ID BINARY_INTEGER IN<BR>FUNCTION INVALIDATE_OBJECT RETURNS NUMBER<BR> Argument Name Type In/Out Default?<BR> ------------------------------ ----------------------- ------ --------<BR> CACHE_ID VARCHAR2 IN<BR>PROCEDURE INVALIDATE_OBJECT<BR> Argument Name Type In/Out Default?<BR> ------------------------------ ----------------------- ------ --------<BR> CACHE_ID VARCHAR2 IN<BR>PROCEDURE MEMORY_REPORT<BR> Argument Name Type In/Out Default?<BR> ------------------------------ ----------------------- ------ --------<BR> DETAILED BOOLEAN IN DEFAULT<BR>FUNCTION STATUS RETURNS VARCHAR2</P>
<P>SQL> </P>
<P>SQL> select DBMS_RESULT_CACHE.status from dual;</P>
<P>STATUS<BR>------------------------------<BR>ENABLED</P>
<P>SQL> <BR>另外提及一下的是，如果你不想让某个sql不使用query cache result特性，而这时又无法用过<BR>修改sql语句来加hint 时，那么此时你可以用过如下方式来进行操作：<BR>exec DBMS_RESULT_CACHE.INVALIDATE_OBJECT('&CACHE_ID');如下例子</P>
<P>SQL> exec DBMS_RESULT_CACHE.INVALIDATE_OBJECT('&CACHE_ID');<BR>Enter value for cache_id: 6u1h1qaku8rv6bp04nj91w3vvh</P>
<P>PL/SQL procedure successfully completed.</P>
<P>Elapsed: 00:00:00.13<BR>SQL> select name,status,cache_id from v$result_cache_objects;<BR>NAME STATUS CACHE_ID<BR>------------------------------------------------------ --------- -----------------------------------<BR>ROGER.HT01 Published ROGER.HT01<BR>select owner,object_name from ht01 where object_id=666 Published 6u1h1qaku8rv6bp04nj91w3vvh<BR>select owner,object_name from ht01 where object_id=999 Published 4gj5xks5wnjmk752h1fz18jprp<BR>select /*+ RESULT_CACHE */ owner,object_name Published 0mn43k8b004mrgacy3snrb9ff7<BR>from ht01 where object_id=888</P>
<P>select owner,object_name from ht01 where object_id=666 Invalid 6u1h1qaku8rv6bp04nj91w3vvh</P>
<P>SQL> <BR>SQL> select owner,object_name from ht01 where object_id=666;</P>
<P>Elapsed: 00:00:00.02</P>
<P>Execution Plan<BR>----------------------------------------------------------<BR>Plan hash value: 2671155529</P>
<P>-----------------------------------------------------------------------------------------------------------<BR>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR>-----------------------------------------------------------------------------------------------------------<BR>| 0 | SELECT STATEMENT | | 1 | 96 | 2 (0)| 00:00:01 |<BR>| 1 | RESULT CACHE | 6u1h1qaku8rv6bp04nj91w3vvh | | | | |<BR>| 2 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 96 | 2 (0)| 00:00:01 |<BR>|* 3 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 |<BR>-----------------------------------------------------------------------------------------------------------</P>
<P>Predicate Information (identified by operation id):<BR>---------------------------------------------------</P>
<P> 3 - access("OBJECT_ID"=666)</P>
<P>Result Cache Information (identified by operation id):<BR>------------------------------------------------------</P>
<P> 1 - column-count=2; dependencies=(ROGER.HT01); attributes=(ordered); name="select owner,object_name from ht01 where object_id=666"</P>
<P>Note<BR>-----<BR> - dynamic sampling used for this statement (level=2)</P>
<P><BR>Statistics<BR>----------------------------------------------------------<BR> 0 recursive calls<BR> 0 db block gets<BR> 4 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 493 bytes sent via SQL*Net to client<BR> 415 bytes received via SQL*Net from client<BR> 2 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 1 rows processed</P>
<P>SQL> alter session set result_cache_mode = auto;</P>
<P>Session altered.</P>
<P>Elapsed: 00:00:00.02<BR>SQL> select owner,object_name from ht01 where object_id=666;</P>
<P>Elapsed: 00:00:00.03</P>
<P>Execution Plan<BR>----------------------------------------------------------<BR>Plan hash value: 2671155529</P>
<P>-------------------------------------------------------------------------------------------<BR>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR>-------------------------------------------------------------------------------------------<BR>| 0 | SELECT STATEMENT | | 1 | 96 | 2 (0)| 00:00:01 |<BR>| 1 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 96 | 2 (0)| 00:00:01 |<BR>|* 2 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 |<BR>-------------------------------------------------------------------------------------------</P>
<P>Predicate Information (identified by operation id):<BR>---------------------------------------------------</P>
<P> 2 - access("OBJECT_ID"=666)</P>
<P>Note<BR>-----<BR> - dynamic sampling used for this statement (level=2)</P>
<P><BR>Statistics<BR>----------------------------------------------------------<BR> 9 recursive calls<BR> 0 db block gets<BR> 14 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 501 bytes sent via SQL*Net to client<BR> 415 bytes received via SQL*Net from client<BR> 2 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 1 rows processed</P>
<P>SQL> </P>
<P>这里需要注意的是，如果result_cache_mode是设置为force的话，那么经过该过程的操作其实<BR>是不起任何作用的，当该参数设置为auto了就ok了。</P>
<P>通过dbms_result_cache.memory_report来查看cache memory的使用情况：</P>
<P>SQL> exec dbms_result_cache.memory_report<BR>R e s u l t C a c h e M e m o r y R e p o r t<BR><BR>Block Size = 1K bytes<BR>Maximum Cache Size = 960K bytes (960 blocks)<BR>Maximum Result Size = 48K bytes (48 blocks)<BR><BR>Total Memory = 107836 bytes <BR>... Fixed Memory = 9440 bytes <BR>... Dynamic Memory = 98396 bytes <BR>....... Overhead = 65628 bytes<BR>....... Cache Memory = 32K bytes (32 blocks)<BR>........... Unused Memory = 28 blocks<BR>........... Used Memory = 4 blocks<BR>............... Dependencies = 1 blocks (1 count)<BR>............... Results = 3 blocks<BR>................... SQL = 3 blocks (3 count)</P>
<P>PL/SQL procedure successfully completed.<BR> <BR> <BR>通过dbms_result_cache.fulsh来清除已经cache的信息：<BR>SQL> BEGIN<BR>DBMS_RESULT_CACHE.BYPASS(TRUE);<BR>DBMS_RESULT_CACHE.FLUSH;<BR> 2 END;<BR> 3 / 4 5 </P>
<P>PL/SQL procedure successfully completed.</P>
<P>Elapsed: 00:00:00.02</P>
<P>SQL> set serveroutput on<BR>SQL> exec dbms_result_cache.memory_report;<BR>R e s u l t C a c h e M e m o r y R e p o r t<BR><BR>Block Size = 1K bytes<BR>Maximum Cache Size = 960K bytes (960 blocks)<BR>Maximum Result Size = 48K bytes (48 blocks)<BR><BR>Total Memory = 9440 bytes <BR>... Fixed Memory = 9440 bytes <BR>... Dynamic Memory = 0 bytes </P>
<P>PL/SQL procedure successfully completed.</P>
<P>Elapsed: 00:00:00.13<BR>SQL> </P>
<P><BR> <BR>另外跟该特性相关的几个新引入的视图也跟大家简单的介绍一下，如下：<BR>V$RESULT_CACHE_DEPENDENCY</P>
<P>该视图记录了result cache的一些对象，如下：<BR>SQL> select * from V$RESULT_CACHE_DEPENDENCY;</P>
<P> RESULT_ID DEPEND_ID OBJECT_NO<BR>---------- ---------- ----------<BR> 1 0 73434<BR> <BR>result_id其实就是执行计划中的id。 后面的object_no即是对象的object_id。<BR>SQL> select owner,object_id,object_name from dba_objects where object_name='HT01';</P>
<P>OWNER OBJECT_ID OBJECT_NAME<BR>---------- ---------- -------------------------<BR>ROGER 73434 HT01</P>
<P> </P>
<P>V$RESULT_CACHE_MEMORY</P>
<P>该视图主要是用来查询query cache的使用情况，如下：<BR>SQL> select * from V$RESULT_CACHE_MEMORY;</P>
<P> ID CHUNK OFFSET FRE OBJECT_ID POSITION<BR>---------- ---------- ---------- --- ---------- ----------<BR> 0 0 0 NO 0 0<BR> 1 0 1 NO 1 0<BR> 2 0 2 YES<BR> 3 0 3 YES<BR> 4 0 4 YES<BR> 5 0 5 YES<BR> 6 0 6 YES<BR> 7 0 7 YES<BR> 8 0 8 YES<BR> 9 0 9 YES<BR> 10 0 10 YES<BR> 11 0 11 YES<BR> 12 0 12 YES<BR> 13 0 13 YES<BR> 14 0 14 YES<BR> 15 0 15 YES<BR> 16 0 16 YES<BR> 17 0 17 YES<BR> 18 0 18 YES<BR> 19 0 19 YES<BR> 20 0 20 YES<BR> 21 0 21 YES<BR> 22 0 22 YES<BR> 23 0 23 YES<BR> 24 0 24 YES<BR> 25 0 25 YES<BR> 26 0 26 YES<BR> 27 0 27 YES<BR> 28 0 28 YES<BR> 29 0 29 YES<BR> 30 0 30 YES<BR> 31 0 31 YES</P>
<P>32 rows selected.</P>
<P><BR>V$RESULT_CACHE_OBJECTS</P>
<P>该视图主要是记录了关于cache 对象的一些信息，大家可以参考官方文档的说明，<BR>该视图在11gR1和11gR2 中无任何差异，如下查询例子：</P>
<P>SQL> select id,type,name,OBJECT_NO,CACHE_ID,CACHE_KEY,HASH<BR> 2 from V$RESULT_CACHE_OBJECTS</P>
<P> ID TYPE NAME OBJECT_NO CACHE_ID CACHE_KEY HASH<BR>---- ---------- -------------------- ---------- ---------------------------- --------------------------- ----------<BR> 0 Dependency ROGER.HT01 73434 ROGER.HT01 ROGER.HT01 1419051366<BR> 1 Result select /*+ RESULT_CA 0 0mn43k8b004mrgacy3snrb9ff7 gq7925h12u7315u1m3t300pb6a 3399706625<BR> CHE */ owner,object_<BR> name<BR> from ht01 where obje<BR> ct_id=888</P>
<P><BR>V$RESULT_CACHE_STATISTICS<BR> <BR>该视图主要是记录result cache对象的一些统计信息，是记录的累计值。 <BR>SQL> select * from V$RESULT_CACHE_STATISTICS; <BR> <BR> ID NAME VALUE <BR>---------- ------------------------------ -------<BR> 1 Block Size (Bytes) 1024 <BR> 2 Block Count Maximum 960 <BR> 3 Block Count Current 32 <BR> 4 Result Size Maximum (Blocks) 48 <BR> 5 Create Count Success 1 <BR> 6 Create Count Failure 0 <BR> 7 Find Count 1 <BR> 8 Invalidation Count 0 <BR> 9 Delete Count Invalid 0 <BR> 10 Delete Count Valid 0 <BR> 11 Hash Chain Length 1 <BR> <BR>11 rows selected. <BR> <BR>SQL> </P>
<P><BR>到最后，我们再来看看query cache result特性有哪些局限，通俗的将就是在哪些情况下，<BR>该特性将无法使用或将不会生效。 <BR>Result cache is disabled for queries containing:<BR> Temporary or Dictionary tables<BR> Nondeterministic PL/SQL functions<BR> Sequence CURRVAL and NEXTVALSQL functions CURRENT_DATE,SYSDATE,SYS_GUID, and so on</P>
<P>DDL/DML on remote database does not expire cached results</P>
<P>Flashback queries can be cached</P>
<P>Result Cache does not automatically release memory</P>
<P> It grows until maximum size is reached<BR> DBMS_RESULT_CACHE.FLUSH purges memory</P>
<P>Bind variables</P>
<P> Cached result is parameterized with variable values<BR> Cached results can only be found for the same variable values</P>
<P>Cached result will not be build if:</P>
<P> Query is build on a noncurrent version of data(read consistency enforcement)<BR> Current session has outstanding transaction on tables in query</P>
<P><BR>Result cache is flushed when we flush the shared pool</P>
<P><BR>关于该特性，大家可以参考如下metalink文档或查阅官方文档：<BR>Complete Reference To 11g New Feature : SQL Query Result Cache </P>
<P>当你读完这篇文章以后，你或许会跟我一样，大脑中会有了一个很大的疑问：</P>
<P>query cache result特性所占据的这部分内存是如何管理的，虽然说该部分内存是从sga中分配，<BR>那么该部分内存到底存在哪儿呢？cache buffer？还是shared pool中吗？如果是，<BR>那么当执行如下是命令后还有用吗？<BR>alter system flush cache_buffer;<BR>alter system flush shared_pool;</P>
<P>其实从上面红色部分我们可以看出，该部分内存存在shared pool中。至于是如何进行管理的，<BR>其结构如何？将是下一篇文章所要阐述的。</P>
<P><BR>备注：<BR> 1. 由于plsql query cache result其实基本上差不多，只是11.1 和11.2有小小的差异，<BR>所以我这里没有演示plsql query cache result。另外就是既然是新特性，那么就避免不了<BR>有不少的bug，所以在使用该特性时需要做一定的权衡(经查metalink，相关的几个bug影响其实<BR>不大）。<BR> 2. query cache result除了server端之外还有client query cache result，也有一些<BR>相关的参数配置，如下：<BR>client_result_cache_size<BR>client_result_cache_lag</P>
<P>如下参数需要加到客户端sqlnet.ora中。<BR>OCI_RESULT_CACHE_MAX_SIZE<BR>OCI_RESULT_CACHE_MAX_RSET_SIZE<BR>OCI_RESULT_CACHE_MAX_RSET_ROWS<BR> <BR>更为详细的说明或用法以及注意事项，大家请参考官方文档，这里不做说明。 </P>
页:
[1]