Oracle 11g Result cache使用指南

何为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