result cache

1 result cache 是什么： 通俗来说，他就是一个结果缓存，多次查询的时候可以直接查询这个结果缓存。 SQL语句的缓存结果集功能是Oracle11g中开始加入的一个新功能。 众所周知，buffer cache 之类的能够降低sql的物理读，但是对于逻辑读比较高的sq，还是没有很好的解决办法， 这个时候我们就可以尝试 result cache是否可能缓解这个问题。result cache 用于存储数据库查询或查询块的结果以供重用。 缓存的行在语句和会话之间共享，除非它们过期。一般来说数据仓库之类的表改变很少并且访问大量行但返回少量行的查询都能够得到很好的性能提升。 3 result cache的工作原理： 执行查询时，数据库会查看缓存内存以确定结果是否存在于缓存中。如果结果存在，则数据库将从内存中检索结果，而不是执行查询。 如果未缓存结果，则数据库将执行查询，将结果作为输出返回，并将结果存储在结果缓存中。 当用户重复执行查询和函数时，数据库会从缓存中检索行，从而缩短响应时间。修改依赖数据库对象中的数据时，缓存的结果将失效。 3 Result Cache 的好处： 3.1. “使用服务器结果缓存的好处取决于应用程序。OLAP 应用程序可以从它的使用中受益匪浅。适合缓存的查询是访问大量行但返回少量行的查询，例如数据仓库中的查询。 例如，您可以使用具有等效性的高级查询重写来创建具体化视图，这些视图在结果缓存中具体化查询，而不是使用表。 3.2. 由于使用结果缓存的查询会绕过缓冲区缓存，并且结果在缓存中，因此 CPU 消耗较少。 3.3. 没有物理 I/O，因为结果在缓存中再次可用。 4 不建议使用结果缓存的两种情况： 4.1. 结果缓存可能不适合具有高 DML 的易失性数据库。当表中的数据发生更改时，查询将失效，从而导致无法使用结果缓存查询。    因此，Oracle 建议对只读或以只读为主的数据库对象的查询进行结果缓存。 也就是说表数据变化很快的话不建议使用结果缓存，刷新可能很频繁。 4.2. 结果缓存数据存储在共享池中。如果服务器上的内存有限，则结果缓存可能不是最佳选项，因为它不会自动释放内存。    确保服务器有足够的内存来支持共享池大小，包括结果缓存的大小。   5 其他 SQL 查询结果缓存注意事项 5.1  对于包含以下内容的查询，将禁用结果缓存：    临时表或字典表    非确定性 PL/SQL 函数    序列 CURRVAL 和 NEXTVAL    SQL 函数 CURRENT_DATE、SYSDATE、SYS_GUID 等 5.2 远程数据库上的 DDL/DML 不会使缓存的结果过期 5.3 可以缓存闪回查询 5.4 结果缓存不会自动释放内存   它会增长，直到达到最大大小，我们可以通过 DBMS_RESULT_CACHE.FLUSH 清除内存 5.5 绑定变量  缓存的结果使用变量值进行参数化  只能找到相同变量值的缓存结果 5.6 在以下情况下，将不会生成缓存的结果：  查询基于非当前版本的数据构建（读取一致性强制）  当前会话对查询中的表有未完成的事务 6 result cache的相关参数 以下数据库初始化参数控制服务器结果缓存： RESULT_CACHE_MAX_SIZE 此参数设置分配给服务器结果缓存的内存。除非将此参数设置为0 在这种情况下，将禁用缓存，否则将启用服务器结果缓存。 RESULT_CACHE_MAX_RESULT 此参数设置可用于单个结果的服务器结果缓存内存的最大量。默认值为 5%，但您可以指定 1和 100之间的任何百分比值。您可以在系统或会话级别设置此参数。  RESULT_CACHE_REMOTE_EXPIRATION 此参数指定依赖于远程数据库对象的服务器结果缓存中结果的过期时间。默认值为 0 minutes，这意味着不应缓存使用远程对象的结果。 SQL> show parameter RESULT_CACHE_MAX_SIZE NAME      TYPE VALUE ------------------------------------ ----------- ------------------------------ result_cache_max_size      big integer 5248K SQL> show parameter RESULT_CACHE_MAX_RESULT NAME      TYPE VALUE ------------------------------------ ----------- ------------------------------ result_cache_max_result      integer 5 SQL> show parameter RESULT_CACHE_REMOTE_EXPIRATION NAME      TYPE VALUE ------------------------------------ ----------- ------------------------------ result_cache_remote_expiration      integer 0 注意：如果要使用查询结果高速缓存并将RESULT_CACHE_MODE初始化参数设置为MANUAL，则必须在查询中显式指定RESULT_CACHE 提示。 数据库会根据 RESULT_CACHE_MODE 参数的设置管理结果高速缓存机制，可以在系统和会话级别设置RESULT_CACHE_MODE 参数。 参数值可以是AUTO、MANUAL 和FORCE： (1)  设置为AUTO 时，优化程序将根据重复的执行操作确定将哪些结果存储在高速缓存中。 (2)  设置为MANUAL（默认值）时，必须使用RESULT_CACHE 提示指定在高速缓存中存储特定结果。 (3)  设置为FORCE 时，所有结果都将存储在高速缓存中。 注：对于AUTO 和FORCE 设置，如果语句中包含[NO_]RESULT_CACHE 提示，则该提示优先于参数设置。 SQL> show parameter RESULT_CACHE_MODE NAME      TYPE VALUE ------------------------------------ ----------- ------------------------------ result_cache_mode      string MANUAL 默认情况下，在数据库启动时，Oracle Database 将内存分配给共享池中的服务器结果高速缓存。 分配的内存大小取决于共享池和内存管理系统的内存大小。数据库使用以下算法： 当使用 initialization 参数指定MEMORY_TARGET 内存分配时，Oracle Database 将MEMORY_TARGET的 0.25% 分配给结果高速缓存。  当您使用 initialization 参数SGA_TARGET 设置共享池的大小时，Oracle Database 会将SGA_TARGET 的 0.50% 分配给结果高速缓存。 如果使用 initialization 参数SHARED_POOL_SIZE指定共享池的大小，则 Oracle Database 会将共享池大小的 1% 分配给结果高速缓存。  服务器结果缓存的大小会增长，直到达到最大大小。 7 result cache 相关的字典信息：   (1)V$RESULT_CACHE_STATISTICS : 列出各种高速缓存设置和内存使用量统计信息 (2)V$RESULT_CACHE_MEMORY : 列出所有内存块和相应的统计信息 (3)V$RESULT_CACHE_OBJECTS: 列出所有对象（高速缓存结果和依赖性）及其属性 (4)V$RESULT_CACHE_DEPENDENCY: 列出高速缓存结果之间的依赖性详细信息及依赖性 8 使用 DBMS_RESULT_CACHE 管理服务器结果高速缓存： 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  = 5248K bytes (5248 blocks) Maximum Result Size = 262K bytes (262 blocks) [Memory] Total Memory = 185608 bytes [0.055% of the Shared Pool] ... Fixed Memory = 5848 bytes [0.002% of the Shared Pool] ... Dynamic Memory = 179760 bytes [0.053% of the Shared Pool] ....... Overhead = 146992 bytes ....... Cache Memory = 32K bytes (32 blocks) ........... Unused Memory = 29 blocks ........... Used Memory = 3 blocks ............... Dependencies = 1 blocks (1 count) ............... Results = 2 blocks ................... SQL     = 1 blocks (1 count) ................... Invalid = 1 blocks (1 count) PL/SQL procedure successfully completed. 9 使用客户端查询高速缓存 9.1简介： Oracle 调用接口 （OCI） 客户端结果缓存是客户端进程内的内存区域，用于缓存 OCI 应用程序的 SQL 查询结果集。        此客户端缓存对于每个客户端进程都存在，并由进程内的所有会话共享。Oracle Database 建议对只读或只读表的查询使用客户端结果缓存。 9.2注意：         客户端结果缓存与驻留在 SGA 中的服务器结果缓存不同。启用客户端结果缓存后，查询结果集可以缓存在客户端和/或服务器上。    即使禁用了服务器结果缓存，也可以启用客户端缓存。OCI 驱动程序（如 OCCI、JDBC OCI 驱动程序和 ODP.NET 支持客户端结果缓存。     9.3客户端结果缓存的性能优势包括： 缩短了查询响应时间 当重复执行查询时，应用程序直接从客户端缓存内存中检索结果，从而加快查询响应时间。 更高效地使用数据库资源 服务器往返次数的减少可以节省大量服务器资源（例如，服务器 CPU 和 I/O）的性能。这些资源被释放用于其他任务，从而使服务器更具可扩展性。 降低内存成本 缓存使用可能比服务器内存更便宜的客户端内存。 详细信息可见官方文档的：Client Result Cache 10 官方文档的几个示例： 该提示指示数据库缓存查询块的结果，并在将来的执行中使用缓存的结果。 RESULT_CACHE提示： SELECT   /*+ RESULT_CACHE */ prod_id, SUM(amount_sold) FROM     sales  GROUP BY prod_id ORDER BY prod_id; 该提示指示数据库不要将结果缓存在服务器或客户端结果缓存中。指示数据库不缓存 table 查询的行。/*+ NO_RESULT_CACHE */sales NO_RESULT_CACHE提示： SELECT   /*+ NO_RESULT_CACHE */ prod_id, SUM(amount_sold)  FROM     sales  GROUP BY prod_id ORDER BY prod_id; RESULT_CACHE内联视图中指定的提示: SELECT *  FROM   ( SELECT /*+ RESULT_CACHE */ department_id, manager_id, count(*) count           FROM   hr.employees           GROUP BY department_id, manager_id ) view1  WHERE  department_id = 30; 官方文档中视图使用result cache的样例： WITH summary AS ( SELECT /*+ RESULT_CACHE */ department_id, avg(salary) avg_sal   FROM   hr.employees   GROUP BY department_id ) SELECT d.*, avg_sal FROM   hr.departments d, summary s WHERE  d.department_id = s.department_id; --------------------------------------------------------------------------------------------------- | Id  | Operation             | Name                       | Rows | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT      |                            |   11 |   517 |     7  (29)| 00:00:01 | |*  1 |  HASH JOIN            |                            |   11 |   517 |     7  (29)| 00:00:01 | |   2 |   VIEW                |                            |   11 |   286 |     4  (25)| 00:00:01 | |   3 |    RESULT CACHE       | 8nknvkh64ctmz94a5muf2tyb8r |      |       |            |          | |   4 |     HASH GROUP BY     |                            |   11 |    77 |     4  (25)| 00:00:01 | |   5 |      TABLE ACCESS FULL| EMPLOYEES                  |  107 |   749 |     3   (0)| 00:00:01 | |   6 |   TABLE ACCESS FULL   | DEPARTMENTS                |   27 |   567 |     2   (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- 11 表注释 您可以使用表注释来控制结果缓存。Table 注释仅对整个查询有效，对查询段无效。这些 Comments 的主要好处是避免了在应用程序级别向查询添加结果缓存提示的必要性。 表注释的优先级低于 SQL 提示。因此，您可以通过在查询级别使用RESULT_CACHE提示来覆盖表和会话设置。table 注释的允许值如下： DEFAULT 如果查询中至少有一个表设置为DEFAULT ，则不会在表级别为此查询启用结果缓存，除非 initialization 参数RESULT_CACHE_MODE设置为 FORCE 或 RESULT_CACHE hint 指定。这是默认值。 FORCE 如果查询的所有表都标记为 FORCE，则考虑对查询结果进行缓存。表注释FORCE优先于在会话级别设置的MANUAL的 RESULT_CACHE_MODE 参数值。 示例： CREATE TABLE sales (...) RESULT_CACHE (MODE DEFAULT); SELECT   prod_id, SUM(amount_sold) FROM     sales  GROUP BY prod_id  ORDER BY prod_id; ALTER TABLE sales RESULT_CACHE (MODE FORCE); SELECT   prod_id, SUM(amount_sold) FROM     sales  GROUP BY prod_id  HAVING   prod_id=136; SELECT   /*+ NO_RESULT_CACHE */ *  FROM     sales ORDER BY time_id DESC; 12 自己环境测试： 自己测试： SQL> ALTER TABLE test RESULT_CACHE (MODE FORCE); Table altered. SQL> select * from test where OBJECT_ID='100'; Execution Plan ---------------------------------------------------------- Plan hash value: 1209412814 ----------------------------------------------------------------------------------------------------------- | Id  | Operation      | Name   | Rows  | Bytes | Cost (%CPU)| Time   | ----------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT      |   | 1 |   132 | 2   (0)| 00:00:01 | |   1 |  RESULT CACHE      | 40j3xuf5v6kguav8pxtu4xrysf | 1 |   132 | 2   (0)| 00:00:01 | |   2 |   TABLE ACCESS BY INDEX ROWID| TEST   | 1 |   132 | 2   (0)| 00:00:01 | |*  3 |    INDEX RANGE SCAN      | AA   | 1 |   | 1   (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    3 - access("OBJECT_ID"=100) Result Cache Information (identified by operation id): ------------------------------------------------------    1 - column-count=26; dependencies=(ZC.TEST); attributes=(ordered); parameters=(nls); name="select * from test where OBJECT_ID='100'" Statistics ----------------------------------------------------------   0  recursive calls   0  db block gets   0  consistent gets   0  physical reads   0  redo size        2686  bytes sent via SQL*Net to client 402  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 /*+ NO_RESULT_CACHE */ *  from test where OBJECT_ID='100'; Execution Plan ---------------------------------------------------------- Plan hash value: 1320585271 -------------------------------------------------------------------------------------------- | Id  | Operation     | Name | Rows  | Bytes | Cost (%CPU)| Time    | -------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT     |    | 1 |   132 | 2   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 1 |   132 | 2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN     | AA   | 1 |    | 1   (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("OBJECT_ID"=100) Statistics ----------------------------------------------------------   0  recursive calls   0  db block gets   4  consistent gets   0  physical reads   0  redo size        2686  bytes sent via SQL*Net to client 426  bytes received via SQL*Net from client   2  SQL*Net roundtrips to/from client   0  sorts (memory)   0  sorts (disk)   1  rows processed select /*+ RESULT_CACHE */ * from te  where OBJECT_ID='100'; SQL> select /*+ RESULT_CACHE */ * from te  where OBJECT_ID='100'; Elapsed: 00:00:00.04 Execution Plan ---------------------------------------------------------- Plan hash value: 3298742357 ------------------------------------------------------------------------------------------------- | Id  | Operation    | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   | |     1 |   132 |   397   (1)| 00:00:01 | |   1 |  RESULT CACHE    | 3fxnrdavfkmun243f7kt21fu9c |     1 |   132 |   397   (1)| 00:00:01 | |*  2 |   TABLE ACCESS FULL| TE |     1 |   132 |   397   (1)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - filter("OBJECT_ID"=100) Result Cache Information (identified by operation id): ------------------------------------------------------    1 - column-count=26; dependencies=(ZC.TE); parameters=(nls); name="select /*+ RESULT_CACHE */ * from te  where OBJECT_ID='100'" Statistics ---------------------------------------------------------- 99  recursive calls 133  db block gets        1547  consistent gets        1427  physical reads       26396  redo size        2686  bytes sent via SQL*Net to client 421  bytes received via SQL*Net from client   2  SQL*Net roundtrips to/from client   5  sorts (memory)   0  sorts (disk)   1  rows processed SQL> select /*+ RESULT_CACHE */ * from te  where OBJECT_ID='100'; Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 3298742357 ------------------------------------------------------------------------------------------------- | Id  | Operation    | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   | |     1 |   132 |   397   (1)| 00:00:01 | |   1 |  RESULT CACHE    | 3fxnrdavfkmun243f7kt21fu9c |     1 |   132 |   397   (1)| 00:00:01 | |*  2 |   TABLE ACCESS FULL| TE |     1 |   132 |   397   (1)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - filter("OBJECT_ID"=100) Result Cache Information (identified by operation id): ------------------------------------------------------    1 - column-count=26; dependencies=(ZC.TE); parameters=(nls); name="select /*+ RESULT_CACHE */ * from te  where OBJECT_ID='100'" Statistics ----------------------------------------------------------   0  recursive calls   0  db block gets   0  consistent gets   0  physical reads   0  redo size        2686  bytes sent via SQL*Net to client 421  bytes received via SQL*Net from client   2  SQL*Net roundtrips to/from client   0  sorts (memory)   0  sorts (disk)   1  rows processed   13 怎么找出使用次数比较少，并且结果比较大的结果缓存将它加入黑名单： 13.1. 对于12.2以前的版本确保，补丁17305006已经应用。 13.2. 通过下面的sql查询出哪些需要加入黑名单: select namespace, status, name, count(*) number_of_results, round(avg(scan_count)) avg_scan_cnt, round(max(scan_count)) max_scan_cnt, round(sum(block_count)) tot_blk_cnt from v$result_cache_objects where type = 'Result' group by namespace, name, status order by namespace, tot_blk_cnt; select namespace, status, name, hash, count(*) number_of_results, round(avg(scan_count)) avg_scan_cnt, round(max(scan_count)) max_scan_cnt, round(sum(block_count)) tot_blk_cnt from v$result_cache_objects where type = 'Result' group by namespace, name, hash,status order by namespace, tot_blk_cnt; 找出tot_blk_cnt 非常大，但是 avg_scan_cnt, max_scan_cnt 这两个比较小的行 13.3. 找到对应的 cache_id: select cache_id, name from v$result_cache_objects where type='Result' and name='"APPS"."FND_PROFILE"::11."GET_USER_VALUE"#c1ecffb161e59dc2 #497'; select cache_id, name from v$result_cache_objects where type='Result' and hash='870367482'; 13.4.添加到黑名单: exec dbms_result_cache.Black_List_Add('<cache_id>', TRUE); ====>将 <cache_id> 修改为相应的数值 exec dbms_result_cache.Black_List_Add('3fxnrdavfkmun243f7kt21fu9c', TRUE);  13.5. 运行以下命令清除现有的缓存结果： exec dbms_result_cache.flush();   13.6. 测试结果 SQL>  select /*+ RESULT_CACHE */ * from te  where OBJECT_ID='100'; Elapsed: 00:00:00.02 Execution Plan ---------------------------------------------------------- Plan hash value: 3298742357 ------------------------------------------------------------------------------------------------- | Id  | Operation    | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   | |     1 |   132 |   397   (1)| 00:00:01 | |   1 |  RESULT CACHE    | 3fxnrdavfkmun243f7kt21fu9c |     1 |   132 |   397   (1)| 00:00:01 | |*  2 |   TABLE ACCESS FULL| TE |     1 |   132 |   397   (1)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - filter("OBJECT_ID"=100) Result Cache Information (identified by operation id): ------------------------------------------------------    1 - column-count=26; dependencies=(ZC.TE); parameters=(nls); name="select /*+ RESULT_CACHE */ * from te  where OBJECT_ID='100'" Statistics ----------------------------------------------------------   0  recursive calls   0  db block gets        1430  consistent gets   0  physical reads   0  redo size        2686  bytes sent via SQL*Net to client 422  bytes received via SQL*Net from client   2  SQL*Net roundtrips to/from client   0  sorts (memory)   0  sorts (disk)   1  rows processed 添加后多次执行发现 consistent gets 已经不为0了 14  已知的一些问题 在 Oracle 12.2 和 18c 中，已经实现了 ADG 的 会话连接保持 和 Buffer Cache保持，在 20c 中，Result Cache 在备库上进一步得以保留， 以确保这个细节特性的主备性能通过。 已知的一些问题： Document 567114.1 Init.ora Parameter "RESULT_CACHE_MODE" Reference Note Document 14665745.8 Excessive "Result Cache: RC Latch" latch gets using DBMS_RESULT_CACHE.memory_report Document 12658411.8 LARGE RESULT CACHE CAUSING INSTANCE TO HANGS Document 2256587.1   Performance Issue With Application After 12.2.4 Result Cache Upgrade With Database 12.1.0.2 Active Data Guard (ADG) RESULT_CACHE Query Hint Not Working On Primary After Switchover (Doc ID 2159553.1) Bug 23642282 - results cache is left disabled after switchover (Doc ID 23642282.8)