Result Cache

来源：互联网发布：实验室数据管理制度编辑：程序博客网时间：2024/05/21 07:12

结果集缓存（Result Cache）是Oracle Database 11g新引入的功能，除了可以在服务器端缓存结果集（Server Result Cache）之外，还可以在客户端缓存结果集（Client Result Cache）。下面着重介绍一下服务器端结果集缓存。

服务器端的Result Cache Memorey由两部分组成。
·SQL Query Result Cache：存储SQL查询的结果集。
·PL/SQL Function Result Cache：用于存储PL/SQL函数的结果集。

Oracle 通过一个新引入的初始化参数result_cache_max_size来控制该Cache的大小。如果result_cache_max_size=0则表示禁用该特性。参数result_cache_max_result则控制单个缓存结果可以占总的Server Result Cache大小的百分比。

官方对result_cache_max_size的解释：

PropertyDescriptionParameter typeBig integerSyntaxRESULT_CACHE_MAX_SIZE = integer [K | M | G]Default valueDerived from the values of SHARED_POOL_SIZE, SGA_TARGET, and MEMORY_TARGETModifiableALTER SYSTEMRange of values0 to operating system-dependentBasicNoOracle RACYou must either set this parameter to 0 on all instances to disable the result cache, or use a nonzero value on all instances. Disabling the result cache on some instances may lead to incorrect results.

RESULT_CACHE_MAX_SIZE specifies the maximum amount of SGA memory (in bytes) that can be used by the Result Cache. Values of this parameter greater than 0are rounded up to the next multiple of 32 KB. If the value of this parameter is 0, then the feature is disabled.

tq@CCDB> select * from v$version where rownum < 2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
tq@CCDB> show parameter result_cache
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

上面显示的参数中result_cache_mode用于控制Server Result cache的模式，该参数有3个可选设置。
·设置auto：则优化器会自动判断是否将查询结果缓存。
·设置manual：则需要通过查询提示result_cache来告诉优化器是否缓存结果。
·设置force：则尽可能地缓存查询结果（通过提示no_result_cache可以拒绝缓存）。

官方对RESULT_CACHE_MODE的解释：

PropertyDescriptionParameter typeStringSyntaxRESULT_CACHE_MODE = { MANUAL | FORCE }Default valueMANUALModifiableALTER SESSION, ALTER SYSTEMBasicNo

RESULT_CACHE_MODE specifies when a ResultCache operator is spliced into a query's execution plan.

Values:

MANUAL
The ResultCache operator is added only when the query is annotated (that is, hints).
FORCE
The ResultCache operator is added to the root of all SELECT statements (provided that it is valid to do so).
Note:
FORCE mode is not recommended because the database and clients will attempt to cache all queries, which may create significant performance and latching overhead. Moreover, because queries that call non-deterministic PL/SQL functions are also cached, enabling the result cache in such a broad-based manner may cause material changes to the results.

For the FORCE setting, if the statement contains a NO_RESULT_CACHE hint, then the hint takes precedence over the parameter setting.

下面通过测试来看一下这一新特性的使用及优势所在，首先创建一张测试表：

tq@CCDB> create table dbtan as select * from dba_objects;
Table created.

在以前的版本中，第一次执行该SQL可以看到consistent gets和physical reads大致相同：

tq@CCDB> set autotrace on
tq@CCDB> select count(*) from dbtan;
COUNT(*)
----------
     70439
Statistics
----------------------------------------------------------
         28 recursive calls
          0 db block gets
       1119 consistent gets
       1036 physical reads

再次执行同样查询时，由于数据Cache在内存中，physical reads会减少到0，但是consistent gets很难降低：

tq@CCDB> select count(*) from dbtan;
COUNT(*)
----------
     70439
Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       1041 consistent gets
          0 physical reads

现在再来看看在Server Result Cache下Oracle的行为，首先在result_cache_mode参数设置为MANUAL时：

tq@CCDB> show parameter result_cache_mode
NAME TYPE VALUE
------------------------------------ -------------------- -----------------
result_cache_mode string MANUAL

需要在SQL语句中手工指定Cache，这需要通过加入一个hints来实现，这个hints是result_cache：

