在Oracle 11g中，RESULT CACHE特性使Oracle能将SQL查询结果缓存到SGA的shared pool中，当有相同的SQL语句要求查询时，若发现结果无变化，则将缓存的结果返回，无需访问磁盘。这大大加速了查询效率。

有以下几个参数需要关注

点击(此处)折叠或打开

SQL> 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
4M
result_cache_mode string
MANUAL
result_cache_remote_expiration integer
0

一般用默认值即可，具体含义略。
如果要使用result_cache的结果返回，需要在SQL查询语句中加上HINT
/*+ RESULT_CACHE */

看下例：

点击(此处)折叠或打开

set autotrace on
//第一次查询，不用result cache
select prod_id,cust_id from sales where time_id between to_date('2000-01-01', 'YYYY-MM-DD') and to_date('2000-01-03','YYYY-MM-DD');
Statistics
----------------------------------------------------------
        110 recursive calls
          0 db block gets
       1963 consistent gets
        824 physical reads
          0 redo size
      63782 bytes sent via SQL*Net to client
       2613 bytes received via SQL*Net from client
        192 SQL*Net roundtrips to/from client
          6 sorts (memory)
          0 sorts (disk)
       2854 rows processed
//第2次查询
select /*+ RESULT_CACHE */ prod_id,cust_id from sales where time_id between to_date('2000-01-01', 'YYYY-MM-DD') and to_date('2000-01-03','YYYY-MM-DD');
Execution Plan
----------------------------------------------------------
Plan hash value: 3529668017

--------------------------------------------------------------------------------
----------------------------

| Id | Operation                     | Name                       | Rows | Byt
es | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------
----------------------------

|   0 | SELECT STATEMENT              |                            | 8503 |   1
49K| 1738   (1)| 00:00:21 |

|   1 | RESULT CACHE                 | 6bhrdwm7rcqr486gmz2qc71h5m |       |
   |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID | SALES                      | 8503 |   1
49K| 1738   (1)| 00:00:21 |

|   3 |    BITMAP CONVERSION TO ROWIDS|                            |       |
   |            |          |

|* 4 |     BITMAP INDEX RANGE SCAN   | SALES_TIME_BIX             |       |
   |            |          |

--------------------------------------------------------------------------------
----------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("TIME_ID">=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss
') AND

              "TIME_ID"<=TO_DATE(' 2000-01-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss
'))

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

   1 - column-count=2; dependencies=(SH.SALES); attributes=(ordered); parameters
=(nls); name="select /*+ RESULT_CACHE */ prod_id,cust_id from sales where time_i
d between to_date('2000-01-01', 'YYYY-MM-DD') and to_date('200"

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          0 consistent gets
          0 physical reads
          0 redo size
      63782 bytes sent via SQL*Net to client
       2613 bytes received via SQL*Net from client
        192 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       2854 rows processed

其中，6bhrdwm7rcqr486gmz2qc71h5m是Cache ID。

查看使用了result cache的对象

点击(此处)折叠或打开

select id,type,status,name from v$result_cache_objects;
        ID TYPE                 STATUS
---------- -------------------- ------------------
NAME
--------------------------------------------------------------------------------
         0 Dependency           Published
SH.SALES

        19 Result               Published
select /*+ RESULT_CACHE */ prod_id,cust_id from sales where time_id between to_d
ate('2000-01-01', 'YYYY-MM-DD') and to_date('200

         1 Result               Invalid
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_

        ID TYPE                 STATUS
---------- -------------------- ------------------
NAME
--------------------------------------------------------------------------------
features_enable(default) opt_param('parallel_exe

         2 Result               Invalid
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_
features_enable(default) opt_param('parallel_exe

清空RESULT CACHE

点击(此处)折叠或打开

exec DBMS_RESULT_CACHE.FLUSH;

RESULT CACHE的使用限制：
1）RAC中只能供自身实例使用，但如果标记为INVALID，则整个RAC下都为INVALID
2) 绑定变量一定要变量值相同；
3）部分函数如sysdate()不支持。

在PL/SQL中，也可以使用RESULT_CACHE，需要先声明RESULT_CACHE RELIES_ON。详情略。

ITPub博客

使用RESULT CACHE加速SQL查询效率

最新文章