查询结果集cache--oracle 11g release 2

最新推荐文章于 2024-07-02 08:24:59 发布

haiross

最新推荐文章于 2024-07-02 08:24:59 发布

阅读量705

点赞数

分类专栏： oracle SQL语句优化调优 oracle体系架构 oracle系统性能优化及管理

oracle体系架构同时被 3 个专栏收录

260 篇文章

订阅专栏

oracle SQL语句优化调优

194 篇文章

订阅专栏

oracle系统性能优化及管理

174 篇文章

订阅专栏

本文详细介绍并测试了Oracle 11g中新增的查询结果集缓存机制，该机制允许存储SQL查询结果以供后续重用，从而避免了重复计算和I/O操作，显著提高了查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查询结果集cache--oracle 11g release 2

Cache是oracle体系里边一个耳熟能详的机制，在历来的版本演变过程中，oracle都是不遗余力的扩展这个机制。现在我们已经很熟悉了buffer cache，keep cache，library cache，shared pool，PGA/UGA等。除了数据cache和游标共享之外，oracle现在已经支持普通编程模式上的cache；比如子查询cache，全局临时表和联合数组。在11g的版本中，oracle更是扩大了这方面的功能，在服务端和客户端增加了结果集cache。

11g版本中增加的三种结果集cache新特性：

查询结果集cache
PL/SQL函数结果集cache
客户端OCI结果集cache

本文只阐述查询结果集cache机制，PL/SQL函数结果集cache特性将是另外一篇文章的主题。

概述

查询结果集cache通常用来存储SQL语句的查询结果，以便重用。通过这个机制，oracle可以避免潜在的时间消耗和不必要的重复操作（比如，排序/聚合，物理I/O，联合查询等），被cache的结果集存储在内存专属区域，在实例范围内可用（而并非仅仅限于一个会话）。不同于关联数组，全局的临时表等解决方案，查询结果集cache对于应用程序来说完全透明，并由oracle自动来维护其一致性。

本文将详细的介绍并测试查询结果集cache机制。

数据库配置

下面将列出与查询结果集cache机制相关的数据库配置参数：

SQL> SELECT name, value, isdefault
  2  FROM   v$parameter
  3  WHERE  name LIKE 'result_cache%';

NAME                               VALUE              ISDEFAULT
---------------------------------- ------------------ ---------
result_cache_mode                  MANUAL             TRUE
result_cache_max_size              1081344            TRUE
result_cache_max_result            5                  TRUE
result_cache_remote_expiration     0                  TRUE

4 rows selected.

    简短的介绍一下这些参数：

result_cache_mode: 结果集cache的模式，一共有三种方式，hint，alter session和alter system，默认我们需要通过RESULT_CACHE hint 来显示的实现cache。

result_cache_max_size:这个参数限定结果集的最大值，以bytes为单位。缓存从共享池中分配，但又分别管理（举个例子，刷新共享池并不会清空结果集缓存）

result_cache_max_result:这个参数限定了一个结果集所使用的总缓存的最大百分比。

result_cache_remote_expiration:这个参数限定了依赖于远程对象的结果集的有效时间，默认值为0，即不cache这样的结果集。

缓存的大小可以动态的调整，下面将加倍cache的值，以便后面的例子能够实现更好的结果。
 
SQL> ALTER SYSTEM SET result_cache_max_size = 2M SCOPE = MEMORY;

System altered.

SQL> SELECT name, value
  2  FROM   v$parameter
  3  WHERE  name = 'result_cache_max_size';

NAME                                     VALUE
---------------------------------------- -------------------------
result_cache_max_size                    2097152

1 row selected.
   设定这些参数都非常的简单。 下面将通过几个例子来演示本文的主题。
 
   手动cache结果集

   oracle实例中cache的默认方式是手工的（manual），即除非使用RESULT_CACHE hint方式，才能使oracle cache查询的结果集。下面的例子，将演示一个手工缓存一个聚合查询的结果集：

SQL> SELECT value
  2  FROM   v$parameter
  3  WHERE  name = 'result_cache_mode';

VALUE
----------------
MANUAL

1 row selected.

 下面运行查询语句并cache结果集，使用autotrace配置来查看查询语句统计信息和执行计划。

 
SQL> set autotrace traceonly

SQL> set timing on

SQL> SELECT /*+ RESULT_CACHE */
  2         p.prod_name
  3  ,      SUM(s.amount_sold)   AS total_revenue
  4  ,      SUM(s.quantity_sold) AS total_sales
  5  FROM   sales s
  6  ,      products p
  7  WHERE  s.prod_id = p.prod_id
  8  GROUP  BY
  9         p.prod_name;

71 rows selected.

Elapsed: 00:00:05.00
  使用了RESULT_CACHE hint，oracle cache了结果集，查询返回71行数据共花费了5秒。下表显示本次查询的统计信息和执行计划（注：这里只是理论上的而非实际的执行计划）。
