oracle result_cache基本概念和使用

原创 _ 云和恩墨 2022-10-18

1628

一、概述

结果缓存是内存区域，位于共享全局区域 (SGA) 或客户端应用程序内存中，用于存储数据库查询或查询块的结果以供重用。缓存的行在 SQL 语句和会话之间共享。

1.1、服务器结果缓存

服务器结果缓存是共享池中的一个内存池。此内存池由 SQL 查询结果缓存（存储 SQL 查询结果）和 PL/SQL 函数结果缓存（存储 PL/SQL 函数返回的值）组成。OLAP 应用程序可以从它的使用中显着受益。缓存的良好候选者是访问大量行但返回少量行的查询，例如数据仓库中的那些。例如，可以使用具有等价的高级查询重写来创建在结果缓存中实现查询的具体化视图，而不是使用表。

1.2、客户端结果缓存

OCI 驱动程序（例如 OCCI、JDBC OCI 驱动程序和 ODP.NET）支持客户端结果缓存。使用客户端结果缓存的性能优势包括，缩短查询响应时间、更有效地利用数据库资源、降低内存成本

二、配置服务器结果缓存

2.1、内存

默认情况下，在数据库启动时，Oracle 数据库将内存分配给共享池中的服务器结果缓存。分配的内存大小取决于共享池的内存大小和选择的内存管理系统：

自动共享内存管理：sga_target的 0.50% 分配给结果缓存。
手动共享内存管理：SHARED_POOL_SIZE的1%分配给结果缓存

2.2、参数

