Oracle11新特性——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所带来的性能提升：
[PHP]
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
.
[/PHP]
由于第二次执行SQL语句的时候使用了RESULT_CACHE提示，Oracle将这个查询的结果集记录在共享内存中，当第三次查询执行的时候，不需要进行任何的处理，而直接读取结果集，几乎没有任何的消耗，逻辑读为0，运行时间也几乎为0。
下面简单说明一下RESULT_CACHE_MODE的三种模式下，缓存结果集是如何工作的。
当参数值设置为MANUAL时，只有通过HINT明确提示的SQL才会读取缓存结果集。如果不加提示，那么Oracle不会利用已经缓存的结果。
[PHP]
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 |
-------------------------------------------------------------------------------------------
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
统计信息
----------------------------------------------------------
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
.
[/PHP]
当设置为MANUAL时，只有使用HINT的情况下，Oracle才会利用缓存结果集。而对于AUTO模式，Oracle如果发现缓冲结果集已经存在，那么就会使用。但是如果缓冲结果集不存在，Oracle并不会自动进行缓冲。只有使用HINT的情况下，Oracle才会将执行的结果集缓存。
[PHP]
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
.
[/PHP]
对比上面两种情况，由于第一个SQL的缓存结果集已经存在，Oracle采用了缓存结果集，而对于第二个SQL，Oracle是不会自动使用缓存结果集的。
[PHP]
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
.
[/PHP]
当使用HINT后，结果集被缓存，随后的相同的查询就会使用缓存的结果集。
对于FORCE参数，顾名思义，就是会对所有的SQL进行缓存，除非明确使用NO_RESULT_CACHE提示：
[PHP]
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
| 上一篇介绍了SQL缓存结果集，这里简单的介绍一下缓存结果集的特点。为了测试方便，将RESULT_CACHE_MODE设置为AUTO模式。 [PHP] SQL> ALTER SESSION SET RESULT_CACHE_MODE = AUTO; 会话已更改。 SQL> SET AUTOT ON SQL> SELECT /*+ RESULT_CACHE */ COUNT(*) FROM T; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- 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 | 68324 | 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" 统计信息 ---------------------------------------------------------- 56 recursive calls 0 db block gets 1019 consistent gets 1007 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 2 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SELECT COUNT(*) FROM T; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- 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 | 68324 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------ Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT COU 统计信息 ---------------------------------------------------------- 1 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 . [/PHP] 设置参数为AUTO后，第二次执行SQL已经使用了RESULT CACHE。下面来看RESULT CACHE的第一个特点，由于RESULT CACHE所使用的内存来自SGA，因此RESULT CACHE的结果对于所有的SESSION都是可见的。 下面启动第二个SQLPLUS SESSION： [PHP] SQL> SET SQLPROMPT 'SQL2> ' SQL2> SET AUTOT ON SQL2> ALTER SESSION SET RESULT_CACHE_MODE = AUTO; 会话已更改。 SQL2> SELECT COUNT(*) FROM T; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- 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 | 68324 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------ Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT COU 统计信息 ---------------------------------------------------------- 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 . [/PHP] 由于SELECT COUNT(*) FROM T的结果已经保存在SGA中，其他的SESSION也是可以使用这个缓存结果集的。 RESULT CACHE是满足一致性读的，而且当缓存表数据发生变化的时候，Oracle会自动INVALIDATE缓存结果集： [PHP] SQL> DELETE T WHERE ROWNUM = 1; 已删除 1 行。 SQL> SELECT COUNT(*) FROM T; COUNT(*) ---------- 68323 执行计划 ---------------------------------------------------------- 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 | 68324 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------ Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT COU 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 1011 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 . [/PHP] 由于进行了修改，当前会话查询将无法在利用RESULT CACHE，但是这个时候如果从第二个会话进行查询： [PHP] SQL2> SELECT COUNT(*) FROM T; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- 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 | 68324 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------ Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT COU 统计信息 ---------------------------------------------------------- 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 . [/PHP] 由于会话1没有提交，修改对于会话2来说是不可见的。因此会话2仍然可以使用缓存结果集。 [PHP] SQL> ROLLBACK; 回退已完成。 SQL> SELECT COUNT(*) FROM T; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- 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 | 68324 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------ Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT COU 统计信息 ---------------------------------------------------------- 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 . [/PHP] 将会话1的修改回滚，则会话1又可以利用RESULT CACHE。 [PHP] SQL> CREATE INDEX IND_T_OBJECT_ID ON T(OBJECT_ID); 索引已创建。 SQL> SELECT COUNT(*) FROM T; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- 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 | 68324 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------ Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT COU 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 1011 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> ALTER TABLE T MODIFY OBJECT_ID NOT NULL; 表已更改。 SQL> SELECT COUNT(*) FROM T; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- Plan hash value: 3841213438 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 44 (3)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| IND_T_OBJECT_ID | 68324 | 44 (3)| 00:00:01 | --------------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 204 recursive calls 0 db block gets 187 consistent gets 151 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 6 sorts (memory) 0 sorts (disk) 1 rows processed . [/PHP] 由于进行DDL操作，Oracle自动INVALIDATE了RESULT CACHE结果集，可以通过V$RESULT_CACHE_OBJECTS观察到： [PHP] SQL> SELECT ID, TYPE, STATUS FROM V$RESULT_CACHE_OBJECTS 2 WHERE NAME = 'SELECT /*+ RESULT_CACHE */ COUNT(*) FROM T'; ID TYPE STATUS ---------- ---------- --------- 1 Result Invalid . [/PHP] 最后看一下Oracle RESULT CACHE的另外一个特点，是否使用RESULT_CACHE与Oracle的执行计划无关： [PHP] SQL> SELECT /*+ RESULT_CACHE */ COUNT(*) FROM T; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- Plan hash value: 3841213438 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 44 (3)| 00:00:01 | | 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | | | 2 | SORT AGGREGATE | | 1 | | | | 3 | INDEX FAST FULL SCAN| IND_T_OBJECT_ID | 68324 | 44 (3)| 00:00:01 | --------------------------------------------------------------------------------------------- Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT /*+ RESULT_CAC T" 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 158 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 COUNT(*) FROM T; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- Plan hash value: 3841213438 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 44 (3)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| IND_T_OBJECT_ID | 68324 | 44 (3)| 00:00:01 | --------------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 158 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 /*+ FULL(T) */ COUNT(*) FROM T; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- 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 | 68324 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------ Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT /*+ FROM T" 统计信息 ---------------------------------------------------------- 1 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> SELECT /*+ INDEX(T) */ COUNT(*) FROM T; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- Plan hash value: 2391220499 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 153 (1)| 00:00:02 | | 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | | | 2 | SORT AGGREGATE | | 1 | | | | 3 | INDEX FULL SCAN| IND_T_OBJECT_ID | 68324 | 153 (1)| 00:00:02 | ---------------------------------------------------------------------------------------- Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT /*+ ) FROM T" 统计信息 ---------------------------------------------------------- 1 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> SELECT COUNT(*) FROM T A; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- Plan hash value: 3841213438 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 44 (3)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| IND_T_OBJECT_ID | 68324 | 44 (3)| 00:00:01 | --------------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 158 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 . [/PHP] 从最后几个查询可以看出，RESULT CACHE忽略SQL语句的执行计划，即使执行计划发生了变化，只有最终获取的结果是一样的，Oracle仍然会使用RESULT CACHE。 注意当执行计划不同导致结果不同时，Oracle使用RESULT CACHE可能会得到不正确的结果。 |
上一篇介绍了RESULT CACHE的一些特点，其中最后提到了使用RESULT CACHE可能会导致查询结果不正确。下面看一个简单的例子：
[PHP]
SQL> SET AUTOT ON
SQL> ALTER SESSION SET RESULT_CACHE_MODE = MANUAL;
会话已更改。
SQL> SELECT /*+ FULL(T) */ OBJECT_NAME FROM T WHERE ROWNUM = 1;
OBJECT_NAME
------------------------------
ICOL$
执行计划
----------------------------------------------------------
Plan hash value: 508354683
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| T | 1 | 25 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
342 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 /*+ INDEX(T) */ OBJECT_NAME FROM T WHERE ROWNUM = 1;
OBJECT_NAME
------------------------------
C_OBJ#
执行计划
----------------------------------------------------------
Plan hash value: 370698254
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 25 | 3 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | IND_T_OBJECT_ID | 68324 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
343 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
.
[/PHP]
先将RESULT_CACHE_MODE设置为MANUAL，观察全表扫描和索引全扫描两种查询的结果。由于执行计划的不同，虽然语句本身是一样的，但是返回结果中记录的顺序是不同的。在这个例子中，如果使用全表扫描会返回ICOL$，而采用索引扫描，返回结果为C_OBJ#。
下面看看启用缓存结果集的情况：
[PHP]
SQL> SELECT /*+ RESULT_CACHE FULL(T) */ OBJECT_NAME FROM T WHERE ROWNUM = 1;
OBJECT_NAME
------------------------------
ICOL$
执行计划
----------------------------------------------------------
Plan hash value: 508354683
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | 668yp49bw4mfnak70m2pww541d | | | | |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS FULL| T | 1 | 25 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(YANGTK.T); name="SELECT /*+ RESULT_CACHE FULL(T) */ OBJECT_NAME
= 1"
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
342 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 INDEX(T) */ OBJECT_NAME FROM T WHERE ROWNUM = 1;
OBJECT_NAME
------------------------------
ICOL$
执行计划
----------------------------------------------------------
Plan hash value: 370698254
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 |
| 1 | RESULT CACHE | 668yp49bw4mfnak70m2pww541d | | | | |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 1 | 25 | 3 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | IND_T_OBJECT_ID | 68324 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(YANGTK.T); name="SELECT /*+ RESULT_CACHE INDEX(T) */ OBJECT_NAM
M = 1"
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
342 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
.
[/PHP]
由于第二个查询指定使用了RESULT CACHE，虽然Oracle显示的执行计划是索引扫描，但是利用的缓存结果集是全表扫描生成的，所以第二个查询得到的结果也和第一个查询一样。
对于以前的系统中，试图利用索引按照顺序返回结果集的特点来避免排序的写法，在利用RESULT CACHE时，很可能会出现问题。
不知道Oracle是否会认为这是一个bug，是否以后会有所改进。
RESULT CACHE不仅对查询的最终结果生效，查询的中间结果也是可以使用RESULT CACHE的。
在进行测试之前，先将前面的RESULT CACHE清除掉。Oracle提供了一个DBMS_RESULT_CACHE包来管理RESULT CACHE：
[PHP]
SQL> SET AUTOT OFF
SQL> SELECT COUNT(*) FROM V$RESULT_CACHE_OBJECTS;
COUNT(*)
----------
5
SQL> EXEC DBMS_RESULT_CACHE.FLUSH
PL/SQL 过程已成功完成。
SQL> SELECT COUNT(*) FROM V$RESULT_CACHE_OBJECTS;
COUNT(*)
----------
0
.
[/PHP]
这个包还包括其他一些功能，比如BYPASS可以设置当前实例绕过RESULT CACHE机制。INVALIDATE可以设置某个对象关联的所有RESULT CACHE失效。STATUS返回结果集状态。
下面看看Oracle如何对查询的中间结果使用RESULT CACHE：
[PHP]
SQL> SET AUTOT ON
SQL> CREATE TABLE T1 AS SELECT * FROM DBA_SEGMENTS;
表已创建。
SQL> SELECT /*+ RESULT_CACHE */ OWNER, COUNT(*) NUM FROM T GROUP BY OWNER;
OWNER NUM
------------------------------ ----------
WKSYS 371
MDSYS 1281
YANGTK 13
.
.
.
WMSYS 315
SI_INFORMTN_SCHEMA 8
已选择22行。
执行计划
----------------------------------------------------------
Plan hash value: 47235625
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22 | 132 | 287 (3)| 00:00:04 |
| 1 | RESULT CACHE | g02167xk9g68s52fnfssvx00tr | | | | |
| 2 | HASH GROUP BY | | 22 | 132 | 287 (3)| 00:00:04 |
| 3 | TABLE ACCESS FULL| T | 68324 | 400K| 282 (1)| 00:00:04 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(YANGTK.T); parameters=(nls); name="SELECT /*+ RESULT_CACHE */ O
ROM T GROUP BY OWNER"
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1011 consistent gets
0 physical reads
0 redo size
826 bytes sent via SQL*Net to client
388 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22 rows processed
SQL> SELECT /*+ RESULT_CACHE */ OWNER, SUM(BYTES)/1024 K FROM T1 GROUP BY OWNER;
OWNER K
------------------------------ ----------
WKSYS 7616
MDSYS 47744
YANGTK 11392
.
.
.
SYS 854656
WMSYS 7296
已选择18行。
执行计划
----------------------------------------------------------
Plan hash value: 136660032
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7146 | 209K| 36 (6)| 00:00:01 |
| 1 | RESULT CACHE | f072b82hjsvsm9yvr91n766xwp | | | | |
| 2 | HASH GROUP BY | | 7146 | 209K| 36 (6)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 7146 | 209K| 34 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(YANGTK.T1); parameters=(nls); name="SELECT /*+ RESULT_CACHE */
24 K FROM T1 GROUP BY OWNER"
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
68 recursive calls
0 db block gets
183 consistent gets
109 physical reads
0 redo size
773 bytes sent via SQL*Net to client
388 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
18 rows processed
SQL> SELECT A.OWNER, NUM, K FROM
2 (SELECT /*+ RESULT_CACHE */ OWNER, COUNT(*) NUM FROM T GROUP BY OWNER) A,
3 (SELECT /*+ RESULT_CACHE */ OWNER, SUM(BYTES)/1024 K FROM T1 GROUP BY OWNER) B
4 WHERE A.OWNER = B.OWNER;
OWNER NUM K
------------------------------ ---------- ----------
WKSYS 371 7616
MDSYS 1281 47744
YANGTK 13 11392
.
.
.
SYS 29743 854656
WMSYS 315 7296
已选择18行。
执行计划
----------------------------------------------------------
Plan hash value: 3810504953
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7146 | 418K| 323 (3)| 00:00:04 |
|* 1 | HASH JOIN | | 7146 | 418K| 323 (3)| 00:00:04 |
| 2 | VIEW | | 22 | 660 | 287 (3)| 00:00:04 |
| 3 | RESULT CACHE | g02167xk9g68s52fnfssvx00tr | | | | |
| 4 | HASH GROUP BY | | 22 | 132 | 287 (3)| 00:00:04 |
| 5 | TABLE ACCESS FULL| T | 68324 | 400K| 282 (1)| 00:00:04 |
| 6 | VIEW | | 7146 | 209K| 36 (6)| 00:00:01 |
| 7 | RESULT CACHE | f072b82hjsvsm9yvr91n766xwp | | | | |
| 8 | HASH GROUP BY | | 7146 | 209K| 36 (6)| 00:00:01 |
| 9 | TABLE ACCESS FULL| T1 | 7146 | 209K| 34 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OWNER"="B"."OWNER"
Result Cache Information (identified by operation id):
------------------------------------------------------
3 - column-count=2; dependencies=(YANGTK.T); parameters=(nls); name="SELECT /*+ RESULT_CACHE */ O
ROM T GROUP BY OWNER"
7 - column-count=2; dependencies=(YANGTK.T1); parameters=(nls); name="SELECT /*+ RESULT_CACHE */
24 K FROM T1 GROUP BY OWNER"
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
127 recursive calls
8 db block gets
194 consistent gets
0 physical reads
0 redo size
889 bytes sent via SQL*Net to client
388 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
18 rows processed
.
[/PHP]
需要注意，对于SQL中间结果使用RESULT CACHE必须使用RESULT_CACHE提示进行强制。
RESULT CACHE功能对于下列情况是无效的：系统表和临时表；序列的NEXTVAL和CURRVAL伪列；SYSDATE、SYSTIMESTAMP等函数；所有非确定性PL/SQL函数。
posted on 2014-03-09 00:20 pengdaijun 阅读(1062) 评论(0) 编辑 收藏 举报
