oracle 11g 之 result cache

标签： oracle 11g result | 发表时间：2013-10-13 05:46 | 作者：xiaohai20102010
出处：http://blog.csdn.net

oracle 11g 之 result cache

  今天是2013-10-12，打算最近时间研究一下shared pool的相关原理以及awr报告分析。今天学习一下在oracle 11g shared pool中新增的一个cache 那就是result cache。

从上图可以看出在oracle 11g 的shared pool中存有对sql进行存储、存储sql执行计划、sql解析树等信息的library cache可以通过查看v$librarycahe视图了解其分配情况，以及row CACHE（data dictionary cache）可以查看v$rowcache视图了解其分配情况，对于这两部分内容咱不讨论，但是可以看一下如下这个图，大体明白这两个component的作用原理。

 

     现在开始研究一下result cache，对于oracle 11g 分为client result cache以及server result cache，前者在client进行内存的分配，后者对于数据库server进行内存分配，现在看一下server result cache（如下皆是server result cache内容）。说白了，result cache 就是为了缓存结果集的一块区域，主要是缓存sql 查询结果集以及pl/sql function结果集。

    对于 result cache存在几个参数，如下：

result_cache_max_result              integer    
result_cache_max_size                big integer
result_cache_mode                    string      MANUAL（AUTO、FORCE)

1）、result_cache_max_result 表示对于单个的缓存结果占整个result cache 大小的百分比。

2）、result_cache_max_size 参数用于设置该result cache 的大小，是一个动态参数，该参数为0 则说明result cache 功能禁用了。

3）、result_cache_mode，表示result cache的模式，其中有manual、force。manual 表示只有使用hints（result_cache）才可以对其结果进行缓存且当从result cache中获取结果集的时候也必须使用hints（result cache）参数；force表示强制对结果集进行缓存 oracle对该参数的设置不建议，如下：

FORCE mode is not recommended because the database and clients will attempt to cache all queries, which may create significant performance and latching overhead. Moreover, because queries that call non-deterministic PL/SQL functions are also cached, enabling the result cache in such a broad-based manner may cause material changes to the results.

如果需要不对操作进行缓存可以使用hints（no_result_cache)进行设置。

如下是练习过程：

  一、 

     设置result cache 大小为20M，模式为manual，每个结果集占用总的cache比例为50%（为了测试方便）；

SQL> alter system set result_cache_mode=manual;

System altered.

SQL> alter system set  result_cache_max_result=50;

System altered.

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     50
result_cache_max_size                big integer 20M
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0
SQL>

那么启用result cache 与不启用的效果在那呢？如下测试见证分晓！

eg：

SQL> set autotrace trace
SQL> select count(*) from rhys.amy;


