【DB笔试面试611】在Oracle中，什么是结果集缓存？

AiDBA宝典

发布于 2019-09-29 16:07:08

2K0

发布于 2019-09-29 16:07:08

文章被收录于专栏：小麦苗的DB宝专栏

♣

题目部分

在Oracle中，什么是结果集缓存？

♣

答案部分

结果集缓存（Result Cache）是Oracle 11g的新特性，用于存储经常使用的SQL语句和函数的查询结果。当相同语句再次执行的时候，Oracle就不用再次重复执行（包括扫描索引、回表、计算、逻辑读、物理读等操作），而是直接访问内存得到结果。结果集缓存可以将SQL语句查询的结果缓存在内存（SGA的Shared Pool）中，从而显著地改进需要多次执行和查询相同结果的SQL语句的性能。

结果集缓存的优点是可以重用相同的结果集，减少逻辑I/O，从而提高系统性能。结果集缓存最适合的是静态表（例如只读表），即结果集缓存最适合返回同样结果的查询。若SQL语句中包含的对象（例如表）做了UPDATE，INSERT，DELETE或是DDL操作，则相关的所有SQL的缓存结果集就自动失效了。所以，Result Cache只对那些在平时几乎没有任何DML操作的只读表比较有用，可以减轻I/O的压力。

在实际情况中，结果集缓存仅在少数的情况下是有效的。在以下情况中，结果集不会被缓存：

①　查询使用非确定性的函数、序列和临时表的结果集不会被缓存。

②　查询违反了读一致性时结果集将不会被缓存。

③　引用数据字典视图的查询的结果集不会被缓存。

④　查询结果集大于可用缓存结果集可用空间的不会被缓存。

⑤　对依赖对象的任何改变（DML、DDL）都会使整个缓存的结果集变为无效，结果集缓存最适合那些只读或接近只读的表。

⑥　ADG的备库不能使用结果集缓存。

Oracle数据库引擎提供了3种结果集缓存，包括：服务器查询结果集缓存、PL/SQL函数结果集缓存和客户端结果集缓存。

（一）服务器查询结果集缓存

服务器查询结果集缓存由以下一些参数控制：

l RESULT_CACHE_MODE：该参数用来控制结果集缓存的操作模式。AUTO表示优化程序将根据重复的执行操作确定将哪些结果存储在高速缓存中。MANUAL表示只有使用了RESULT_CAHCE提示的查询或对带有RESULT_CACHE属性的表访问的查询才会被缓存，MANUAL为该参数的默认值。FORCE表示所有合适的查询都会被缓存。对于AUTO和FORCE设置，如果语句中包含[NO_]RESULT_CACHE提示，那么该提示优先于参数设置。

l RESULT_CACHE_MAX_SIZE：控制结果集缓存的大小，默认值取决于其它内存设置（MEMORY_TARGET的0.25%或SGA_TARGET的0.5%或SHARED_POOL_SIZE的1%）。当RESULT_CACHE_MAX_SIZE为0的时候，代表不启用结果集缓存。需要注意的是，若将该参数的值从0修改为非0值后，则需要重启数据库才能启用服务器查询结果集缓存特性，反之亦然。

l RESULT_CACHE_MAX_RESULT：单个结果集能够消耗的缓存的最大百分比，比这个值大的结果集将不能被缓存，默认大小为RESULT_CACHE_MAX_SIZE的5%。

l RESULT_CACHE_REMOTE_EXPIRATION：设置远程数据库结果集缓存过期的时间，以分钟为单位，默认值为0，表示不缓存远程数据库结果集。

与结果集缓存相关的一些视图：

l V$RESULT_CACHE_STATISTICS：列出各种缓存设置和内存使用统计数据。

l V$RESULT_CACHE_MEMORY：列出所有的内存块和相应的统计信息。

l V$RESULT_CACHE_OBJECTS：列出所有的对象（缓存的结果和依赖的对象）和它们的属性。