RESULT_CACHE_MAX_SIZE 指定分配给服务器结果缓存的内存。要禁用服务器结果缓存，请将此参数设置为 0。
RESULT_CACHE_MAX_RESULT 指定可用于单个结果的服务器结果高速缓存内存的最大量（百分比）。有效值介于 1 和 100 之间。默认值为 5%。
RESULT_CACHE_REMOTE_EXPIRATION 此参数用于指定依赖于远程数`据库对象的结果保持有效的时间（以分钟为单位）。默认值为 0，表示不缓存使用远程对象的结果。当设置为非零值时，当远程对象中的数据被修改时，结果可能会变得陈旧。
*RESULT_CACHE_MODE：*执行引擎根据 RESULT_CACHE_MODE 初始化参数的设置来管理结果缓存机制。这控制是否将查询结果发送到结果缓存。
- MANUAL：设置为 MANUAL（默认值）时，查询结果不存储在结果缓存中。如果单个查询需要缓存结果，则需要使用提示。
- FORCE：设置为 FORCE 时，结果缓存用于所有查询结果。

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            11808K
result_cache_mode                    string                 MANUAL
result_cache_remote_expiration       integer                0

三、配置客户端结果缓存

3.1、参数

CLIENT_RESULT_CACHE_SIZE 指定每个客户端进程的客户端结果缓存的最大大小。要启用客户端结果缓存，请将大小设置为 32768 字节或更大。较小的值（包括默认值 0）会禁用客户端结果缓存
CLIENT_RESULT_CACHE_LAG 指定客户端结果缓存的延迟时间量（以毫秒为单位）。默认值为 3000（3 秒）。如果 OCI 应用程序在一段时间内未执行任何数据库调用，则此设置会强制下一个语句执行调用检查验证。如果 OCI 应用程序不经常访问数据库，则将此参数设置为较低的值会导致从 OCI 客户端到数据库的往返次数更多，以保持客户端结果缓存与数据库同步。
COMPATIBLE 指定 Oracle 数据库必须保持兼容性的版本。要启用客户端结果缓存，此参数必须设置为 11.0.0.0 或更高版本。对于视图上的客户端缓存，此参数必须设置为 11.2.0.0.0 或更高版本。

四、结果缓存限制

当查询中包含以下对象或函数时，无法缓存结果：

SYS或SYSTEM模式中的临时表和表
序列CURRVAL和NEXTVAL伪列
SQL 函数CURRENT_DATE, CURRENT_TIMESTAMP, LOCAL_TIMESTAMP, USERENV/SYS_CONTEXT （带有非常量变量）, SYS_GUID, SYSDATE, 和SYSTIMESTAMP
客户端结果缓存对结果缓存有额外的限制。
结果缓存不适用于以只读模式打开的 Active Data Guard 备用数据库。

五、加入结果缓存的两种方法

5.1、使用 RESULT_CACHE 提示：当结果缓存模式MANUAL时，/+ RESULT_CACHE /提示指示数据库缓存查询块的结果，并在以后的执行中使用缓存的结果。/+ RESULT_CACHE /提示指示数据库不要将结果缓存在服务器或客户端结果缓存中。

5.2、缓存表注释。表注释影响整个查询，而不是查询段。使用表注释的主要好处是避免在应用程序级别向查询添加结果缓存提示的必要性。由于表注释的优先级低于 SQL 结果缓存提示，因此可以通过在查询级别使用提示来覆盖表和会话设置。

CREATE TABLE dept (...) RESULT_CACHE (MODE DEFAULT); 
ALTER TABLE dept RESULT_CACHE (MODE FORCE); 

-- 默认值,如果查询中至少有一个表设置为DEFAULT，则不会在该查询的表级别启用结果缓存，除非RESULT_CACHE_MODE初始化参数设置为FORCE或RESULT_CACHE指定了提示。
-- 如果一个查询的所有表都标记为FORCE，则查询结果被认为是缓存。表注释FORCE优先于会话级别 RESULT_CACHE_MODE参数值MANUAL。
-- 表DEFAULT注释可防止数据库在表级别缓存结果

六、监控结果缓存

V$RESULT_CACHE_STATISTICS
V$RESULT_CACHE_MEMORY
V$RESULT_CACHE_OBJECTS
V$RESULT_CACHE_DEPENDENCY
CLIENT_RESULT_CACHE_STATS$
DBA_TABLES, USER_TABLES,ALL_TABLES

七、案例测试

7.1、案例一：

7.1.1、参数检查

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            11808K
result_cache_mode                    string                 MANUAL
result_cache_remote_expiration       integer                0



--sys用户下的表不能结果缓存
SQL> set linesize 10000
SQL> set autotrace traceonly
SQL> select /*+ RESULT_CACHE */ count(*) from t;


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   763   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |   144K|   763   (1)| 00:00:01 |
-------------------------------------------------------------------


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



[oracle@19c02 ~]$ sqlplus scott/oracle

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 16 21:31:17 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sun Oct 16 2022 16:51:19 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> set linesize 1000
SQL> set autotrace traceonly
SQL> select /*+ RESULT_CACHE */ count(*) from t;


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |   393   (1)| 00:00:01 |
|   1 |  RESULT CACHE       | fs5rjt4my6jxz58khvgbhdxumu |     1 |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| T                          | 72511 |   393   (1)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - column-count=1; dependencies=(SCOTT.T); attributes=(single-row); name="select /*+ RESULT_CACHE */ count(*) from t"


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

SQL> /


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |   393   (1)| 00:00:01 |
|   1 |  RESULT CACHE       | fs5rjt4my6jxz58khvgbhdxumu |     1 |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| T                          | 72511 |   393   (1)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - column-count=1; dependencies=(SCOTT.T); attributes=(single-row); name="select /*+ RESULT_CACHE */ count(*) from t"


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        552  bytes sent via SQL*Net to client
        404  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
--普通用户使用了结果缓存，第二次明显没有物理读和逻辑读

7.3、黑名单

SQL> select /*+ RESULT_CACHE */ count(*) from t;

  COUNT(*)
----------
     72511

SQL> select cache_id, name from v$result_cache_objects where type='Result';

CACHE_ID                       NAME
------------------------------ --------------------------------------------------------------------------------
3ahzarbxb9s49b7m2tcnhgmbkw     select /*+ FIRST_ROWS(1) RESULT_CACHE */ count(*) from mdsys.sdo_feature_usage w
                               here used = 'Y' and rownum = 1

59n2vr0axcu2jffsr8wb84wdb7     select /*+ RESULT_CACHE */ count(*)  from T where owner='SYS'
--不能缓存,清空黑名单
SQL> begin
  2   dbms_result_cache.BLACK_LIST_CLEAR();
  3  end;
  4  /

PL/SQL procedure successfully completed.
--执行
SQL> select /*+ RESULT_CACHE */ count(*) from t;


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |   393   (1)| 00:00:01 |
|   1 |  RESULT CACHE       | fs5rjt4my6jxz58khvgbhdxumu |     1 |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| T                          | 72511 |   393   (1)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - column-count=1; dependencies=(SCOTT.T); attributes=(single-row); name="select /*+ RESULT_CACHE */ count(*) from t"


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        552  bytes sent via SQL*Net to client
        404  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
已经缓存进去
SQL> select cache_id, name from v$result_cache_objects where type='Result';

CACHE_ID                       NAME
------------------------------ --------------------------------------------------------------------------------
3ahzarbxb9s49b7m2tcnhgmbkw     select /*+ FIRST_ROWS(1) RESULT_CACHE */ count(*) from mdsys.sdo_feature_usage w
                               here used = 'Y' and rownum = 1

59n2vr0axcu2jffsr8wb84wdb7     select /*+ RESULT_CACHE */ count(*)  from T where owner='SYS'
fs5rjt4my6jxz58khvgbhdxumu     select /*+ RESULT_CACHE */ count(*) from t
--加入黑名单

SQL> begin
  2       dbms_result_cache.BLACK_LIST_ADD('fs5rjt4my6jxz58khvgbhdxumu',true);
  3  end;
  4  /

PL/SQL procedure successfully completed
--刷新结果缓存
SQL> begin
  2     dbms_result_cache.flush();
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select cache_id, name from v$result_cache_objects where type='Result';

no rows selected

--再次执行
SQL> select /*+ RESULT_CACHE */ count(*) from t;


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |   393   (1)| 00:00:01 |
|   1 |  RESULT CACHE       | fs5rjt4my6jxz58khvgbhdxumu |     1 |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| T                          | 72511 |   393   (1)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - column-count=1; dependencies=(SCOTT.T); attributes=(single-row); name="select /*+ RESULT_CACHE */ count(*) from t"


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1413  consistent gets
          0  physical reads
          0  redo size
        552  bytes sent via SQL*Net to client
        404  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
虽然提示在使用结果缓存，但1413个consistent gets说明并不是使用结果缓存。

八、案例二

SQL> create table t2 RESULT_CACHE (MODE DEFAULT) as select * from dba_objects;

Table created.

SQL> set autotrace traceonly
SQL> select count(*) from t2;


Execution Plan
----------------------------------------------------------
Plan hash value: 3321871023

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   394   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T2   | 72602 |   394   (1)| 00:00:01 |
-------------------------------------------------------------------


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

SQL> create table t3 RESULT_CACHE (MODE force) as select * from dba_objects;

Table created.

SQL> select count(*) from t3;


Execution Plan
----------------------------------------------------------
Plan hash value: 463314188

------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |   394   (1)| 00:00:01 |
|   1 |  RESULT CACHE       | ghsachzzjtsmc5mmw47dvj48np |     1 |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| T3                         | 72603 |   394   (1)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - column-count=1; dependencies=(SCOTT.T3); attributes=(single-row); name="select count(*) from t3"


Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
       1416  consistent gets
       1411  physical reads
          0  redo size
        552  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
表定义期间使用force时，会默认使用，defautl时需要hint提示加入结果缓存。

九、参考文档

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/tuning-result-cache.html#GUID-A76DEAB3-AB92-4B1F-BF87-1B0016B6A93F
Server Result Cache : Overview (Doc ID 1588763.1)
SQL Query Result Cache. Includes: [Video] (Doc ID 1108133.1)
Performance Issue With Application After 12.2.4 Result Cache Upgrade With Database 12.1.0.2 (Doc ID 2256587.1)

墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者