tq@CCDB> select /*+ result_cache */ count(*) from dbtan;
COUNT(*)
----------
     70439
Execution Plan
----------------------------------------------------------
Plan hash value: 1782547706
------------------------------------------------------------------------------------------
| Id | Operation           | Name                       | Rows | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |   290   (1)| 00:00:04 |
|   1 | RESULT CACHE       | g6tx53yfbxr2fah44y3vvdp4hc |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| DBTAN                      | 78689 |   290   (1)| 00:00:04 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=1; dependencies=(TQ.DBTAN); attributes=(single-row); name="select /*+ result_cache */count(*) from dbtan"
Statistics
----------------------------------------------------------
          4 recursive calls
          0 db block gets
       1117 consistent gets
          0 physical reads

注意到这个执行计划已经和以往的不同，RESULT CACHE以g6tx53yfbxr2fah44y3vvdp4hc名称创建。那么在接下来的查询中，这个Result Cache就可以被利用：

tq@CCDB> select /*+ result_cache */ count(*) from dbtan;
COUNT(*)
----------
     70439
Execution Plan
----------------------------------------------------------
Plan hash value: 1782547706
------------------------------------------------------------------------------------------
| Id | Operation           | Name                       | Rows | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |   290   (1)| 00:00:04 |
|   1 | RESULT CACHE       | g6tx53yfbxr2fah44y3vvdp4hc |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| DBTAN                      | 78689 |   290   (1)| 00:00:04 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=1; dependencies=(TQ.DBTAN); attributes=(single-row); name="select /*+ result_cache */ count(*) from dbtan"
Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          0 consistent gets
          0 physical reads
          0 redo size

在这个利用到Result Cache的查询中，consistent gets减少到0，直接访问结果集，不再需要执行SQL查询。这就是Result Cache的强大之处。

在以上的测试中，当result_cache_mode设置为MANUAL时，只有使用hints的情况下，Oracle才会利用缓存结果集；而如果将result_cache_mode设置为AUTO，Oracle如果发现缓冲结果集已经存在，那么就会自动使用。但是如果缓冲结果集不存在，Oracle并不会自动进行缓冲，只有使用HINTS的情况下，Oracle才会将执行的结果集缓存。

可以通过查询v$result_cache_memory视图来看Cache的使用情况：

tq@CCDB> select * from v$result_cache_memory where free='NO';
        ID      CHUNK     OFFSET FREE    OBJECT_ID   POSITION
---------- ---------- ---------- ------ ---------- ----------
         0          0          0 NO              0          0
         1          0          1 NO              1          0
         2          0          2 NO              2          0
         3          0          3 NO              3          0

通过V$RESULT_CACHE_STATISTICS可以查询Result Cache的统计信息：

tq@CCDB> 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                                 2
         6 Create Count Failure                                 0
         7 Find Count                                           2
         8 Invalidation Count                                   1
         9 Delete Count Invalid                                 0
        10 Delete Count Valid                                   0

V$RESULT_CACHE_OBJECTS记录了Cache的对象：

tq@CCDB> select id,type,name,block_count,row_count from v$result_cache_objects;
        ID TYPE            NAME                                           BLOCK_COUNT ROW_COUNT
---------- --------------- ---------------------------------------------- ----------- ----------
         2 Dependency      TQ.DBTAN                                                 1          0
         0 Dependency      object-id(98106)                                         1          0
         3 Result          select /*+ result_cache */ count(*) from dbtan           1          1
         1 Result          select /*+ result_cache */ count(*) from t               1          1

一个新的系统包被引入，DBMS_RESULT_CACHE可以用于执行关于Result Cache的管理：

tq@CCDB> set serveroutput on
tq@CCDB> 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 = 169352 bytes [0.045% of the Shared Pool]
... Fixed Memory = 5296 bytes [0.001% of the Shared Pool]
... Dynamic Memory = 164056 bytes [0.044% of the Shared Pool]
....... Overhead = 131288 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 28 blocks
........... Used Memory = 4 blocks
............... Dependencies = 2 blocks (2 count)
............... Results = 2 blocks
................... SQL     = 1 blocks (1 count)
................... Invalid = 1 blocks (1 count)
PL/SQL procedure successfully completed.

- The End -

0 0