l V$RESULT_CACHE_DEPENDENCY：列出缓存的结果和依赖对象间的依赖详情。

与结果集缓存相关的包是DBMS_RESULT_CACHE。

l STATUS函数：返回值若为DISABLED则表示没有开启结果集缓存；若为ENABLED则表示已经开启并且可以使用结果集缓存；若为BYPASS则表示已经开启结果集缓存但不可以使用结果集缓存，此时可以通过执行“EXEC DBMS_RESULT_CACHE.BYPASS(FALSE);”来使用结果集缓存。执行后如果返回值仍然是BYPASS，那么可能是参数RESULT_CACHE_MAX_SIZE的值为0的原因。STATUS函数返回值若为SYNC则表示结果缓存是可用的，但是目前正与其它RAC节点重新同步。可以使用SQL语句“SELECT DBMS_RESULT_CACHE.STATUS FROM DUAL;”来检查是否开启了结果集缓存机制。

l MEMORY_REPORT存储过程：列出结果缓存内存利用的一个概要（默认）或详细的报表。如下所示：

SYS@orclasm > set serveroutput on
SYS@orclasm > exec 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  = 2816K bytes (2816 blocks)
Maximum Result Size = 140K bytes (140 blocks)
[Memory]
Total Memory = 169328 bytes [0.034% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]
... Dynamic Memory = 163976 bytes [0.033% of the Shared Pool]
....... Overhead = 131208 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 25 blocks
........... Used Memory = 7 blocks
............... Dependencies = 2 blocks (2 count)
............... Results = 5 blocks
................... SQL     = 5 blocks (5 count)

PL/SQL procedure successfully completed.

l FLUSH函数：清空整个结果缓存的内容。

l INVALIDATE函数：使结果缓存中某个特定对象的缓存结果无效。

l INVALIDATE_OBJECT函数：根据缓存ID使某个特定结果缓存无效。

可以在表级别设置RESULT_CACHE，如下所示：

ALTER TABLE HR.EMPLOYEES RESULT_CACHE(MODE FORCE);

下面给出一个服务器查询结果集缓存示例：

LHR@orclasm > 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                      2816K
result_cache_mode                    string                           MANUAL
result_cache_remote_expiration       integer                          0

LHR@orclasm > create table t_rc_20170611_lhr as select * from dba_objects;

Table created.


LHR@orclasm > set autotrace traceonly
LHR@orclasm > select /*+result_cache*/ * from  t_rc_20170611_lhr where object_id=10;


Execution Plan
----------------------------------------------------------
Plan hash value: 3007689452

-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |    13 |  2691 |   312   (1)| 00:00:04 |
|   1 |  RESULT CACHE      | 4ns8fbzpm3a10agkanndg1mqbn |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T_RC_20170611_LHR          |    13 |  2691 |   312   (1)| 00:00:04 |
-------------------------------------------------------------------------------------------------

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

   2 - filter("OBJECT_ID"=10)

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

   1 - column-count=15; dependencies=(LHR.T_RC_20170611_LHR); name="select /*+result_cache*/ * from  t_rc_20170611_lhr where object_id=10"

Note
-----
   - dynamic sampling used for this statement (level=2)


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

LHR@orclasm > select /*+result_cache*/ * from  t_rc_20170611_lhr where object_id=10;


Execution Plan
----------------------------------------------------------
Plan hash value: 3007689452

-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |    13 |  2691 |   312   (1)| 00:00:04 |
|   1 |  RESULT CACHE      | 4ns8fbzpm3a10agkanndg1mqbn |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T_RC_20170611_LHR          |    13 |  2691 |   312   (1)| 00:00:04 |
-------------------------------------------------------------------------------------------------

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

   2 - filter("OBJECT_ID"=10)

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

   1 - column-count=15; dependencies=(LHR.T_RC_20170611_LHR); name="select /*+result_cache*/ * from  t_rc_20170611_lhr where object_id=10"

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       1609  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SYS@orclasm > SELECT d.NAMESPACE,d.DEPEND_COUNT,d.SCN,d.INVALIDATIONS FROM V$RESULT_CACHE_OBJECTS d WHERE d.CACHE_ID='4ns8fbzpm3a10agkanndg1mqbn';

