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 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 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 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 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 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 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 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 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 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
.
[/PHP]
原文出自：http://yangtingkun.itpub.net/post/468/391015