使用RESULT CACHE加速SQL查詢效率

hooca發表於2016-04-25
SQL
在Oracle 11g中，RESULT CACHE特性使Oracle能將SQL查詢結果快取到SGA的shared pool中，當有相同的SQL語句要求查詢時，若發現結果無變化，則將快取的結果返回，無需訪問磁碟。這大大加速了查詢效率。

有以下幾個引數需要關注

點選(此處)摺疊或開啟

  1. SQL> show parameter result_cache

  3. NAME TYPE
  4. ------------------------------------ ----------------------
  5. VALUE
  6. ------------------------------
  7. client_result_cache_lag big integer
  8. 3000
  9. client_result_cache_size big integer
  10. 0
  11. result_cache_max_result integer
  12. 5
  13. result_cache_max_size big integer
  14. 4M
  15. result_cache_mode string
  16. MANUAL
  17. result_cache_remote_expiration integer
  18. 0
一般用預設值即可，具體含義略。
如果要使用result_cache的結果返回，需要在SQL查詢語句中加上HINT
/*+ RESULT_CACHE */

看下例：

點選(此處)摺疊或開啟

  1. set autotrace on

  2. //第一次查詢，不用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');
  4. 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
  5. //第2次查詢
  6. 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');

  7. 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的物件

點選(此處)摺疊或開啟

  1. select id,type,status,name from v$result_cache_objects;

  2.         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

點選(此處)摺疊或開啟

  1. 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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章