NAMES DEPEND_COUNT        SCN INVALIDATIONS
----- ------------ ---------- -------------
SQL              1   66062134             0
SYS@orclasm > select * from v$result_cache_statistics;

        ID NAME                                     VALUE
---------- ---------------------------------------- -------------------
         1 Block Size (Bytes)                       1024
         2 Block Count Maximum                      2816
         3 Block Count Current                      32
         4 Result Size Maximum (Blocks)             140
         5 Create Count Success                     5
         6 Create Count Failure                     0
         7 Find Count                               8
         8 Invalidation Count                       0
         9 Delete Count Invalid                     0
        10 Delete Count Valid                       0
        11 Hash Chain Length                        1
        12 Find Copy Count                          8

在第二次查询的执行计划中，发现逻辑读已经变成0了，说明Result Cache起作用了。

（二）函数结果集缓存

Oracle数据库用一个单独的缓存区为每一个函数同时保存输入和返回值。这个缓存区被连接到这个数据库实例的所有会话共享。每当函数被调用时，数据库就会检查是否已经缓存了相同的输入值。如果是，那么函数就不用重新执行了，而是把缓存中的值简单返回即可。每当发现要修改的是缓存所依赖的表，数据库就会自动把缓存失效。

有两种函数缓存机制，分别是确定性函数缓存和函数结果集缓存。对于一个函数，如果有相同的IN和IN OUT参数，且函数的返回结果也相同，那么这个函数就是确定性的（DETERMINISTIC）。Oracle通过关键字DETERMINISTIC来表明一个函数是确定性的，确定性函数可以用于创建基于函数的索引。函数结果集缓存指的是Oracle通过关键字RESULT_CACHE对函数返回的结果进行缓存，缓存结果可以被所有会话共享。

下面给出一个函数结果集缓存的示例：

准备表：

CREATE TABLE T_FRC_20170611_LHR AS SELECT * FROM DBA_OBJECTS;
CREATE OR REPLACE FUNCTION FUN_OBJECTNAME_LHR(P_ID IN NUMBER) RETURN VARCHAR2 RESULT_CACHE AS
  V_OBJECT VARCHAR2(200);
BEGIN
  SELECT OBJECT_NAME INTO V_OBJECT FROM T_FRC_20170611_LHR WHERE OBJECT_ID = P_ID;
  RETURN V_OBJECT;
END FUN_OBJECTNAME_LHR;

执行查询：

LHR@orclasm > SELECT FUN_OBJECTNAME_LHR(10) FROM DUAL;


Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


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

LHR@orclasm > SELECT FUN_OBJECTNAME_LHR(10) FROM DUAL;


Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        545  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SYS@orclasm > SELECT d.NAMESPACE,d.DEPEND_COUNT,d.SCN,d.INVALIDATIONS FROM V$RESULT_CACHE_OBJECTS d WHERE D.NAMESPACE='PLSQL';

NAMES DEPEND_COUNT        SCN INVALIDATIONS
----- ------------ ---------- -------------
PLSQL            2   66067285             0

LHR@orclasm > DELETE FROM T_FRC_20170611_LHR WHERE ROWNUM<=1;

1 row deleted.
LHR@orclasm > COMMIT;

Commit complete.

LHR@orclasm >

（三）客户端结果集缓存

初始化参数CLIENT_RESULT_CACHE_SIZE表示所有客户端的总缓存大小，有关客户端结果缓存本书不再详解，读者可以查阅相关的官方文档来学习。

& 说明：

有关结果集缓存的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2140589/