Execution Plan
----------------------------------------------------------
Plan hash value: 504757596

----------------------------------------------------------------------- ... -----------------
| Id  | Operation                | Name                       | Rows  | ... | Pstart| Pstop |
----------------------------------------------------------------------- ... -----------------
|   0 | SELECT STATEMENT         |                            |    71 | ... |       |       |
|   1 |  RESULT CACHE            | 091zc7mvn8ums36mbd2gqac4h0 |       | ... |       |       |
|   2 |   HASH GROUP BY          |                            |    71 | ... |       |       |
|*  3 |    HASH JOIN             |                            |    72 | ... |       |       |
|   4 |     VIEW                 | VW_GBC_5                   |    72 | ... |       |       |
|   5 |      HASH GROUP BY       |                            |    72 | ... |       |       |
|   6 |       PARTITION RANGE ALL|                            |   918K| ... |     1 |    28 |
|   7 |        TABLE ACCESS FULL | SALES                      |   918K| ... |     1 |    28 |
|   8 |     TABLE ACCESS FULL    | PRODUCTS                   |    72 | ... |       |       |
----------------------------------------------------------------------- ... -----------------

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

   3 - access("ITEM_1"="P"."PROD_ID")

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

   1 - column-count=3; dependencies=(SH.SALES, SH.PRODUCTS); parameters=(nls); name="SELECT /*+ RESULT_CACHE */
       p.prod_name
,      SUM(s.amount_sold)   AS total_revenue
,      SUM(s.quantity_sold) AS total_"
 高亮的部分包含了一些新的信息：
     首先：ID=1时的操作是“RESULT CACHE”，这是本次查询的最后一步，指示oracle cache了结果集。
     其次：除了RESULT CACHE操作外，还有一个系统产生的名称，通常作为一个查找key，用在在系统内部匹配查询语句和cache的结果集。
     最后：有一段新的关于cache的报告，这部分内容包含了结果集所依赖的对象及主要的sql语句片段。
 下面是autotrace显示的查询统计信息：
Statistics
----------------------------------------------------------
      14871  recursive calls
          0  db block gets
       4890  consistent gets
       1745  physical reads
          0  redo size
       3526  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
        136  sorts (memory)
          0  sorts (disk)
         71  rows processed
    查询产生了许多的I/O和sql递归调用，下面再一次运行查询，并对比统计信息。
SQL> SELECT /*+ RESULT_CACHE */
  2         p.prod_name
  3  ,      SUM(s.amount_sold)   AS total_revenue
  4  ,      SUM(s.quantity_sold) AS total_sales
  5  FROM   sales s
  6  ,      products p
  7  WHERE  s.prod_id = p.prod_id
  8  GROUP  BY
  9         p.prod_name;

71 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 504757596

----------------------------------------------------------------------- ... -----------------
| Id  | Operation                | Name                       | Rows  | ... | Pstart| Pstop |
----------------------------------------------------------------------- ... -----------------
|   0 | SELECT STATEMENT         |                            |    71 | ... |       |       |
|   1 |  RESULT CACHE            | 091zc7mvn8ums36mbd2gqac4h0 |       | ... |       |       |
|   2 |   HASH GROUP BY          |                            |    71 | ... |       |       |
|*  3 |    HASH JOIN             |                            |    72 | ... |       |       |
|   4 |     VIEW                 | VW_GBC_5                   |    72 | ... |       |       |
|   5 |      HASH GROUP BY       |                            |    72 | ... |       |       |
|   6 |       PARTITION RANGE ALL|                            |   918K| ... |     1 |    28 |
|   7 |        TABLE ACCESS FULL | SALES                      |   918K| ... |     1 |    28 |
|   8 |     TABLE ACCESS FULL    | PRODUCTS                   |    72 | ... |       |       |
----------------------------------------------------------------------- ... -----------------

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

   3 - access("ITEM_1"="P"."PROD_ID")

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

   1 - column-count=3; dependencies=(SH.SALES, SH.PRODUCTS); parameters=(nls); name="SELECT /*+ RESULT_CACHE */
       p.prod_name
,      SUM(s.amount_sold)   AS total_revenue
,      SUM(s.quantity_sold) AS total_"


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       3526  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         71  rows processed
    统计报告显示，相比第一次查询，本次没有发生I/O，排序，sql递归调用，oracle检测到cache的结果集满足查询需求，所以本次查询仅花费0.1秒。
    有趣的是，本次查询的执行计划没有发生变化（因为sql语句没有被重复解析），但是这里却很容易被误导，事实上，执行计划中的操作并没有被执行，但是执行计划中的result cache操作应该设定一个标志，表明重用了cache的结果集。

参考：

PGA 结果集 Oracle 谷歌