Result_cache 2

Server result cache是在shared pool中为查询结果开辟一块专用的缓存，通过新引入的一个初始化参数来控制该cache的大小：result_cache_max_size。如果result_cache_max_size=0则表示禁用该特性。参数result_cache_max_result则控制单个缓存结果可以占总的Server result cache大小的百分比。另外还有一个初始化参数用于控制Server result cache的模式：result_cache_mode，如果取值为auto，则优化器会自动判断是否将查询结果缓存；如果取值为manual（这是默认值），则需要通过查询提示result_cache来告诉优化器是否缓存结果；如果取值为force，则系统会尽可能的缓存查询结果，当然，即使参数取值为force，也可以通过提示no_result_cache来拒绝缓存结果。在itpub关于该特性的讨论中，有人提到，文档中result_cache_mode只有manual/force两个取值。这个可能是文档的bug，通过给参数赋一个非法值得到的报错信息中明白的说明有三个取值：

SQL> alter session set result_cache_mode=a;
ERROR:
ORA-00096: invalid value A for parameter result_cache_mode, must be from among FORCE, MANUAL, AUTO 

 

Oracle11g还引入了一个package专门用于管理server result cache：dbms_result_cache

 

清空server result cache

SQL> exec dbms_result_cache.flush
 
PL/SQL procedure successfully completed. 

 

查看server result cache的内存使用报告

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  = 1184K bytes (1184 blocks)
Maximum Result Size = 59K bytes (59 blocks)
[Memory]
Total Memory = 5132 bytes [0.006% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.006% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
 
PL/SQL procedure successfully completed. 

 

通过动态性能视图v$result_cache_statistics可以知道server result cache的使用统计信息。

SQL> select * from v$result_cache_statistics;
 
        ID NAME                                VALUE
---------- ------------------------------ ----------
         1 Block Size (Bytes)                        1024
         2 Block Count Maximum                  1184
         3 Block Count Current                     0
         4 Result Size Maximum (Blocks)       59
         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
 
10 rows selected. 

 

其他相关动态性能视图：
v$result_cache_objects
v$result_cache_memory
v$result_cache_dependency

下面我们看一个实际的例子

SQL> set autotrace on
SQL> select /*+ result_cache */ count(*) from test;
 
  COUNT(*)
----------
     11865
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=48 Card=1)
   1    0   RESULT CACHE OF '7rkq2h8sb7kdg5sjxw69z8c45g'
   2    1     SORT (AGGREGATE)
   3    2       TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=48 Card=12024)
 
 
Statistics
----------------------------------------------------------
        337  recursive calls
          0  db block gets
        291  consistent gets
          0  physical reads
          0  redo size
        352  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed 

 

注意到执行计划中的RESULT CACHE OF ‘7rkq2h8sb7kdg5sjxw69z8c45g’，说明已经缓存前面两步的结果。

再来看result cache的内存使用情况

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  = 1184K bytes (1184 blocks)
Maximum Result Size = 59K bytes (59 blocks)
[Memory]
Total Memory = 103528 bytes [0.112% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.006% of the Shared Pool]
... Dynamic Memory = 98396 bytes [0.107% of the Shared Pool]
....... Overhead = 65628 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 procedure successfully completed. 

 

查看v$result_cache_statistics

SQL> select * from v$result_cache_statistics;
 
        ID NAME                                VALUE
---------- ------------------------------ ----------
         1 Block Size (Bytes)                        1024
         2 Block Count Maximum                  1184
         3 Block Count Current                     32
         4 Result Size Maximum (Blocks)       59
         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 

 

注意到Create Count Success=1说明成功的缓存了一笔记录

再次执行该查询

SQL> select /*+ result_cache */ count(*) from test;
 
  COUNT(*)
----------
     11865
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=48 Card=1)
   1    0   RESULT CACHE OF '7rkq2h8sb7kdg5sjxw69z8c45g'
   2    1     SORT (AGGREGATE)
   3    2       TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=48 Card=12024)
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        352  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed 

 

注意到这里consistent gets=0，也就是该查询甚至不产生逻辑读了，也就是直接从result cache中获得了查询结果。

查看v$result_cache_statistics

SQL> select * from v$result_cache_statistics;
 
        ID NAME                                VALUE
---------- ------------------------------ ----------
         1 Block Size (Bytes)                        1024
         2 Block Count Maximum                  1184
         3 Block Count Current                     32
         4 Result Size Maximum (Blocks)       59
         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 

 

注意到Find Count = 1，说明上次缓存的结果被后续的查询应用了一次。如果继续执行该查询，可以发现该统计信息相应增加。

当然，这个例子中，缓存了整个查询的结果，实际上也可以缓存执行计划中某一步或者某几步的查询结果。例如，下面的例子中，就缓存了子查询的结果

SQL> select count(*) from (select /*+result_cache*/* from test where object_id<100);
 
  COUNT(*)
----------
        98
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=48 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     VIEW (Cost=48 Card=237)
   3    2       RESULT CACHE OF 'fbbc5y53mwuj75buth9d2vwkkn'
   4    3         TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=48 Card=237 Bytes=37446)
 
Statistics
----------------------------------------------------------
        716  recursive calls
          4  db block gets
        403  consistent gets
          0  physical reads
          0  redo size
        350  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         23  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL> select count(*) from (select /*+result_cache*/* from test where object_id<100);
 
  COUNT(*)
----------
        98
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=48 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     VIEW (Cost=48 Card=237)
   3    2       RESULT CACHE OF 'fbbc5y53mwuj75buth9d2vwkkn'
   4    3         TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=48 Card=237 Bytes=37446)
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          350  bytes sent via SQL*Net to client
          500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed