使用RESULT CACHE加速SQL查詢效率

在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部落格 ” ，連結：http://blog.itpub.net/22621861/viewspace-2087899/，如需轉載，請註明出處，否則將追究法律責任。

使用RESULT CACHE加速SQL查詢效率

相關文章