何為result cache?
result cache，結果快取，當表的訪問方式以讀為主前提下，從一張大表中過濾出少量的記錄作為結果集的查詢語句適合把查詢結果集放入result cache，後續相同的查詢語句可以直接從result cache裡獲取想要的結果，省去了CPU、I/O上的開銷，result cache位於shared pool裡的某一塊區域中，其大小可以透過result_cache_max_size進行調整。本文從以下幾個方面透過實驗全面的瞭解result cache特性
1、result cache基本功能
2、易使result cache裡的內容變成stale的操作
3、使用result cache快取遠端資料庫物件的查詢結果
4、dbms_result_cache package的使用
5、表級別的result cache屬性設定對result cache行為的影響
6、result cache在哪些場合下不會被啟用

//////////////////////////////////////////////////////
/// 1、result cache基本功能測試
//////////////////////////////////////////////////////
---建立測試表
create table rct1 tablespace ts_acct_dat_01 as select * from all_users;

SQL> desc rct1;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
CREATED NOT NULL DATE

---檢視result cache object，此時為空
select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects

---首次執行帶有/*+ result_cache */的select，執行完後檢視執行計劃裡iid=1的operation為RESULT CACHE
select /*+ result_cache */ * from rct1 where user_id=0;

set linesize 170
set pagesize 200
select * from table(dbms_xplan.display_cursor(null,null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0556ycm3kvuud, child number 0
-------------------------------------
select /*+ result_cache */ * from rct1 where user_id=0

Plan hash value: 2755714139

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | RESULT CACHE | 6sxmkvgurm2rj1j9r1ppv6a4h7 | | | | |
|* 2 | TABLE ACCESS FULL| RCT1 | 1 | 39 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

---執行完後檢視v$result_cache_object檢視
select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects
ID TYPE STATUS CREATION_TIMESTAM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---- ---------- --------- ----------------- ------------ --------- ---------- --------- ---------- ------------- -------------------- --------------------
0 Dependency Published 20141231 16:40:29 1 0 0 0 0 0 AD.RCT1 AD.RCT1
1 Result Published 20141231 16:40:29 1 0 0 1 0 0 346t2529j38xn6s4swb3 6sxmkvgurm2rj1j9r1pp
th5tj4 v6a4h7
Type=dependency所在行的cache_key、cache_id表示type=result的行所依賴的物件，Type=dependency行的depend_count表示依賴其的結果有多少個，type=Result的行表示其依賴的物件有多少個，row_count僅在type=Result的行會有>0的值出現，表示當前result_cache裡快取了多少行。v$result_cache_object儲存了dependency和result間的關係

---第二次執行帶有/*+ result_cache */的select，執行完後檢視執行計劃和第一次一樣
select /*+ result_cache */ * from rct1 where user_id=0;

SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0556ycm3kvuud, child number 0
-------------------------------------
select /*+ result_cache */ * from rct1 where user_id=0

Plan hash value: 2755714139

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | RESULT CACHE | 6sxmkvgurm2rj1j9r1ppv6a4h7 | | | | |
|* 2 | TABLE ACCESS FULL| RCT1 | 1 | 39 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

---第二次執行後v$result_cache_object相比前面一次scan_count增加了1，說明這次確實用到了result_cache裡的結果
col name format a20
col cache_id format a20
col cache_key format a20
set linesize 180
select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects;
ID TYPE STATUS CREATION_TIMESTAM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---- ---------- --------- ----------------- ------------ --------- ---------- --------- ---------- ------------- -------------------- --------------------
0 Dependency Published 20141231 16:40:29 1 0 0 0 0 0 AD.RCT1 AD.RCT1
1 Result Published 20141231 16:40:29 1 0 1 1 0 0 346t2529j38xn6s4swb3 6sxmkvgurm2rj1j9r1pp
th5tj4 v6a4h7

---再緩衝一行user_id=9
select /*+ result_cache */ * from rct1 where user_id=9;

---觀察v$result_cache_object中的內容，新增了一行id=2，注意type=Dependency行的depend_count由1變為2
select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects
ID TYPE STATUS CREATION_TIMESTAM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---------- ---------- --------- ----------------- ------------ ---------- ---------- ---------- ---------- ------------- -------------------- --------------------
0 Dependency Published 20141231 16:40:29 2 0 0 0 0 0 AD.RCT1 AD.RCT1
2 Result Published 20141231 17:18:57 1 0 0 1 0 0 201k5dmrk9v10fzk0uk0 2t115vddvb9xt792qxyz
vvm315 g37mzw

1 Result Published 20141231 16:40:29 1 0 1 1 0 0 346t2529j38xn6s4swb3 6sxmkvgurm2rj1j9r1pp
th5tj4 v6a4h7

---v$result_cache_dependency反應了dependency和result間的對應關係: $result_cache_object裡id=1、2的result都依賴於id=0的dependency
select * from v$result_cache_dependency;
RESULT_ID DEPEND_ID OBJECT_NO
---------- ---------- ----------
2 0 6096953
1 0 6096953

---v$result_cache_statistics檢視：如果顯式設定了shared_pool_size那麼result_cache的大小為shared_pool_size的1%
set pagesize 200
col name format a50
col value format a50
set linesize 150
select * from v$result_cache_statistics;
ID NAME VALUE
---------- -------------------------------------------------- --------------------------------------------------
1 Block Size (Bytes) 1024
2 Block Count Maximum 10496
3 Block Count Current 32
4 Result Size Maximum (Blocks) 524
5 Create Count Success 2
6 Create Count Failure 0
7 Find Count 1
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
11 Hash Chain Length 1
12 Find Copy Count 1

SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 1G

SQL> show parameter result_cache_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size big integer 10496K

---V$RESULT_CACHE_MEMORY裡每一行對應一個block，block總數(分配的數量，包括了free和non-free的)等於v$result_cache_statistics裡的Block Count Current
set pagesize 300
select * from V$RESULT_CACHE_MEMORY;
ID CHUNK OFFSET FRE OBJECT_ID POSITION
---------- ---------- ---------- --- ---------- ----------
0 0 0 NO 0 0
1 0 1 NO 1 0
2 0 2 NO 2 0
3 0 3 YES
4 0 4 YES
5 0 5 YES
6 0 6 YES
7 0 7 YES
8 0 8 YES
9 0 9 YES
10 0 10 YES
11 0 11 YES
12 0 12 YES
13 0 13 YES
14 0 14 YES
15 0 15 YES
16 0 16 YES
17 0 17 YES
18 0 18 YES
19 0 19 YES
20 0 20 YES
21 0 21 YES
22 0 22 YES
23 0 23 YES
24 0 24 YES
25 0 25 YES
26 0 26 YES
27 0 27 YES
28 0 28 YES
29 0 29 YES
30 0 30 YES
31 0 31 YES

/////////////////////////////////////////////////////////////////
/// 2、容易使result cache裡的內容變成stale的操作
/////////////////////////////////////////////////////////////////
###result cache所涉及的欄位值發生變化，將rct1表中user_id=9的記錄，其username改為'OOOOO'
---先記錄一下修改前的v$result_cache_object檢視內容，其中id=2對應為user_id=9的記錄，id=0、type=dependency的depend_count=2表明此時已經有兩個結果依賴於它
col name format a20
col cache_id format a20
col cache_key format a20
set linesize 180
select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects;
SQL>
ID TYPE STATUS CREATION_TIMESTAM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---------- ---------- --------- ----------------- ------------ ---------- ---------- ---------- ---------- ------------- -------------------- --------------------
0 Dependency Published 20141231 16:40:29 2 0 0 0 0 0 AD.RCT1 AD.RCT1
2 Result Published 20141231 17:18:57 1 0 0 1 0 0 201k5dmrk9v10fzk0uk0 2t115vddvb9xt792qxyz
vvm315 g37mzw

1 Result Published 20141231 16:40:29 1 0 1 1 0 0 346t2529j38xn6s4swb3 6sxmkvgurm2rj1j9r1pp
th5tj4 v6a4h7

---進行修改，修改後發現雖然我們修改的是user_id=9的記錄(對應id=2的result)，但結果卻是v$result_cache_object裡欄位id=1(對應user_id=0的結果集)、id=2(對應user_id=9的結果集)對應行的status都變成了invalid，type=dependency的行對應的invalidations增加了1，表示ad.rct1物件的變更引發了依賴其的result cache失效，次數為1次
update rct1 set username='OOOOO' where user_id=9;
commit;

select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects

ID TYPE STATUS CREATION_TIMESTAM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---------- ---------- --------- ----------------- ------------ ---------- ---------- ---------- ---------- ------------- -------------------- --------------------
0 Dependency Published 20141231 16:40:29 0 0 0 0 0 1 AD.RCT1 AD.RCT1
1 Result Invalid 20141231 16:40:29 1 0 1 1 0 0 346t2529j38xn6s4swb3 6sxmkvgurm2rj1j9r1pp
th5tj4 v6a4h7

2 Result Invalid 20141231 17:18:57 1 0 0 1 0 0 201k5dmrk9v10fzk0uk0 2t115vddvb9xt792qxyz
vvm315 g37mzw

---v$result_cache_dependency裡也不再顯示result對於table的依賴關係
SQL> select * from v$result_cache_dependency;

no rows selected

---從v$result_cache_statistics裡，可以觀察到invalidation Count上升為2
set pagesize 200
col name format a50
col value format a50
set linesize 150
select * from v$result_cache_statistics;

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

---我們重新執行user_id=0、user_id=9兩條select語句，再觀察v$result_cache_object、v$result_cache_dependency、v$result_cache_statistics、v$result_cache_memory
select /*+ result_cache */ * from rct1 where user_id=0;
select /*+ result_cache */ * from rct1 where user_id=9;

SQL>select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects;

ID TYPE STATUS CREATION_TIMESTAM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---------- ---------- --------- ----------------- ------------ ---------- ---------- ---------- ---------- ------------- -------------------- --------------------
0 Dependency Published 20141231 16:40:29 2 0 0 0 0 1 AD.RCT1 AD.RCT1
4 Result Published 20141231 21:41:12 1 0 0 1 0 0 201k5dmrk9v10fzk0uk0 2t115vddvb9xt792qxyz
vvm315 g37mzw

3 Result Published 20141231 21:40:49 1 0 0 1 0 0 346t2529j38xn6s4swb3 6sxmkvgurm2rj1j9r1pp
th5tj4 v6a4h7

1 Result Invalid 20141231 16:40:29 1 0 1 1 0 0 346t2529j38xn6s4swb3 6sxmkvgurm2rj1j9r1pp
th5tj4 v6a4h7

2 Result Invalid 20141231 17:18:57 1 0 0 1 0 0 201k5dmrk9v10fzk0uk0 2t115vddvb9xt792qxyz

SQL> select * from v$result_cache_dependency; vvm315 g37mzw

RESULT_ID DEPEND_ID OBJECT_NO
---------- ---------- ----------
4 0 6096953
3 0 6096953

SQL> select * from v$result_cache_memory;

ID CHUNK OFFSET FRE OBJECT_ID POSITION
---------- ---------- ---------- --- ---------- ----------
0 0 0 NO 0 0
1 0 1 NO 1 0
2 0 2 NO 2 0
3 0 3 NO 3 0
4 0 4 NO 4 0
5 0 5 YES
6 0 6 YES
7 0 7 YES
8 0 8 YES
9 0 9 YES
10 0 10 YES
11 0 11 YES
12 0 12 YES
13 0 13 YES
14 0 14 YES
15 0 15 YES
16 0 16 YES
17 0 17 YES
18 0 18 YES
19 0 19 YES
20 0 20 YES
21 0 21 YES
22 0 22 YES
23 0 23 YES
24 0 24 YES
25 0 25 YES
26 0 26 YES
27 0 27 YES
28 0 28 YES
29 0 29 YES
30 0 30 YES
31 0 31 YES

set pagesize 200
col name format a50
col value format a50
set linesize 150
select * from v$result_cache_statistics;
ID NAME VALUE
---------- -------------------------------------------------- --------------------------------------------------
1 Block Size (Bytes) 1024
2 Block Count Maximum 10496
3 Block Count Current 32
4 Result Size Maximum (Blocks) 524
5 Create Count Success 4
6 Create Count Failure 0
7 Find Count 1
8 Invalidation Count 2
9 Delete Count Invalid 0
10 Delete Count Valid 0
11 Hash Chain Length 1
12 Find Copy Count 1

由以上內容可見， v$result_cache_object裡新增了兩個type=result的rows，id=3、id=4，這兩個id的result和ad.rct1的依賴關係反應在了v$result_cache_dependency檢視，v$result_cache_memory裡non-free的block數量從3個上升為5個block(每個block為1k大小)

---再次執行user_id=9、user_id=0的兩條SQL，id=4、id=3的scan_count變成了1，說明這次的確是從result_cache裡獲取的結果
select /*+ result_cache */ * from rct1 where user_id=0;
select /*+ result_cache */ * from rct1 where user_id=9;

col name format a20
col cache_id format a20
col cache_key format a20
set linesize 180
select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects;
ID TYPE STATUS CREATION_TIMESTAM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---------- ---------- --------- ----------------- ------------ ---------- ---------- ---------- ---------- ------------- -------------------- --------------------
0 Dependency Published 20141231 16:40:29 2 0 0 0 0 1 AD.RCT1 AD.RCT1
4 Result Published 20141231 21:41:12 1 0 1 1 0 0 201k5dmrk9v10fzk0uk0 2t115vddvb9xt792qxyz
vvm315 g37mzw

3 Result Published 20141231 21:40:49 1 0 1 1 0 0 346t2529j38xn6s4swb3 6sxmkvgurm2rj1j9r1pp
th5tj4 v6a4h7

1 Result Invalid 20141231 16:40:29 1 0 1 1 0 0 346t2529j38xn6s4swb3 6sxmkvgurm2rj1j9r1pp
th5tj4 v6a4h7

2 Result Invalid 20141231 17:18:57 1 0 0 1 0 0 201k5dmrk9v10fzk0uk0 2t115vddvb9xt792qxyz
vvm315 g37mzw

###result cache所涉及的表結構發生變化，觀察是否result變為invalid
---修改rct1.username欄位長度，從30bytes改為100bytes
alter table rct1 modify(username varchar2(100));

---果真id=4、id=5的result也變成了invalid，修改table的metadata也會使得result cache中儲存的值失效
col name format a20
col cache_id format a20
col cache_key format a20
set linesize 180
select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects;
ID TYPE STATUS CREATION_TIMESTAM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---------- ---------- --------- ----------------- ------------ ---------- ---------- ---------- ---------- ------------- -------------------- --------------------
0 Dependency Published 20141231 16:40:29 0 0 0 0 0 2 AD.RCT1 AD.RCT1
1 Result Invalid 20141231 16:40:29 1 0 1 1 0 0 346t2529j38xn6s4swb3 6sxmkvgurm2rj1j9r1pp
th5tj4 v6a4h7

2 Result Invalid 20141231 17:18:57 1 0 0 1 0 0 201k5dmrk9v10fzk0uk0 2t115vddvb9xt792qxyz
vvm315 g37mzw

3 Result Invalid 20141231 21:40:49 1 0 1 1 0 0 346t2529j38xn6s4swb3 6sxmkvgurm2rj1j9r1pp
th5tj4 v6a4h7

4 Result Invalid 20141231 21:41:12 1 0 1 1 0 0 201k5dmrk9v10fzk0uk0 2t115vddvb9xt792qxyz
vvm315 g37mzw

---invalidation count也變為了4
SQL> select * from v$result_cache_statistics;

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

###ad.rct1表的enable row movement屬性發生修改的情況下，是否也會使得result cache變為invalidated
---先修復上例產生的invalidation result，重新執行下列兩條語句
select /*+ result_cache */ * from rct1 where user_id=0;
select /*+ result_cache */ * from rct1 where user_id=9;

SQL> select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects;

ID TYPE STATUS CREATION_TIMESTAM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---------- ---------- --------- ----------------- ------------ ---------- ---------- ---------- ---------- ------------- -------------------- --------------------
0 Dependency Published 20141231 16:40:29 2 0 0 0 0 2 AD.RCT1 AD.RCT1
6 Result Published 20141231 22:14:11 1 0 0 1 0 0 201k5dmrk9v10fzk0uk0 2t115vddvb9xt792qxyz
vvm315 g37mzw

5 Result Published 20141231 22:13:50 1 0 0 1 0 0 346t2529j38xn6s4swb3 6sxmkvgurm2rj1j9r1pp
th5tj4 v6a4h7

1 Result Invalid 20141231 16:40:29 1 0 1 1 0 0 346t2529j38xn6s4swb3 6sxmkvgurm2rj1j9r1pp
th5tj4 v6a4h7

2 Result Invalid 20141231 17:18:57 1 0 0 1 0 0 201k5dmrk9v10fzk0uk0 2t115vddvb9xt792qxyz
vvm315 g37mzw

3 Result Invalid 20141231 21:40:49 1 0 1 1 0 0 346t2529j38xn6s4swb3 6sxmkvgurm2rj1j9r1pp
th5tj4 v6a4h7

4 Result Invalid 20141231 21:41:12 1 0 1 1 0 0 201k5dmrk9v10fzk0uk0 2t115vddvb9xt792qxyz
vvm315 g37mzw

---修改rct1的row movement屬性
alter table rct1 enable row movement;

---又變成invalid了
SQL> r
1* select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects

ID TYPE STATUS CREATION_TIMESTAM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---------- ---------- --------- ----------------- ------------ ---------- ---------- ---------- ---------- ------------- -------------------- --------------------
0 Dependency Published 20141231 16:40:29 0 0 0 0 0 3 AD.RCT1 AD.RCT1
1 Result Invalid 20141231 16:40:29 1 0 1 1 0 0 346t2529j38xn6s4swb3 6sxmkvgurm2rj1j9r1pp
th5tj4 v6a4h7

2 Result Invalid 20141231 17:18:57 1 0 0 1 0 0 201k5dmrk9v10fzk0uk0 2t115vddvb9xt792qxyz
vvm315 g37mzw

3 Result Invalid 20141231 21:40:49 1 0 1 1 0 0 346t2529j38xn6s4swb3 6sxmkvgurm2rj1j9r1pp
th5tj4 v6a4h7

4 Result Invalid 20141231 21:41:12 1 0 1 1 0 0 201k5dmrk9v10fzk0uk0 2t115vddvb9xt792qxyz
vvm315 g37mzw

5 Result Invalid 20141231 22:13:50 1 0 0 1 0 0 346t2529j38xn6s4swb3 6sxmkvgurm2rj1j9r1pp
th5tj4 v6a4h7

6 Result Invalid 20141231 22:14:11 1 0 0 1 0 0 201k5dmrk9v10fzk0uk0 2t115vddvb9xt792qxyz
vvm315 g37mzw

---清理result cache裡的內容使用alter system flush shared_pool無效，必須使用dbms_result_cache.flush
exec dbms_result_cache.flush;

////////////////////////////////////////////////////////////////////////////
/// 3、如果result cache裡存放的是透過db_link獲取的遠端資料庫上的結果
////////////////////////////////////////////////////////////////////////////
###RESULT_CACHE_REMOTE_EXPIRATION引數
---先清理result cache
exec dbms_result_cache.flush;

---設定為0表示對於遠端資料庫上的結果不能使用result cache
SQL> show parameter RESULT_CACHE_REMOTE_EXPIRATION

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_remote_expiration integer 0

---執行訪問遠端物件的SQL
select /*+ result_cache */ * from rct1@ad_bcv209 where user_id=9;

---執行計劃裡有RESULT CACHE出現，但後面檢視v$result_cache_objects檢視發現result cache裡沒有內容
set linesize 170
set pagesize 200
select * from table(dbms_xplan.display_cursor(null,null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6t2hnrt01guwt, child number 0
-------------------------------------
select /*+ result_cache */ * from rct1@ad_bcv209 where user_id=9

Plan hash value: 2014232345

---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| | | |
| 1 | RESULT CACHE | 4rt03uk7tzs57fg5n44429xwzd | | | | | | |
| 2 | REMOTE | RCT1 | 16 | 1184 | 5 (0)| 00:00:01 | AD_BC~ | R->S |
---------------------------------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

2 - SELECT "USERNAME","USER_ID","CREATED" FROM "RCT1" "RCT1" WHERE "USER_ID"=9 (accessing
'AD_BCV209' )

---檢視v$result_cache_objects無內容
col name format a20
col cache_id format a20
col cache_key format a20
set linesize 180
select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects;

no rows selected

---RESULT_CACHE_REMOTE_EXPIRATION設定為1分鐘
alter session set RESULT_CACHE_REMOTE_EXPIRATION=1;

select /*+ result_cache */ * from rct1@ad_bcv209 where user_id=9

select * from table(dbms_xplan.display_cursor(null,null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID btv7t83g01kq4, child number 0
-------------------------------------
select /*+ result_cache */ * from rct1@ad_bcv209 where user_id=9

Plan hash value: 2014232345

---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| | | |
| 1 | RESULT CACHE | 4rt03uk7tzs57fg5n44429xwzd | | | | | | |
| 2 | REMOTE | RCT1 | 16 | 1184 | 5 (0)| 00:00:01 | AD_BC~ | R->S |
---------------------------------------------------------------------------------------------------------------

---v$result_cache_object裡有內容了，但注意這裡沒有型別為dependency的記錄，因為dependency物件在遠端
select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects;
ID TYPE STATUS CREATION_TIM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---------- ---------- --------- ------------ ------------ ---------- ---------- ---------- ---------- ------------- -------------------- --------------------
0 Result Published 01-JAN-15 0 0 0 1 0 0 9hyztg2n2dd6x1cxj4s9 4rt03uk7tzs57fg5n444
a3s8ww 29xwzd
---過了一分鐘狀態變為expired了，接著又變為invalid
SQL> r
1* select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects

ID TYPE STATUS CREATION_TIM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---------- ---------- --------- ------------ ------------ ---------- ---------- ---------- ---------- ------------- -------------------- --------------------
0 Result Expired 01-JAN-15 0 0 0 1 0 0 9hyztg2n2dd6x1cxj4s9 4rt03uk7tzs57fg5n444
a3s8ww 29xwzd
SQL> r
1* select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects

ID TYPE STATUS CREATION_TIM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---------- ---------- --------- ------------ ------------ ---------- ---------- ---------- ---------- ------------- -------------------- --------------------
0 Result Invalid 01-JAN-15 0 0 0 1 0 0 9hyztg2n2dd6x1cxj4s9 4rt03uk7tzs57fg5n444
a3s8ww 29xwzd

###當RESULT_CACHE_REMOTE_EXPIRATION設定為x的情況下，在x分鐘以內及時遠端資料庫上的記錄發生修改也不會同步到本地，會出現本地查詢到過期記錄的情況
---設定為1分鐘
alter session set RESULT_CACHE_REMOTE_EXPIRATION=1;

show parameter RESULT_CACHE_REMOTE_EXPIRATION
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_remote_expiration integer 1

---執行兩次查詢操作，確保第二次使用result cache裡獲取的結果
select /*+ result_cache */ * from rct1@ad_bcv209 where user_id=9;
select /*+ result_cache */ * from rct1@ad_bcv209 where user_id=9;

select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects
ID TYPE STATUS CREATION_TIM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---------- ---------- --------- ------------ ------------ ---------- ---------- ---------- ---------- ------------- -------------------- --------------------
0 Result Published 01-JAN-15 0 0 1 1 0 0 9hyztg2n2dd6x1cxj4s9 4rt03uk7tzs57fg5n444
a3s8ww 29xwzd

---立即在遠端資料庫上更改記錄內容
update rct1 set created=sysdate where user_id=9;

commit;

---在result_cache_remote_expiration定義的時間之內，從遠端、本地資料庫上查到的結果不一致，體現在created欄位上
**遠端資料庫
select * from rct1 where user_id=9;
SERNAME USER_ID CREATED
-------------------- ---------- -----------------
OOOOO 9 20150101 09:11:53

**本地資料庫透過dblink連線到遠端資料庫查詢
select /*+ result_cache */ * from rct1@ad_bcv209 where user_id=9

USERNAME USER_ID CREATED
---------------------------------------------------------------------------------------------------- ---------- -----------------
OOOOO 9 20120920 14:55:31

---超過result_cache_remote_expiration定義的時間之後，result變為invalid了
select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects

ID TYPE STATUS CREATION_TIM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---------- ---------- --------- ------------ ------------ ---------- ---------- ---------- ---------- ------------- -------------------- --------------------

0 Result Invalid 01-JAN-15 0 0 3 1 0 0 9hyztg2n2dd6x1cxj4s9 4rt03uk7tzs57fg5n444
a3s8ww 29xwzd

//////////////////////////////////////////////////////
/// 4、dbms_result_cache package的使用
//////////////////////////////////////////////////////
### DBMS_RESULT_CACHE.MEMORY_REPORT生成統計報告(detailed=>FALSE)
set serveroutput on
EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT(detailed=>FALSE);
SQL> 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 = 10496K bytes (10496 blocks)
Maximum Result Size = 524K bytes (524 blocks)
[Memory]
Total Memory = 162168 bytes [0.009% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.000% of the Shared Pool]
... Dynamic Memory = 156816 bytes [0.008% of the Shared Pool]
....... Overhead = 124048 bytes
....... Cache Memory = 32K bytes (32 blocks) ---對應於select count(*) from v$result_cache_memory;
........... Unused Memory = 29 blocks ---對應於select count(*) from v$result_cache_memory where free='YES'
........... Used Memory = 3 blocks ---對應於select count(*) from v$result_cache_memory where free='NO'
............... Dependencies = 2 blocks (2 count) ---對應於select count(*) from v$result_cache_objects where type='Dependency';
............... Results = 1 blocks ---對應於select count(*) from v$result_cache_objects where type='Result';
................... Invalid = 1 blocks (1 count) ---對應於select count(*) from v$result_cache_objects where status='STATUS';

### DBMS_RESULT_CACHE.MEMORY_REPORT生成統計報告(detailed=>TRUE)
set serveroutput on
EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT(detailed=>TRUE);

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 = 10496K bytes (10496 blocks)
Maximum Result Size = 524K bytes (524 blocks)
[Memory]
Total Memory = 162168 bytes [0.009% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.000% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr = 208 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 156816 bytes [0.008% of the Shared Pool]
....... Overhead = 124048 bytes
........... Hash Table = 64K bytes (4K buckets)
........... Chunk Ptrs = 24K bytes (3K slots)
........... Chunk Maps = 12K bytes
........... Miscellaneous = 21648 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 29 blocks
........... Used Memory = 3 blocks
............... Dependencies = 2 blocks (2 count)
............... Results = 1 blocks
................... Invalid = 1 blocks (1 count)

//////////////////////////////////////////////////////
/// 5、開啟表級別的result cache引數設定為force
//////////////////////////////////////////////////////
###表上賦予了result_cache為force的屬性後，對於僅包含該表的查詢不加/*+ result_cache */也能使用到result cache特性
---清空result cache
exec dbms_result_cache.flush;

---驗證v$result_cache_objects無內容
col name format a20
col cache_id format a20
col cache_key format a20
set linesize 180
select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects;

no rows selected

---建立新的測試表
create table rct2 as select * from rct1;

SQL>select result_cache,table_name from user_tables where table_name='RCT2'

RESULT_ TABLE_NAME
------- ------------------------------
DEFAULT RCT2

alter table rct2 result_cache (mode force);

select result_cache,table_name from user_tables where table_name='RCT2';

RESULT_ TABLE_NAME
------- ------------------------------
FORCE RCT2

---首次執行不帶/*+ result_cache */的查詢
select * from rct2 where user_id=9;

---執行計劃中預設使用了RESULT CACHE
set linesize 170 pagesize 100
select * from table(dbms_xplan.display_cursor());

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cdkf6yg88acud, child number 0
-------------------------------------
select * from rct2 where user_id=9

Plan hash value: 3469089648

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | RESULT CACHE | gjw9z4qdbr7tx8tk39fsnv4swg | | | | |
|* 2 | TABLE ACCESS FULL| RCT2 | 1 | 74 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

---再次執行不帶/*+ result_cache */的查詢
select * from rct2 where user_id=9;

---執行完後發現v$result_cache_objects裡的scan_count增加了1
col name format a20
col cache_id format a20
col cache_key format a20
set linesize 180
select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects;
SQL> SQL>
ID TYPE STATUS CREATION_TIMESTAM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---------- ---------- --------- ----------------- ------------ ---------- ---------- ---------- ---------- ------------- -------------------- --------------------
0 Dependency Published 20150101 11:29:40 1 0 0 0 0 0 AD.RCT2 AD.RCT2
1 Result Published 20150101 11:29:40 1 0 1 1 0 0 cp66ntcgsps5c4ztptzc gjw9z4qdbr7tx8tk39fs
0tmhzm nv4swg

###表級的result_cache的屬性僅會作用到整個查詢，而不會作用到查詢塊裡；在僅使用表級result cache定義的情況下如果要使整個查詢用到result cache，涉及查詢的所有表都必須附上result_cache=force
---清空result cache
exec dbms_result_cache.flush;

---給rct2表定義result_cache的force屬性，給rct1表定義result_cache的default屬性
alter table rct2 result_cache (mode force);
alter table rct1 result_cache (mode default);

---下列查詢無法使用到result cache，因為rct2雖然有result_cache為force的定義，但rct2所在的查詢語句是query block不是whole query，query block裡的表即使定義了result_cache=force也不會啟用result cache屬性；rct1雖然在外層查詢裡但因為使用的是default mode所以導致整個查詢都無法用到result cache
select * from (select * from rct2 where user_id=9) s1,rct1 where s1.user_id=rct1.user_id;

---執行計劃中不包含RESULT_CACHE字樣
set linesize 170 pagesize 100
select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b6uagh7aht1v3, child number 0
-------------------------------------
select * from (select * from rct2 where user_id=9) s1,rct1 where
s1.user_id=rct1.user_id

Plan hash value: 666718222

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 11 (100)| |
|* 1 | HASH JOIN | | 1 | 148 | 11 (10)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| RCT2 | 1 | 74 | 5 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| RCT1 | 1 | 74 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------

col name format a20
col cache_id format a20
col cache_key format a20
set linesize 180
select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects;

no rows selected

---給rct1表也定義上result_cache的force屬性後再次嘗試上述查詢
alter table rct1 result_cache (mode force);

---還是沒有用到result cache，雖然兩個表都已經是result_cache=force的屬性
SQL> select table_name,result_cache from dba_tables where table_name like 'RCT%';

TABLE_NAME RESULT_
------------------------------ -------
RCT2 FORCE
RCT1 FORCE

select * from (select * from rct2 where user_id=9) s1,rct1 where s1.user_id=rct1.user_id;

select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects

no rows selected

---只在子查詢中新增/*+ result_cache */，那麼結果只是子查詢的結果緩衝到了result cache裡，可以看到v$result_cache_objects裡只有AD.RCT2沒有AD.RCT1
select * from (select /*+ result_cache */ * from rct2 where user_id=9) s1,rct1 where s1.user_id=rct1.user_id;

select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects

ID TYPE STATUS CREATION_TIMESTAM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---------- ---------- --------- ----------------- ------------ ---------- ---------- ---------- ---------- ------------- -------------------- --------------------
0 Dependency Published 20150101 14:31:03 1 0 0 0 0 0 AD.RCT2 AD.RCT2
1 Result Published 20150101 14:31:03 1 0 0 1 0 0 cp66ntcgsps5c4ztptzc gjw9z4qdbr7tx8tk39fs
0tmhzm nv4swg
---要使得整條語句都用上result cache必須在outer query層面也加上/*+ result_cache */
select /*+ result_cache */ * from (select /*+ result_cache */ * from rct2 where user_id=9) s1,rct1 where s1.user_id=rct1.user_id;

select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects

ID TYPE STATUS CREATION_TIMESTAM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---------- ---------- --------- ----------------- ------------ ---------- ---------- ---------- ---------- ------------- -------------------- --------------------
2 Dependency Published 20150101 14:32:20 1 0 0 0 0 0 AD.RCT1 AD.RCT1
0 Dependency Published 20150101 14:31:03 2 0 0 0 0 0 AD.RCT2 AD.RCT2
3 Result Published 20150101 14:32:20 2 0 0 1 0 0 2qb9f4cp24fpud7s4q1n 3furhpzvdzn8gdu6zj99
1214h9 kgkksv

1 Result Published 20150101 14:31:03 1 0 2 1 0 0 cp66ntcgsps5c4ztptzc gjw9z4qdbr7tx8tk39fs
0tmhzm nv4swg

---但如果寫成下面inner的形式就能利用到表級的result cache屬性
select * from rct2,rct1 where rct2.user_id=rct1.user_id and rct1.user_id=9;
select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects

ID TYPE STATUS CREATION_TIMESTAM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---------- ---------- --------- ----------------- ------------ ---------- ---------- ---------- ---------- ------------- -------------------- --------------------
1 Dependency Published 20150101 14:46:12 1 0 0 0 0 0 AD.RCT2 AD.RCT2
0 Dependency Published 20150101 14:46:12 1 0 0 0 0 0 AD.RCT1 AD.RCT1
2 Result Published 20150101 14:46:12 2 0 0 1 0 0 83780wanjc96ua7q58rm 00urjma4d5f574mz7urk
hp8p12 mb2hwf

---如果將rct1表的result cache mode改回default，那麼上面的語句將無法使用result cache
exec dbms_result_cache.flush;

alter table rct1 result_cache(mode default);

select * from rct2,rct1 where rct2.user_id=rct1.user_id and rct1.user_id=9;

select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects;

no rows selected

//////////////////////////////////////////////////////
/// 6、不能使用到result cache的場合
//////////////////////////////////////////////////////
###result cache不能用在scalar subquery的場合(scalar subquery是指僅返回一個值的子查詢)
---下列查詢語句沒有包含在scalable subquery時能夠使用到result cache
select /*+ result_cache */ username from rct1 where user_id=9;

set pagesize 150 linesize 170
select * from table(dbms_xplan.display_cursor(null,null,'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+ result_cache */ username from rct1 where user_id=9

Plan hash value: 2755714139

---------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | RESULT CACHE | dta71ub9tkrpdfspnhgjuaajtf |
| 2 | TABLE ACCESS FULL| RCT1 |
---------------------------------------------------------

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

1 -

col name format a20
col cache_id format a20
col cache_key format a20
set linesize 180
set pagesize 100
select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects where status='Published';
ID TYPE STATUS CREATION_TIMESTAM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---------- ---------- --------- ----------------- ------------ ---------- ---------- ---------- ---------- ------------- -------------------- --------------------
0 Dependency Published 20141231 16:40:29 1 0 0 0 0 3 AD.RCT1 AD.RCT1
7 Result Published 20150101 07:42:27 1 0 0 1 0 0 331g5r72acudbfx041th dta71ub9tkrpdfspnhgj
6f1gmu uaajtf
select /*+ result_cache */ username from rct1 where user_id=9;

col name format a20
col cache_id format a20
col cache_key format a20
set linesize 180
select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects where status='Published';
ID TYPE STATUS CREATION_TIMESTAM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---------- ---------- --------- ----------------- ------------ ---------- ---------- ---------- ---------- ------------- -------------------- --------------------
0 Dependency Published 20141231 16:40:29 1 0 0 0 0 3 AD.RCT1 AD.RCT1
7 Result Published 20150101 07:42:27 1 0 1 1 0 0 331g5r72acudbfx041th dta71ub9tkrpdfspnhgj
6f1gmu uaajtf

---同樣的查詢包含在scalable subquery時無法用到result cache
select (select /*+ result_cache */ username from rct1 where user_id=9) c1 from rct1;

set pagesize 150 linesize 170
select * from table(dbms_xplan.display_cursor(null,null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6a4h4j7cbqdkw, child number 0
-------------------------------------
select (select /*+ result_cache */ username from rct1 where user_id=9)
c1 from rct1

Plan hash value: 2343611958

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
|* 1 | TABLE ACCESS FULL| RCT1 | 1 | 65 | 5 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| RCT1 | 45 | | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------

---如果把多列放到object裡，也一樣不會用到result cache
create or replace type rct1_rowtype as object
(
un varchar2(100),
crt_time date
)
/

select (select /*+ result_cache */ rct1_rowtype(username,created) from rct1 where user_id=9) from dual;

set pagesize 150 linesize 170
select * from table(dbms_xplan.display_cursor(null,null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a9kvut6a6cc5r, child number 0
-------------------------------------
select (select /*+ result_cache */ rct1_rowtype(username,created) from
rct1 where user_id=9) from dual

Plan hash value: 3473461435

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| RCT1 | 1 | 74 | 5 (0)| 00:00:01 |
| 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

###如果view的查詢語句和外層查詢語句之間有欄位關聯，那麼result cache是不能用在view的查詢語句裡的
---表結構
SQL> desc rct1
Name Null? Type
----------------------- -------- ----------------
USERNAME NOT NULL VARCHAR2(100)
USER_ID NOT NULL NUMBER
CREATED NOT NULL DATE

SQL> desc rct2;
Name Null? Type
----------------------- -------- ----------------
USERNAME NOT NULL VARCHAR2(100)
USER_ID NOT NULL NUMBER
CREATED NOT NULL DATE

---下面兩條語句就是因為view裡所在查詢語句就是correlated subquery，無法用到result cache
select username,user_id,(select /*+ result_cache */ count(*) from rct2 where user_id=rct1.user_id) from rct1;

select username,user_id from rct2 where exists (select user_id from rct1 where user_id=rct2.user_id);

###result cache在一致性上有嚴格的要求，要求進入result cache的內容必須來自於最新的已經提交的資料塊，如果當前session正在對某張表進行dml操作，且尚未commit或者rollback，那麼這個session裡所有對於這張表的select結果是無法快取在result cache裡的
----修改user_id=9的記錄但不提交，可以看到沒有提交的記錄的查詢
update rct2 set created=sysdate where user_id=9;

select /*+ result_cache */ * from rct2 where user_id=9;

col name format a20
col cache_id format a20
col cache_key format a20
set linesize 180
select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects;

no rows selected

----雖然沒有修改user_id=0的行，但它和user_id=9同在rct2表裡，rct2表還有事物沒有提交所以user_id=0的記錄也無法快取到result_cache裡
select /*+ result_cache */ * from rct2 where user_id=0;

select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects;

no rows selected

---直至事物提交，result cache才能被重新利用起來
commit;

select /*+ result_cache */ * from rct2 where user_id=0;

select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects;

ID TYPE STATUS CREATION_TIMESTAM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---------- ---------- --------- ----------------- ------------ ---------- ---------- ---------- ---------- ------------- -------------------- --------------------
0 Dependency Published 20150101 15:18:42 1 0 0 0 0 0 AD.RCT2 AD.RCT2
1 Result Published 20150101 15:18:42 1 0 0 1 0 0 ah8zt4v3ph27p2q7bpa5 cfnp2mv6yxpam2vz7cpm
5tk5hv 2pagy3

###從result cache對事物一致性的要求來推理，使用set transaction read only的情況也是無法使用到result cache特性的，因為它讀到的不一定是最新的已經提交的資料
set transaction read only;

select /*+ result_cache */ * from rct2 where user_id=9;

---可以看到v$result_cache_objects結果裡並沒有type=result的記錄進入
select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects;
1* select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects

ID TYPE STATUS CREATION_TIMESTAM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---------- ---------- --------- ----------------- ------------ ---------- ---------- ---------- ---------- ------------- -------------------- --------------------
0 Dependency Published 20150101 15:30:31 0 0 0 0 0 0 AD.RCT2 AD.RCT2

###對於結果中包含系統自帶non-deterministic函式的查詢無法使用result cache
select /*+ result_cache */ sysdate from dual;

SQL> select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects;

no rows selected

---但是對於user defined的函式，如果函式里包含了系統的non-deterministic函式則仍然可以使用result cache
create or replace function self1 return date is
v1 date;
begin
v1:=sysdate;
return v1;
end;
/

select /*+ result_cache */ self1 from dual;

SELF1
-----------------
20150101 16:12:56

select /*+ result_cache */ self1 from dual

SELF1
-----------------
20150101 16:12:56

select /*+ result_cache */ self1 from dual

SELF1
-----------------
20150101 16:12:56

select id,type,status,creation_timestamp,depend_count,pin_count,scan_count,row_count,build_time,invalidations,cache_key,cache_id from v$result_cache_objects

ID TYPE STATUS CREATION_TIMESTAM DEPEND_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT BUILD_TIME INVALIDATIONS CACHE_KEY CACHE_ID
---------- ---------- --------- ----------------- ------------ ---------- ---------- ---------- ---------- ------------- -------------------- --------------------
0 Dependency Published 20150101 16:12:56 1 0 0 0 0 0 AD.SELF1 AD.SELF1
1 Result Published 20150101 16:12:56 1 0 2 1 0 0 aa5dvwyysk0p6ayy3xbw 733wf05pb0x94auxkbyk
hry70j 56w3fs

ORACLE 11g Result cache使用指南

相關文章