作者:dbtan |【转载时请以超链接形式标明文章出处作者信息】


Oracle 11g新特性:Result Cache

结果集缓存(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大小的百分比。

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可以拒绝缓存)。

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

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 -