11g新特性之–Query Cache Result 研究

时间:2021-10-06 07:37:24

该特性是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

发现第一使用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

第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> 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> 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

到这里,或许有人会有点迷惑了,最开始执行的是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> SELECT DBMS_RESULT_CACHE.STATUS FROM dual;
 
STATUS
------------------------------
ENABLED

另外提及一下的是,如果你不想让某个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> 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

这里需要注意的是,如果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
2 DBMS_RESULT_CACHE.BYPASS(TRUE);
3 DBMS_RESULT_CACHE.FLUSH;
4 END;
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

另外跟该特性相关的几个新引入的视图也跟大家简单的介绍一下,如下:

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.

到最后,我们再来看看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