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