Execution Plan
----------------------------------------------------------
Plan hash value: 2204613761

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   348   (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| AMY  | 87260 |   348   (1)| 00:00:05 |
-------------------------------------------------------------------


Statistics
----------------------------------------------------------
         61  recursive calls
          0  db block gets
       1271  consistent gets
       1246  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> r
  1* select count(*) from rhys.amy


Execution Plan
----------------------------------------------------------
Plan hash value: 2204613761

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   348   (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| AMY  | 87260 |   348   (1)| 00:00:05 |
-------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1248  consistent gets
       1246  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> r
  1* select count(*) from rhys.amy



Execution Plan
----------------------------------------------------------
Plan hash value: 2204613761

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   348   (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| AMY  | 87260 |   348   (1)| 00:00:05 |
-------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1248  consistent gets
       1246  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SQL> 
SQL>

首先看到查看rhys下的amy这张表在稳定下来的时候一致性读为1248，物理读为1246（万源之恶）。

现在使用result cache 功能。

SQL> select /*+result_cache*/ count(*) from rhys.amy;


Execution Plan
----------------------------------------------------------
Plan hash value: 2204613761

------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |   348   (1)| 00:00:05 |
|   1 |  RESULT CACHE       | 6tux55tbcpqfj66980yb24pfbh |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| AMY                        | 87260 |   348   (1)| 00:00:05 |
------------------------------------------------------------------------------------------

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

   1 - column-count=1; dependencies=(RHYS.AMY); attributes=(single-row); name="select /*+result_cache*/ count(*) from rhys.amy"


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1248  consistent gets
       1246  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

第一次执行该语句并将其缓存到result cache中，一致性读以及物理 读没有变化。

如下使用hints（result_cache）进行在此查询该结果集。如下：

SQL> select /*+result_cache*/ count(*) from rhys.amy;


Execution Plan
----------------------------------------------------------
Plan hash value: 2204613761

------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |   348   (1)| 00:00:05 |
|   1 |  RESULT CACHE       | 6tux55tbcpqfj66980yb24pfbh |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| AMY                        | 87260 |   348   (1)| 00:00:05 |
------------------------------------------------------------------------------------------

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

   1 - column-count=1; dependencies=(RHYS.AMY); attributes=(single-row); name="select /*+result_cache*/ count(*) from rhys.amy"


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

SQL>

可见物理读和一致性读都没有了，直接获得了结果集。这是好事啊。呵呵。

对于mode为force模式演示如下：

SQL> alter system set result_cache_mode=force;

System altered.

SQL> 
SQL> set autotrace trace
SQL> select count(*) from rhys.amy;


Execution Plan
----------------------------------------------------------
Plan hash value: 2204613761

------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |   348   (1)| 00:00:05 |
|   1 |  RESULT CACHE       | 6tux55tbcpqfj66980yb24pfbh |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| AMY                        | 87260 |   348   (1)| 00:00:05 |
------------------------------------------------------------------------------------------

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

   1 - column-count=1; dependencies=(RHYS.AMY); attributes=(single-row); name="select count(*) from rhys.amy"


Statistics
----------------------------------------------------------
         63  recursive calls
          8  db block gets
       1327  consistent gets
       1246  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> r
  1* select count(*) from rhys.amy


Execution Plan
----------------------------------------------------------
Plan hash value: 2204613761

------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |   348   (1)| 00:00:05 |
|   1 |  RESULT CACHE       | 6tux55tbcpqfj66980yb24pfbh |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| AMY                        | 87260 |   348   (1)| 00:00:05 |
------------------------------------------------------------------------------------------

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

   1 - column-count=1; dependencies=(RHYS.AMY); attributes=(single-row); name="select count(*) from rhys.amy"


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

SQL>


另外在联机手册中并没提及到result_cache_mode 存在auto模式，但是我在不经意输错的是否发现了这个值。

SQL> alter system set result_cache_mode=false;
alter system set result_cache_mode=false
*
ERROR at line 1:
ORA-00096: invalid value FALSE for parameter result_cache_mode, must be from amongFORCE, MANUAL, AUTO

查看资料发现auto，是只有使用hints的时候才能起作用，但是当再次查询结果的时候没必要在使用hints了，而是直接把结果取出来。测试如下：

SQL> set autotrace off
SQL> execute dbms_result_cache.flush;

PL/SQL procedure successfully completed.

SQL> alter system set result_cache_mode=auto;

System altered.

SQL> set autotrace trace
SQL> select /*+result_cache*/ count(*) from rhys.amy;


Execution Plan
----------------------------------------------------------
Plan hash value: 2204613761

------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |   348   (1)| 00:00:05 |
|   1 |  RESULT CACHE       | 6tux55tbcpqfj66980yb24pfbh |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| AMY                        | 87260 |   348   (1)| 00:00:05 |
------------------------------------------------------------------------------------------

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

   1 - column-count=1; dependencies=(RHYS.AMY); attributes=(single-row); name="select /*+result_cache*/ count(*) from rhys.amy"


Statistics
----------------------------------------------------------
         49  recursive calls
          0  db block gets
       1275  consistent gets
       1246  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> r
  1* select /*+result_cache*/ count(*) from rhys.amy


Execution Plan
----------------------------------------------------------
Plan hash value: 2204613761

------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |   348   (1)| 00:00:05 |
|   1 |  RESULT CACHE       | 6tux55tbcpqfj66980yb24pfbh |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| AMY                        | 87260 |   348   (1)| 00:00:05 |
------------------------------------------------------------------------------------------

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

   1 - column-count=1; dependencies=(RHYS.AMY); attributes=(single-row); name="select /*+result_cache*/ count(*) from rhys.amy"


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


二、

result cache 管理

对于11g中新增了result cache 那么也新增了4个视图以及一个包。

相关视图如下：

  • v$result_cache_dependency
  • v$result_cache_memory
  • v$result_cache_objects
  • v$result_cache_statistics

    就不在介绍了，可以参考联机手册进行分析以及学习：

    http://www.oracle.com/pls/db112/search?word=v$result_cache_

    现在看一个dbms_result_cache包：

    存在有6个subprograms，

    Subprogram Description

    BYPASS Procedure

    Sets the bypass mode for the Result Cache

    FLUSH Function & Procedure

    Attempts to remove all the objects from the Result Cache, and depending on the arguments retains or releases the memory and retains or clears the statistics

    INVALIDATE Functions & Procedures

    Invalidates all the result-set objects that dependent upon the specified dependency object

    INVALIDATE_OBJECT Functions & Procedures

    Invalidates the specified result-set object(s)

    MEMORY_REPORT Procedure

    Produces the memory usage report for the Result Cache

    STATUS Function

    Checks the status of the Result Cache

    演示二个：

    其他参考：

    http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_result_cache.htm#CHDJCFJG

    SQL> set serveroutput on
SQL> execute 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  = 20M bytes (20K blocks)
Maximum Result Size = 10M bytes (10K blocks)
[Memory]
Total Memory = 168264 bytes [0.134% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.004% of the Shared Pool]
... Dynamic Memory = 162912 bytes [0.129% of the Shared Pool]
....... Overhead = 130144 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL     = 1 blocks (1 count)

PL/SQL procedure successfully completed.

SQL> execute dbms_result_cache.memory_report(true);
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  = 20M bytes (20K blocks)
Maximum Result Size = 10M bytes (10K blocks)
[Memory]
Total Memory = 168264 bytes [0.134% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.004% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr  = 208 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 162912 bytes [0.129% of the Shared Pool]
....... Overhead = 130144 bytes
........... Hash Table    = 64K bytes (4K buckets)
........... Chunk Ptrs    = 24K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 130144 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL     = 1 blocks (1 count)

PL/SQL procedure successfully completed.


     

    SQL> execute dbms_result_cache.flush;

PL/SQL procedure successfully completed.

SQL> execute dbms_result_cache.memory_report(true);
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          = 0 bytes
Maximum Cache Size  = 0 bytes (0 blocks)
Maximum Result Size = 0 bytes (0 blocks)
[Memory]
Total Memory = 5352 bytes [0.004% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.004% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr  = 208 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.

SQL>


     


     

    • 作者：xiaohai20102010 发表于2013-10-12 21:46:40 原文链接
    阅读：69 评论：0 查看评论

    相关 [oracle 11g result] 推荐：

    oracle 11g 之 result cache

    - - CSDN博客数据库推荐文章
    oracle 11g 之 result cache.   今天是2013-10-12，打算最近时间研究一下shared pool的相关原理以及awr报告分析. 今天学习一下在oracle 11g shared pool中新增的一个cache 那就是result cache.      现在开始研究一下result cache，对于oracle 11g 分为client result cache以及server result cache，前者在client进行内存的分配，后者对于数据库server进行内存分配，现在看一下server result cache（如下皆是server result cache内容）.

    Linux下安装Oracle 11g

    - - Oracle - 数据库 - ITeye博客
    1、 下载安装VMware Workstation v9.0.2 虚拟机软件，下载rhel-server-6.0-x86_64-dvd.iso安装Red Hat Enterprise Linux 6 64-bit操作系统到VMware中，安装时候添加Linux用户Oracle. 2、 在Vmware虚拟机中设置光驱属性为使用ISO映像文件linux.x64_11gR2_database.iso，并连接到虚拟机，拷贝光驱中的Oracle11G安装文件夹database到虚拟机的/u02/目录下.

    [原]Oracle 11g 新特性 系列小结

    - - David Dai -- Focus on Oracle
    2012年12月15日，陆陆续续的花了几个星期的时间，把Oracle 11g的新特性熟悉了一遍,这里也只是部分特性，还有部分，如果以后有整理在补充进来. 这段时间Dave的状态并不太好，正所谓：如人饮水，冷暖自知，有些事只有经过了才能体会，不管怎么样，至少还有一件事是Dave 比较感兴趣，并且愿意为其付出很多的时间，那就是研究Oracle.

    Oracle 11g AWR 系列五：如何生成 AWR 报告？

    - - CSDN博客数据库推荐文章
    1.生成单实例 AWR 报告：. 2.生成 Oracle RAC AWR 报告：. 3.生成 RAC 环境中特定数据库实例的 AWR 报告:. 4.生成 Oracle RAC 环境中多个数据库实例的 AWR 报告的方法：. 5.生成 SQL 语句的 AWR 报告：. 6.生成特定数据库实例上某个 SQL 语句的 AWR 报告：.

    oracle 11g Flashback Data Archive(闪回数据归档)

    - - CSDN博客数据库推荐文章
    Flashback Data Archive(闪回数据归档). UNDO表空间记录的回滚信息虽然可以提供回闪查询,但时间久了,这些信息会被覆盖掉,其实只要事务一提交,他们就变成可覆盖的对象了,所以经常在做回闪查询时,我们会因为找不到undo block而收到1555错误,11G里面引入了Flashback Data Archive ,他用于存储数据的所有改变,时间由你自己设定,消耗的是更多的磁盘空间,现在来看下这个特性.

    （总结）Oracle 11g常用管理命令（用户、表空间、权限）

    - - 服务器运维与网站架构|Linux运维|X研究
    PS：下面是Oracle 11g最常用的基本管理命令，包括创建用户、表空间，权限分配等. 以下命令本人都验证操作过，加上了本人的小结说明. 从root切换到oracle用户进入：. 进入sqlplus环境，nolog参数表示不登录：. 注：要把Linux下的Oracle配置成服务自启动，请看：. （原创）CentOS Linux下配置Oracle 11gR2为系统服务自动启动.

    Oracle 11g系统自动收集统计信息的一些知识

    - - CSDN博客数据库推荐文章
    ---11g的是 周一到周五 22：00-2：00 周六周日 6：00-4：00. WINDOW_NAME          REPEAT_INTERVAL                                              DURATION. CLIENT_NAME                                                      STATUS.

    Oracle 收购 Ksplice

    - feng823 - LinuxTOY
    实现无需重启即可为 Linux 内核打安全补丁的 Ksplice 被 Oracle 收购. 在被收购前， Ksplice 为 Fedora, Ubuntu 免费提供该功能，对于 RHEL 和 CentOS 则需要订阅其产品. Oracle 表示将把 Ksplice 带来的零宕机安全更新功能添加到 Oracle 产品订阅服务中，同时停止对其他企业级 Linux 发行版的支持，将 Oracle Unbreakable Linux 打造成唯一具备零宕机安全更新功能的企业级 Linux 发行版.

    Linux Ksplice，MySQL and Oracle

    - Syn - DBA Notes
    Oracle 在 7 月份收购了 Ksplice. 使用了 Ksplice 的 Linux 系统，为 Kernel 打补丁无需重启动，做系统维护的朋友应该明白这是一个杀手级特性. 现在该产品已经合并到 Oracle Linux 中. 目前已经有超过 700 家客户，超过 10 万套系统使用了 Ksplice (不知道国内是否已经有用户了.

    oracle license计算

    - Fenng - eagle's home
    Oracle license的计算是基于CPU core的. 用core的数目乘以一个系数core factor就可以得到所需的oracle license的数目. 对于不同的CPU,core factor是不一样的，可以从oracle提供的这张列表中查到 Oracle Processor Core Factor Table.