Oracle 11g新特性:Result Cache

在Oracle Database 11g中，Oracle引入了一个令人关注的新特性：结果集缓存（Result Cache）。
顾名思义，这个新特性的含义就是将查询的结果集Cache起来，以便随后相同的查询请求可以直接利用，从而避免了再次查询。今天开始来学习一下这个新特性。

进一步的Result Cache又可以分为：Server Result Cache 和 Client Result Cache。
前者通过服务器端SGA来缓存结果集，后者通过客户端来缓存结果集。

缓存是提高性能的一个常用手段，可以说在Oracle数据库中，Cache无处不在。
对于Client Result Cache:
在使用OCI应用程序时，可以通过客户端内存来缓存查询的结果集，缓存结果可以在所有session间共享，当查询反复执行时，查询结果可以直接从客户段的缓存中获得，从而极大地提高应用效率。
客户端结果集缓存并不使用服务器端的内存，不会对服务器的内存使用造成影响，这一点和Server Result Cache不同。
同Client Result Cache相关的视图主要有：

SQL> select * from dict where table_name like '%CLIENT_RESULT_CACHE%';

TABLE_NAME                    COMMENTS
------------------------------ ---------------------------------------------
CLIENT_RESULT_CACHE_STATS$    Synonym for CRCSTATS_$
GV$CLIENT_RESULT_CACHE_STATS  Synonym for GV_$CLIENT_RESULT_CACHE_STATS
V$CLIENT_RESULT_CACHE_STATS    Synonym for V_$CLIENT_RESULT_CACHE_STATS

同Client Result Cache相关的参数有：

SQL> show parameter client_result

NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag              big integer 3000
client_result_cache_size            big integer 0

如果我们不想启用Client Result Cache的特性，可以设置参数client_result_cache_size为0即可。

对于Server Result Cache:
服务器端结果集缓存使用Shared Pool中的内存来进行结果缓存，这部分内存使用可以通过v$sgastat视图来查询观察：

SQL> select * from v$sgastat
  2  where lower(name) like '%result%';

POOL        NAME                            BYTES
------------ -------------------------- ----------
shared pool  Result Cache: State Objs        2852
shared pool  Result Cache: Memory Mgr          124
shared pool  Result Cache: Bloom Fltr        2048
shared pool  Result Cache: Cache Mgr          108

进一步的和Result Cache相关的视图有：

SQL> select * from dict where table_name like '%RESULT_CACHE%';

TABLE_NAME                    COMMENTS
------------------------------ ---------------------------------------------
CLIENT_RESULT_CACHE_STATS$    Synonym for CRCSTATS_$
GV$CLIENT_RESULT_CACHE_STATS  Synonym for GV_$CLIENT_RESULT_CACHE_STATS
GV$RESULT_CACHE_DEPENDENCY    Synonym for GV_$RESULT_CACHE_DEPENDENCY
GV$RESULT_CACHE_MEMORY        Synonym for GV_$RESULT_CACHE_MEMORY
GV$RESULT_CACHE_OBJECTS        Synonym for GV_$RESULT_CACHE_OBJECTS
GV$RESULT_CACHE_STATISTICS    Synonym for GV_$RESULT_CACHE_STATISTICS
V$CLIENT_RESULT_CACHE_STATS    Synonym for V_$CLIENT_RESULT_CACHE_STATS
V$RESULT_CACHE_DEPENDENCY      Synonym for V_$RESULT_CACHE_DEPENDENCY
V$RESULT_CACHE_MEMORY          Synonym for V_$RESULT_CACHE_MEMORY
V$RESULT_CACHE_OBJECTS        Synonym for V_$RESULT_CACHE_OBJECTS
V$RESULT_CACHE_STATISTICS      Synonym for V_$RESULT_CACHE_STATISTICS

11 rows selected.

相关的参数主要有：

SQL> show parameter result_cache_max

NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_result              integer    5
result_cache_max_size                big integer 992K

类似的，result_cache_max_size参数可以被看作Server Result Cache的一个开关，如果设置为0，则Server Result Cache功能会被禁用。而result_cache_max_result则用于定义每个Cache结果集能够使用的Result Cache的百分比。

参数result_cache_mode用于控制Server Result Cache的方式，该参数有3个选项：Manual、AUTO、Force

SQL> show parameter result_cache_mo     

NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
result_cache_mode                    string      MANUAL

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

 

 

 

参考Metalink: SQL Query Result Cache

A separate shared memory pool is now used for storing and retrieving cached results. Query retrieval from the query result cache is faster than rerunning the query. Frequently executed queries will see performance improvements when using the query result cache.

The new query result cache enables explicit caching of results in database memory. Subsequent queries using the cached results will experience significant performance improvements.

Enable Result Caching :

You can set the RESULT_CACHE_MODE initialization parameter to control whether the SQL query result cache is used for all queries (when possible).

This parameter can be set at the system, session, or table level to the following values:

MANUAL - The results of a query can only be stored in the result cache by using the result_cache hint.
FORCE - All results are stored in the result cache.

Example:
If the RESULT_CACHE_MODE parameter is set to MANUAL then you must explicitly use the /*+ result_cache */ hint in your query to store the results of a query in the result cache.

SELECT /*+ result_cache */ deptno, avg(sal)
FROM emp
GROUP BY deptno;

If the RESULT_CACHE_MODE parameter is set to FORCE, and you do not wish to include the result of the query in the result cache, then you must use the /*+ no_result_cache */ hint in your query.

SELECT /*+ no_result_cache */ deptno, avg(sal)
FROM emp
GROUP BY deptno;

The database automatically invalidates a cached result whenever a transaction modifies the data or metadata of any of the database objects used to construct that cached result.