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