ITPub博客

首页 > 数据库 > Oracle > 使用RESULT CACHE加速SQL查询效率

使用RESULT CACHE加速SQL查询效率

原创 Oracle 作者:hooca 时间:2016-04-25 14:58:25 0 删除 编辑
在Oracle 11g中,RESULT CACHE特性使Oracle能将SQL查询结果缓存到SGA的shared pool中,当有相同的SQL语句要求查询时,若发现结果无变化,则将缓存的结果返回,无需访问磁盘。这大大加速了查询效率。

有以下几个参数需要关注

点击(此处)折叠或打开

  1. SQL> show parameter result_cache

  2. NAME TYPE
  3. ------------------------------------ ----------------------
  4. VALUE
  5. ------------------------------
  6. client_result_cache_lag big integer
  7. 3000
  8. client_result_cache_size big integer
  9. 0
  10. result_cache_max_result integer
  11. 5
  12. result_cache_max_size big integer
  13. 4M
  14. result_cache_mode string
  15. MANUAL
  16. result_cache_remote_expiration integer
  17. 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');
  3. 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
  4. //第2次查询
  5. 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');
  6. 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-09-29

  • 博文量
    215
  • 访问量
    403385