sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle数据库11g新特性之Query Cache Result研究案例

时间:2016-11-15 09:29   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库11G版本中新特性研究笔记,详细介绍了11g新特性之Query Cache Result深入研究案例。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 11g新特性之–Query Cache Result 研究

该特性是11gR1引入的,关于query result cache特性,主要有2种:

1. PL/SQL Function Result Cache –针对plsql而言
2. Query Result Cache –顾名思义针对重复执行的sql


我们都知道oracle通常是通过参数来进行控制某个功能的,当然这个也不例外,首先我们来介绍跟该特性有关的几个参数(包括隐含参数):

SQL> SELECT * FROM v$version WHERE rownum <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 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  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_namefrom 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 RESULhttp://www.oracleplus.netT 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


更为详细的说明或用法以及注意事项,大家请参考官方文档,这里不做说明。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle数据库11g新特性之Query Cache Result研究案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1272.html

Oracle研究中心

关键词:

Oracle数据库11g新特性

深入研究Query Cache Result使用方法