【IT168 技术文档】这篇文章介绍11g新增功能缓存结果集中SQL语句的缓存。SQL语句的缓存结果集功能是Oracle11g中最吸引我的一个新功能。
DB_BUFFER只能缓存访问过的BLOCK，部分解决了物理读的问题，查询仍然需要大量的逻辑读。物化视图提供的是查询重写的功能，对于大部分的情况，只是简化了SQL的复杂度，即使是完全满足查询的条件，物化视图的扫描也是不可避免的。而缓存结果集——RESULT CACHE则截然不同，它缓存的是查询的结果。不在需要大量的逻辑读，不在需要任何的复杂计算，而是直接将已经缓存的结果返回。
Oracle新增了两个HINT，RESULT_CACHE和NO_RESULT_CACHE。通过这两个提示，可以明确的指出下面的语句是否进行RESULT CACHE。Oracle还增加了几个初始化参数来管理RESULT CACHE功能，如：RESULT_CACHE_MODE、RESULT_CACHE_MAX_SIZE等。RESULT_CACHE_MAX_SIZE指明SGA中RESULT CACHE功能可以使用的最大的内存容量。如果这个参数设置为0，则关闭RESULT CACHE功能。RESULT_CACHE_MODE参数设置Oracle如何使用RESULT CACHE，该参数有三个值：MANUAL、AUTO、FORCE。后面会通过几个例子来说明三种情况的区别。
Oracle提供了DBMS_RESULT_CACHE包来管理和维护RESULT CACHE。Oracle还新增了几个关于RESULT CACHE的系统视图，用户可以看到和RESULT CACHE相关的各种信息，视图包括：V$RESULT_CACHE_DEPENDENCY、V$RESULT_CACHE_MEMORY、V$RESULT_CACHE_OBJECTS和V$RESULT_CACHE_STATISTICS等。
首先先看一下RESULT_CACHE所带来的性能提升：
SQL> create table t as select * from dba_objects; 表已创建。 SQL> set autot on SQL> set timing on SQL> select count(*) from t; COUNT(*) ---------- 68324 已用时间: 00: 00: 00.12 执行计划 ---------------------------------------------------------- Plan hash value: 2966233522 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 282 (1)| 00:00:04 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T | 52976 | 282 (1)| 00:00:04 | ------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 28 recursive calls 0 db block gets 1066 consistent gets 1006 physical reads 0 redo size 338 bytes sent via SQL*Net to client 377 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 /*+ result_cache */ count(*) from t; COUNT(*) ---------- 68324 已用时间: 00: 00: 00.21 执行计划 ---------------------------------------------------------- Plan hash value: 2966233522 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 282 (1)| 00:00:04 | | 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | | | 2 | SORT AGGREGATE | | 1 | | | | 3 | TABLE ACCESS FULL| T | 52976 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------ Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; dependencies=(YANGTK.T); attributes=(single-row); name="select /*+ result_cac t" Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 4 recursive calls 0 db block gets 1064 consistent gets 0 physical reads 0 redo size 338 bytes sent via SQL*Net to client 377 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 /*+ result_cache */ count(*) from t; COUNT(*) ---------- 68324 已用时间: 00: 00: 00.00 执行计划 ---------------------------------------------------------- Plan hash value: 2966233522 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 282 (1)| 00:00:04 | | 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | | | 2 | SORT AGGREGATE | | 1 | | | | 3 | TABLE ACCESS FULL| T | 52976 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------ Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; dependencies=(YANGTK.T); attributes=(single-row); name="select /*+ result_cac t" Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 338 bytes sent via SQL*Net to client 377 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提示，Oracle将这个查询的结果集记录在共享内存中，当第三次查询执行的时候，不需要进行任何的处理，而直接读取结果集，几乎没有任何的消耗，逻辑读为0，运行时间也几乎为0。
下面简单说明一下RESULT_CACHE_MODE的三种模式下，缓存结果集是如何工作的。
当参数值设置为MANUAL时，只有通过HINT明确提示的SQL才会读取缓存结果集。如果不加提示，那么Oracle不会利用已经缓存的结果。
SQL> alter session set result_cache_mode = manual; 会话已更改。 SQL> select /*+ result_cache */ object_name from t where object_name = 'DUAL'; OBJECT_NAME ----------------------------------------------------------- DUAL DUAL 执行计划 ---------------------------------------------------------- Plan hash value: 1601196873 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 | | 1 | RESULT CACHE |fhp10hrt50095fufmw3s75txcx| | | | | |* 2 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_NAME"='DUAL') Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; dependencies=(YANGTK.T); parameters=(nls); name="select /*+ result_cache */ o ere object_name = 'DUAL'" Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 28 recursive calls 0 db block gets 1067 consistent gets 0 physical reads 0 redo size 366 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed SQL> select /*+ result_cache */ object_name from t where object_name = 'DUAL'; OBJECT_NAME ------------------------------------------------------------ DUAL DUAL 执行计划 ---------------------------------------------------------- Plan hash value: 1601196873 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 | | 1 | RESULT CACHE | fhp10hrt50095fufmw3s75txcx| | | | | |* 2 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------- redicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_NAME"='DUAL') Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; dependencies=(YANGTK.T); parameters=(nls); name="select /*+ result_cache */ o ere object_name = 'DUAL'" Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 366 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed SQL> select object_name from t where object_name = 'DUAL'; OBJECT_NAME ----------------------------------------------------------- DUAL DUAL 执行计划 ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_NAME"='DUAL') Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 4 recursive calls 0 db block gets 1065 consistent gets 0 physical reads 0 redo size 366 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed
当设置为MANUAL时，只有使用HINT的情况下，Oracle才会利用缓存结果集。而对于AUTO模式，Oracle如果发现缓冲结果集已经存在，那么就会使用。但是如果缓冲结果集不存在，Oracle并不会自动进行缓冲。只有使用HINT的情况下，Oracle才会将执行的结果集缓存。
SQL> alter session set result_cache_mode = auto; 会话已更改。 SQL> select object_name from t where object_name = 'DUAL'; OBJECT_NAME ------------------------------ DUAL DUAL 执行计划 ---------------------------------------------------------- Plan hash value: 1601196873 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 | | 1 | RESULT CACHE |fhp10hrt50095fufmw3s75txcx| | | | | |* 2 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_NAME"='DUAL') Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); parameters=(nls); name="select object_nam t_name = 'DUAL'" Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 5 recursive calls 0 db block gets 53 consistent gets 0 physical reads 0 redo size 366 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed SQL> select object_name from t where object_name = 'DUAL'; OBJECT_NAME ------------------------------ DUAL DUAL 执行计划 ---------------------------------------------------------- Plan hash value: 1601196873 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 | | 1 | RESULT CACHE | fhp10hrt50095fufmw3s75txcx | | | | | |* 2 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_NAME"='DUAL') Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); parameters=(nls); name="select object_nam t_name = 'DUAL'" Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 366 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed SQL> select object_name from t where object_name = 'DBA_TABLES'; OBJECT_NAME ------------------------------ DBA_TABLES DBA_TABLES 执行计划 ---------------------------------------------------------- Plan hash value: 1601196873 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 | | 1 | RESULT CACHE | 14buvcmfum6h9622vpkxm7sj2w| | | | | |* 2 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_NAME"='DBA_TABLES') Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); parameters=(nls); name="select object_nam t_name = 'DBA_TABLES'" Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 4 recursive calls 0 db block gets 1065 consistent gets 0 physical reads 0 redo size 372 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed SQL> select object_name from t where object_name = 'DBA_TABLES'; OBJECT_NAME ------------------------------ DBA_TABLES DBA_TABLES 执行计划 ---------------------------------------------------------- Plan hash value: 1601196873 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 | | 1 | RESULT CACHE | 14buvcmfum6h9622vpkxm7sj2w | | | | | |* 2 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_NAME"='DBA_TABLES') Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); parameters=(nls); name="select object_nam t_name = 'DBA_TABLES'" Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 1012 consistent gets 0 physical reads 0 redo size 372 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed
对比上面两种情况，由于第一个SQL的缓存结果集已经存在，Oracle采用了缓存结果集，而对于第二个SQL，Oracle是不会自动使用缓存结果集的。
SQL> select /*+ result_cache */ object_name from t where object_name = 'DBA_TABLES'; OBJECT_NAME ------------------------------ DBA_TABLES DBA_TABLES 执行计划 ---------------------------------------------------------- Plan hash value: 1601196873 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 | | 1 | RESULT CACHE | 14buvcmfum6h9622vpkxm7sj2w | | | | | |* 2 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_NAME"='DBA_TABLES') Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; dependencies=(YANGTK.T); parameters=(nls); name="select /*+ result_cache */ o ere object_name = 'DBA_TABLES'" Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 4 recursive calls 0 db block gets 1065 consistent gets 0 physical reads 0 redo size 372 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed SQL> select object_name from t where object_name = 'DBA_TABLES'; OBJECT_NAME ------------------------------ DBA_TABLES DBA_TABLES 执行计划 ---------------------------------------------------------- Plan hash value: 1601196873 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 | | 1 | RESULT CACHE | 14buvcmfum6h9622vpkxm7sj2w | | | | | |* 2 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_NAME"='DBA_TABLES') Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); parameters=(nls); name="select object_nam t_name = 'DBA_TABLES'" Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 372 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed
当使用HINT后，结果集被缓存，随后的相同的查询就会使用缓存的结果集。
对于FORCE参数，顾名思义，就是会对所有的SQL进行缓存，除非明确使用NO_RESULT_CACHE提示：
SQL> alter session set result_cache_mode = force; 会话已更改。 SQL> select object_name from t where object_name = 'DBA_VIEWS'; OBJECT_NAME ------------------------------ DBA_VIEWS DBA_VIEWS 执行计划 ---------------------------------------------------------- Plan hash value: 1601196873 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 | | 1 | RESULT CACHE | bdkgz2jxpb7tc8pkwn478qt3p0 | | | | | |* 2 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_NAME"='DBA_VIEWS') Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; dependencies=(YANGTK.T); parameters=(nls); name="select object_name from t wh BA_VIEWS'" Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 5 recursive calls 0 db block gets 1065 consistent gets 0 physical reads 0 redo size 371 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed SQL> select object_name from t where object_name = 'DBA_VIEWS'; OBJECT_NAME ------------------------------ DBA_VIEWS DBA_VIEWS 执行计划 ---------------------------------------------------------- Plan hash value: 1601196873 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 | | 1 | RESULT CACHE | bdkgz2jxpb7tc8pkwn478qt3p0 | | | | | |* 2 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_NAME"='DBA_VIEWS') Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; dependencies=(YANGTK.T); parameters=(nls); name="select object_name from t wh BA_VIEWS'" Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 371 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed SQL> select /*+ no_result_cache */ object_name from t where object_name = 'DBA_VIEWS'; OBJECT_NAME ------------------------------ DBA_VIEWS DBA_VIEWS 执行计划 ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 726 | 282 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 11 | 726 | 282 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_NAME"='DBA_VIEWS') Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 4 recursive calls 0 db block gets 1065 consistent gets 0 physical reads 0 redo size 371 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed
