Oracle 11g New Features - Result Cache

當一支SQL指令由客戶端發送到伺服器端時，Server Process首先必須先將其解析為執行計畫，將執行計畫存放在Shared Pool後，才使用該執行計畫進行SQL操作，這樣當下次使用者執行相同的SQL指令時，便可以直接由Library Cache中取得執行計畫，而不需要再次解析一次。這樣在重複地執行相同的SQL指令的環境下，可以減少硬解析(Hard Parse)的次數，這樣對整體Oracle Server效能有很大的提昇。
同時當Server Process執行該執行計畫時，利用Buffer Cache將由資料檔讀入的資料區塊(data block)快取起來，往後如果有需要相同的資料區塊，便可以直接由Buffer Cache中取得，利用記憶體的存取速度遠快過於磁碟機的優勢，將原本的Disk I/O轉換成Memory I/O。因此即便是I/O的總數量不變，但是整體I/O的時間卻可以大幅下降，如此便可以減少SQL指令的反應時間。
Oracle11g更進一步將SQL指令的結果，直接快取起來，存放在Result Cache。因此當使用者執行相同的SQL指令時，不需再次執行該SQL指令，直接將存放在Result Cache中的結果集，直接回傳給使用者即可，如此可以得到更短的反應時間。同時Oracle11g會自動偵測結果集的正確性，如果相關資料表的內容有變動，將會將該結果集設定為失效，下次執行SQL指令時，會重新執行SQL指令產生最新的結果集並將其快取在Result Cache中，而Result Cache存放在Shared Pool中。

Result Cache的相關參數
SQL> show parameter result_cache

NAME TYPE VALUE
------------------------------- ------------- -------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5 --最多可以快取幾個Result
result_cache_max_size big integer 1536K --Result Cache所佔的最大值
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0

其中result_cache_mode決定是否自動使用RESULT CACHE功能。
如果result_cache_mode設定為MANUAL，查詢指令必須加上RESULT_CACHE的HINT才會使用RESULT CACHE。
反之result_cache_mode設定為FORCE，則會自動使用RESULT CACHE，除非加上NO_RESULT_CACHE才會不使用RESULT CACHE。
NOTE︰有些人發現可以將result_cache_mode設定為auto，並顧名思義地認為可以讓Oracle Server自動判斷是否要使用RESULT CACHE，但是根據Oracle內部的研發人員說明，AUTO現在不會有任何效果。

建立相關測試環境
建立一個測試表格
Connected.
SQL> create user frank
2 identified by oracle
3 default tablespace users
4 quota unlimited on users;

User created.

SQL> grant connect,resource to frank;

Grant succeeded.

SQL> create table frank.big1 as select * from dba_objects;

Table created.

SQL> insert into frank.big1 select * from frank.big1;

11654 rows created.

SQL> /

23308 rows created.

SQL> /

46616 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from frank.big1;

COUNT(*)
----------
93232

SQL> execute dbms_stats.gather_table_stats('FRANK','BIG1');

PL/SQL procedure successfully completed.


Result Cache的功能示範
SQL> show parameter result_cache_mode

NAME TYPE VALUE
----------------------------- --------- ------------------------------
result_cache_mode string MANUAL

SQL> set autotrace traceonly

/*底下的SQL指令將不會產生Result Cache，因為result_cache_mode為MANUAL*/
SQL> select owner,count(*) from frank.big1 group by owner order by count(*);

Execution Plan
----------------------------------------------------------
Plan hash value: 2655342821

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 45 | 346 (3)| 00:00:05 |
| 1 | SORT ORDER BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 2 | HASH GROUP BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 3 | TABLE ACCESS FULL| BIG1 | 93232 | 455K| 338 (1)| 00:00:05 |
----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1241 consistent gets /*需要讀取1241個data blocks，才能完成*/
0 physical reads
0 redo size
638 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed

/*使用result_cahce的HINT，要求將此SQL的結果快取起來*/
SQL> select /*+ result_cache */ owner,count(*) from frank.big1 group by owner order by count(*);

Execution Plan
----------------------------------------------------------
Plan hash value: 2655342821

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 45 | 346 (3)| 00:00:05 |
| 1 | RESULT CACHE | 86cxjfsndphbsd664abz33z4bt | | | | |
| 2 | SORT ORDER BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 3 | HASH GROUP BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 4 | TABLE ACCESS FULL| BIG1 | 93232 | 455K| 338 (1)| 00:00:05 |
-----------------------------------------------------------------------------

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

1 - column-count=2; dependencies=(FRANK.BIG1); parameters=(nls); name="select /*+ result_cache */ owner,cou
nt(*) from frank.big1 group by owner order by count(*)"

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1241 consistent gets
0 physical reads
0 redo size
638 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed

/*以下的SQL使用result_cache的HINT，可以由Result Cache取得結果*/
SQL> select /*+ result_cache */ owner,count(*) from frank.big1 group by owner order by count(*);

Execution Plan
----------------------------------------------------------
Plan hash value: 2655342821

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 9 | 45 | 346 (3)| 00:00:05 |
| 1 | RESULT CACHE | 86cxjfsndphbsd664abz33z4bt | | | | |
| 2 | SORT ORDER BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 3 | HASH GROUP BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 4 | TABLE ACCESS FULL| BIG1 | 93232 | 455K| 338 (1)| 00:00:05 |
-----------------------------------------------------------------------------

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

1 - column-count=2; dependencies=(FRANK.BIG1); parameters=(nls); name="select /*+ result_cache */ owner,cou
nt(*) from frank.big1 group by owner order by count(*)
"

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets /*沒有任何data block被讀取*/
0 physical reads
0 redo size
638 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

/*查詢目前已被快取的結果集*/
SQL> select name,id,status from v$result_cache_objects;

NAME
-----------------------------------------------------------------------------
ID STATUS
---------- ---------
FRANK.BIG1
1 Published

select /*+ result_cache */ owner,count(*) from frank.big1 group by owner order by count(*)
2 Published

SELECT DECODE('A','A','1','2') FROM DUAL
0 Published

/*將Result Cache的內容請空*/
SQL> execute dbms_result_cache.flush;

PL/SQL procedure successfully completed.

SQL> select name,id,status from v$result_cache_objects;

no rows selected

SQL> select owner,count(*) from frank.big1 group by owner order by count(*);

OWNER COUNT(*)
------------------------------ ----------
FRANK 8
TESTUSER 16
TSMSYS 24
ORACLE_OCM 64
OUTLN 72
DBSNMP 440
SYSTEM 4064
PUBLIC 23376
SYS 65168

9 rows selected.

SQL> select name,id,status from v$result_cache_objects;

no rows selected

SQL> alter session set result_cache_mode=FORCE;

Session altered.

/*自動將執行結果快取到Result Cache中*/
SQL> select owner,count(*) from frank.big1 group by owner order by count(*);

OWNER COUNT(*)
------------------------------ ----------
FRANK 8
TESTUSER 16
TSMSYS 24
ORACLE_OCM 64
OUTLN 72
DBSNMP 440
SYSTEM 4064
PUBLIC 23376
SYS 65168

9 rows selected.

SQL> select name,id,status from v$result_cache_objects;

NAME
-----------------------------------------------------------------------------
ID STATUS
---------- ---------
FRANK.BIG1
0 Published

select owner,count(*) from frank.big1 group by owner order by count(*)
1 Published


SQL> set autotrace on statistics
SQL> select owner,count(*) from frank.big1 group by owner order by count(*);

OWNER COUNT(*)
------------------------------ ----------
FRANK 8
TESTUSER 16
TSMSYS 24
ORACLE_OCM 64
OUTLN 72
DBSNMP 440
SYSTEM 4064
PUBLIC 23376
SYS 65168

9 rows selected.


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets /*完全沒有任何I/O*/
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed


可以使用V$RESULT_CACHE_DEPENDENCY的內容，查知與哪些表格有被快取。
SQL> select * from v$result_cache_dependency;

RESULT_ID DEPEND_ID OBJECT_NO
---------- ---------- ----------
3 0 12082

SQL> col object_name format a30
SQL> select owner,object_name,object_type from dba_objects where object_id=12082;

OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
FRANK BIG1 TABLE



如果需要察看目前的Result Cache的狀態，可以透過V$RESULT_CACHE_STATISTICS或DBMS_RESULT_CACHE.MEMORY_REPORT得知。
SQL> col name format a30
SQL> select * from v$result_cache_statistics;

ID NAME VALUE
------- ------------------------------ ----------
1 Block Size (Bytes) 1024
2 Block Count Maximum 1536
3 Block Count Current 32
4 Result Size Maximum (Blocks) 76
5 Create Count Success 1
6 Create Count Failure 0
7 Find Count 0
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0

10 rows selected.

SQL> set serveroutput on
SQL> execute dbms_result_cache.memory_report;
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 1536K bytes (1536 blocks)
Maximum Result Size = 76K bytes (76 blocks)
[Memory]
Total Memory = 116008 bytes [0.132% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.006% of the Shared Pool]
... Dynamic Memory = 110876 bytes [0.126% of the Shared Pool]
....... Overhead = 78108 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL = 1 blocks (1 count)

PL/SQL procedure successfully completed.

Result Cache的內容會自動由Oracle Server維護，例如當Result Set中的相關表格內容被異動後，已快取的Result Set將會被設定為Invalid，同時相關的相依關係也會被消除。
SQL> select id,name,status from v$result_cache_objects;

ID NAME STATUS
------- ------------------------------ ---------
0 FRANK.BIG1 Published
2 select owner,count(*) from fra Published
nk.big1 group by owner order b
y count(*)

1 SELECT DECODE('A','A','1','2') Published
FROM DUAL


SQL> update frank.big1 set object_id=object_id where object_id=10000;

8 rows updated.

SQL> commit;

Commit complete.

SQL> select id,name,status from v$result_cache_objects;

ID NAME STATUS
------- ------------------------------ ---------
0 FRANK.BIG1 Published
1 SELECT DECODE('A','A','1','2') Published
FROM DUAL

2 select owner,count(*) from fra Invalid
nk.big1 group by owner order b
y count(*)

SQL> select * from v$result_cache_dependency;

no rows selected



結論
Result Set Cache的功能可以大幅降低SQL查詢指令的反應時間，尤其當記憶體的成本越來越低的現在，未來有更多的功能，會利用記憶體的空間，來節省整體的反應時間。不過這項功能不適合用在OLTP系統，因為即便已經將結果集快取後，但是表格內容一但被異動後，便失去其意義。因此這項功能應該用在DW/BATCH的資料庫才對。