【11gR2新特性】result cache 的三种模式

yang@rac1>show parameter result
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
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 2080K
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0
结果缓存的三种存储方式: MANUAL,AUTO,FORCE ,这篇文章将分别介绍三种模式的使用方法。
当result_cache_mode=MANUAL的时候，必须使用hint提示才能使用结果缓存特性。首先清理缓存，并查看缓存情况。
yang@rac1>exec dbms_result_cache.flush();
PL/SQL 过程已成功完成。
yang@rac1>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  = 2080K bytes (2080 blocks)
Maximum Result Size = 104K bytes (104 blocks)
[Memory]
Total Memory = 9460 bytes [0.004% of the Shared Pool]
... Fixed Memory = 9460 bytes [0.004% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL 过程已成功完成。
进行不带hint的查询例子。
yang@rac1>set autotrace on
yang@rac1>select object_type,count(*) from yangobj group by object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
TABLE                    65527
执行计划
----------------------------------------------------------
Plan hash value: 1913842841
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     6 |   209   (2)| 00:00:03 |
|   1 |  HASH GROUP BY     |         |     1 |     6 |   209   (2)| 00:00:03 |
|   2 |   TABLE ACCESS FULL| YANGOBJ | 65536 |   384K|   206   (1)| 00:00:03 |
------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        753  consistent gets
          0  physical reads
          0  redo size
        496  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
从缓存报告中可以看出上面的sql结果没有被缓存。
yang@rac1>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  = 2080K bytes (2080 blocks)
Maximum Result Size = 104K bytes (104 blocks)
[Memory]
Total Memory = 9460 bytes [0.004% of the Shared Pool]
... Fixed Memory = 9460 bytes [0.004% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL 过程已成功完成。
yang@rac1>set autotrace off
yang@rac1>select * from v$result_cache_statistics;
        ID NAME                                VALUE
---------- ----------------------------------- ----------
         1 Block Size (Bytes)                  1024
         2 Block Count Maximum                 2080
         3 Block Count Current                 0
         4 Result Size Maximum (Blocks)        104
         5 Create Count Success                0
         6 Create Count Failure                0
         7 Find Count                          0
         8 Invalidation Count                  0
         9 Delete Count Invalid                0
        10 Delete Count Valid                  0
        11 Hash Chain Length                   0
已选择11行。
使用hint提示执行sql语句。
yang@rac1>select /*+ result_cache */ object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE           COUNT(*)
------------------- ----------
TABLE                    65527
执行计划
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   1 |  RESULT CACHE       | 93qg9pxgyrhd35bxgp9ay1mvqw |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   3 |    TABLE ACCESS FULL| YANGOBJ                    | 65536 |   384K|   206   (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
 1 - column-count=2; dependencies=(YANG.YANGOBJ); parameters=(nls);
   name="select /*+ result_cache */object_type,count(*) from yangobj group by object_type"
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        753  consistent gets
        748  physical reads
          0  redo size
        496  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
yang@rac1>set autotrace off
yang@rac1>select * from v$result_cache_statistics;

        ID NAME                                VALUE
---------- ----------------------------------- ----------
         1 Block Size (Bytes)                  1024
         2 Block Count Maximum                 2080
         3 Block Count Current                 32
         4 Result Size Maximum (Blocks)        104
         5 Create Count Success                1 --结果被缓存。
         6 Create Count Failure                0
         7 Find Count                          0
         8 Invalidation Count                  0
         9 Delete Count Invalid                0
        10 Delete Count Valid                  0
        11 Hash Chain Length                   1
已选择11行。

yang@rac1>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  = 2080K bytes (2080 blocks)
Maximum Result Size = 104K bytes (104 blocks)
[Memory]
Total Memory = 102372 bytes [0.044% of the Shared Pool]
... Fixed Memory = 9460 bytes [0.004% of the Shared Pool]
... Dynamic Memory = 92912 bytes [0.040% of the Shared Pool]
....... verhead = 60144 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL     = 1 blocks (1 count)

PL/SQL 过程已成功完成。
yang@rac1>set autotrace on
yang@rac1>select /*+ result_cache */ object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE           COUNT(*)
------------------- ----------
TABLE                    65527
执行计划
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   1 |  RESULT CACHE       | 93qg9pxgyrhd35bxgp9ay1mvqw |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   3 |    TABLE ACCESS FULL| YANGOBJ                    | 65536 |   384K|   206   (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=2; dependencies=(YANG.YANGOBJ); parameters=(nls);
   name="select /*+ result_cache */object_type,count(*) from yangobj group by object_type"
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets    --利用了结果缓存。
          0  physical reads
          0  redo size
        496  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

yang@rac1>
yang@rac1>select name,cache_id,cache_key from v$result_cache_objects;
CACHE_ID                   CACHE_KEY                    NAME
----------                 --------------------------  -----------------------------
YANG.YANGOBJ               YANG.YANGOBJ                YANG.YANGOBJ
93qg9pxgyrhd35bxgp9ay1mvqw fpn1dsgmvbq9cbhu4vs188mqr3  select /*+ result_cache */ object_type,count(*)
                                                       from yangobj group byobject_type
--------------------------autot-------------------------------------------------
当result_cache_mode=MANUAL的时候，情况比较复杂一点，oracle 根据自己非公开的算法来实现结果缓存的。从下面的实验来看，不管在shared POOL 里有没有结果集，oracle都不会使用那个结果集，使用hint提示时，才会使用结果集。
SQL>select object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE           COUNT(*)
------------------- ----------
TABLE                    65536
已用时间:  00: 00: 00.02
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  = 2080K bytes (2080 blocks)
Maximum Result Size = 104K bytes (104 blocks)
[Memory]
Total Memory = 107812 bytes [0.046% of the Shared Pool]
... Fixed Memory = 9460 bytes [0.004% of the Shared Pool]
... Dynamic Memory = 98352 bytes [0.042% of the Shared Pool]
....... verhead = 65584 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... AUTO    = 1 blocks (1 count)

PL/SQL 过程已成功完成。
已用时间:  00: 00: 00.03
SQL>select * from v$result_cache_statistics;
        ID NAME                           VALUE
---------- ------------------------------ --------------------
         1 Block Size (Bytes)             1024
         2 Block Count Maximum            2080
         3 Block Count Current            32
         4 Result Size Maximum (Blocks)   104
         5 Create Count Success           1
         6 Create Count Failure           0
         7 Find Count                     0
         8 Invalidation Count             0
         9 Delete Count Invalid           0
        10 Delete Count Valid             0
        11 Hash Chain Length              1

已选择11行。
已用时间:  00: 00: 00.03
SQL>set autotrace on
SQL>select object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE           COUNT(*)
------------------- ----------
TABLE                    65536

已用时间:  00: 00: 00.02
执行计划
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   1 |  RESULT CACHE       | 93qg9pxgyrhd35bxgp9ay1mvqw |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   3 |    TABLE ACCESS FULL| YANGOBJ                    | 65536 |   384K|   206   (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=2; type=AUTO; dependencies=(YANG.YANGOBJ); parameters=(nls); name="select object_type,count(*) from yangobj group by object_type"
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        753  consistent gets
          0  physical reads
          0  redo size
        496  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>set autotrace off
SQL>select * from v$result_cache_statistics;
        ID NAME                           VALUE
---------- ------------------------------ ---------------------------------------------------------------------------------
         1 Block Size (Bytes)             1024
         2 Block Count Maximum            2080
         3 Block Count Current            32
         4 Result Size Maximum (Blocks)   104
         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行。
已用时间:  00: 00: 00.03
SQL>set autotrace on
SQL>select object_type,count(*) from yangobj group by object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
TABLE                    65536
已用时间:  00: 00: 00.02
执行计划
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   1 |  RESULT CACHE       | 93qg9pxgyrhd35bxgp9ay1mvqw |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   3 |    TABLE ACCESS FULL| YANGOBJ                    | 65536 |   384K|   206   (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=2; type=AUTO; dependencies=(YANG.YANGOBJ); parameters=(nls); name="select object_type,count(*) from yangobj group by object_type"
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        753  consistent gets
          0  physical reads
          0  redo size
        496  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 */ object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE           COUNT(*)
------------------- ----------
TABLE                    65536
已用时间:  00: 00: 00.02
执行计划
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   1 |  RESULT CACHE       | 93qg9pxgyrhd35bxgp9ay1mvqw |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   3 |    TABLE ACCESS FULL| YANGOBJ                    | 65536 |   384K|   206   (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=2; dependencies=(YANG.YANGOBJ); parameters=(nls); name="select /*+ result_cache */ object_type,count(*) from yangobj group by obje
ct_type"
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        753  consistent gets
          0  physical reads
          0  redo size
        496  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 */ object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE           COUNT(*)
------------------- ----------
TABLE                    65536
已用时间:  00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   1 |  RESULT CACHE       | 93qg9pxgyrhd35bxgp9ay1mvqw |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   3 |    TABLE ACCESS FULL| YANGOBJ                    | 65536 |   384K|   206   (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=2; dependencies=(YANG.YANGOBJ); parameters=(nls);
   name="select /*+ result_cache */ object_type,count(*) from yangobj group by obje
ct_type"

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        496  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
--------------------------force-------------------------------------------------
当result_cache_mode=MANUAL的时候，oracle 会强制缓存sql语句的查询结果。实验如下：
yang@rac1>alter session set result_cache_mode = force;
会话已更改。
已用时间:  00: 00: 00.00
yang@rac1>exec dbms_result_cache.flush();
PL/SQL 过程已成功完成。
已用时间:  00: 00: 00.00
第一次查询时，即将结果缓存为cache_id为81zjdmh3h4yza1stdry7m73pvg的结果集。
yang@rac1>select count(*) from yangobj where object_id=74594;
  COUNT(*)                                                                                                                                           
----------                                                                                                                                           
     65536  
已用时间:  00: 00: 00.01
执行计划
----------------------------------------------------------                                                                                           
Plan hash value: 362321706                                                                                                                           
--------------------------------------------------------------------------------------------------                                                   
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |                         
--------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT    |                            |     1 |     5 |   206   (1)| 00:00:03 |
|   1 |  RESULT CACHE       | 81zjdmh3h4yza1stdry7m73pvg |       |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |     5 |            |          |
|*  3 |    TABLE ACCESS FULL| YANGOBJ                    | 65536 |   320K|   206   (1)| 00:00:03 |
-------------------------------------------------------------------------------------------------- 
Predicate Information (identified by operation id):
---------------------------------------------------  
   3 - filter("OBJECT_ID"=74594)                                                                                                                    
Result Cache Information (identified by operation id):                                                                                               
------------------------------------------------------                                                                                               
   1 - column-count=1; dependencies=(YANG.YANGOBJ); attributes=(single-row); name="select count(*) from yangobj where object_id=74594" 
统计信息
----------------------------------------------------------    
          1  recursive calls       
          0  db block gets 
        753  consistent gets
          0  physical reads 
          0  redo size   
        425  bytes sent via SQL*Net to client                               
        416  bytes received via SQL*Net from client                                
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
再次查询时，一致性读为0.
yang@rac1>select count(*) from yangobj where object_id=74594;
  COUNT(*)                                                                                                                                           
----------                                                                                                                                           
     65536  
已用时间:  00: 00: 00.00
执行计划
----------------------------------------------------------                                                                                           
Plan hash value: 362321706                                                                                                                            
--------------------------------------------------------------------------------------------------   
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |     5 |   206   (1)| 00:00:03 | 
|   1 |  RESULT CACHE       | 81zjdmh3h4yza1stdry7m73pvg |       |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |     5 |            |          |
|*  3 |    TABLE ACCESS FULL| YANGOBJ                    | 65536 |   320K|   206   (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):                                                                                                  
---------------------------------------------------                                                                                                  
   3 - filter("OBJECT_ID"=74594)                                                                                                                   
Result Cache Information (identified by operation id):                                                                                               
------------------------------------------------------                                                                                               
   1 - column-count=1; dependencies=(YANG.YANGOBJ); attributes=(single-row);
   name="select count(*) from yangobj where object_id=74594" 
统计信息
----------------------------------------------------------                                                                                           
          0  recursive calls   
          0  db block gets                             
          0  consistent gets                                  
          0  physical reads
          0  redo size          
        425  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)                                                                                                                          
          0  sorts (disk) 
          1  rows processed
除非使用no_result_cache hint提示 在force 模式才会不使用结果缓存的特性。
yang@rac1>alter system set result_cache_mode=force;

系统已更改。

yang@rac1>show parameter result

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
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 2080K
result_cache_mode                    string      FORCE
result_cache_remote_expiration       integer     0
yang@rac1>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  = 2080K bytes (2080 blocks)
Maximum Result Size = 104K bytes (104 blocks)
[Memory]
Total Memory = 102372 bytes [0.044% of the Shared Pool]
... Fixed Memory = 9460 bytes [0.004% of the Shared Pool]
... Dynamic Memory = 92912 bytes [0.040% of the Shared Pool]
....... verhead = 60144 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL     = 1 blocks (1 count)

PL/SQL 过程已成功完成。
yang@rac1>set autotrace on
yang@rac1>select /*+ no_result_cache */ object_type,count(*) from yangobj group by
OBJECT_TYPE           COUNT(*)
------------------- ----------
TABLE                    65527
执行计划
----------------------------------------------------------
Plan hash value: 1913842841
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     6 |   209   (2)| 00:00:03 |
|   1 |  HASH GROUP BY     |         |     1 |     6 |   209   (2)| 00:00:03 |
|   2 |   TABLE ACCESS FULL| YANGOBJ | 65536 |   384K|   206   (1)| 00:00:03 |
------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        753  consistent gets
          0  physical reads
          0  redo size
        496  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed