|
本文原链接，个人站点：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
更为详细的说明或用法以及注意事项，大家请参考官方文档，这里不做说明。 | 
免费注册
发表于 2011-12-22 08:54