[20151015]关于result cache 2.txt
--11G 开始支持result cache,把执行的结果保存在共享池，从而一定程度减少逻辑读，而那些对象或者那些语句适合做result cache呢？
--自己在前一段时间想把一些表设置RESULT_CACHE为MODE FORCE，访问这些对象时，可以利用result cache模式。
--另外我本来想一些语句通过sql patch的方式加入提示/*+ result_cache */ ,结果不成功。
--参考链接：http://blog.itpub.net/267265/viewspace-1377511/
--那些对象可以这样呢？
--我个人觉得那些执行频次很高的sql语句以及"只读"表(指很少修改的表)做为首选。
--我前面提到依赖的本质是scn。通过测试来说明问题。测试看看：
1.测试环境：
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
-------------------- -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> create table t as select rownum id , lpad('x',200,'x') name from dual connect by level<=1e4;
Table created.
create unique index pk_t on t (id);
alter table t add constraint pk_t primary key (id);
--分析表忽略。
SCOTT@test> select rowid,t.id from t where id in (1,1000);
ROWID ID
------------------ ----------
AABNepAAEAAAACjAAA 1
AABNepAAEAAAAJCAAN 1000
--这两条记录在不同的块中。
--设置result_cache=mode force.
SCOTT@test> alter table t result_cache (mode force);
Table altered.
--昨天的测试，说明想即使像select count(*) from t 这样的语句实际上依赖的还是表，而不是索引。
2.为了获得干净的测试环境，我重启数据库，这样结果集清除。或者执行如下清除result cache：
SCOTT@test> exec DBMS_RESULT_CACHE.flush ;
PL/SQL procedure successfully completed.
SCOTT@test> SELECT id,TYPE,status,NAME,object_no,cache_id,invalidations,scn FROM v$result_cache_objects;
no rows selected
SCOTT@test> select * from GV$RESULT_CACHE_DEPENDENCY;
no rows selected
3.测试：
SCOTT@test> select count(*) from t;
COUNT(*)
------------
10000
SCOTT@test> select count(*) from t;
COUNT(*)
------------
10000
SCOTT@test> select current_scn from v$database ;
CURRENT_SCN
------------
13205444762
SCOTT@test> SELECT id,TYPE,status,NAME,object_no,cache_id,invalidations,scn FROM v$result_cache_objects;
ID TYPE STATUS NAME OBJECT_NO CACHE_ID INVALIDATIONS SCN
--- ----------- --------- ------------------------------ ------------ --------------------------- ------------- ------------
0 Dependency Published SCOTT.T 317353 SCOTT.T 0 13205444736
1 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444736
SCOTT@test> select * from GV$RESULT_CACHE_DEPENDENCY;
INST_ID RESULT_ID DEPEND_ID OBJECT_NO
------------ ------------ ------------ ------------
1 1 0 317353
SCOTT@test> select owner,object_name,object_id,data_object_id from dba_objects where owner=user and object_name in ('T','PK_T');
OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
---------- ------------ --------- --------------
SCOTT PK_T 317354 317354
T 317353 317353
--可以发现result cache依赖表T。注意看scn是13205444736。
--如果这个时候做dml，依赖马上失效，INVALIDATIONS+1。ID=0的scn增加。
SCOTT@test> insert into t values (1e4+1,'xxxx');
1 row created.
SCOTT@test> SELECT id,TYPE,status,NAME,object_no,cache_id,invalidations,scn FROM v$result_cache_objects;
ID TYPE STATUS NAME OBJECT_NO CACHE_ID INVALIDATIONS SCN
--- ----------- --------- ------------------------------ ------------ --------------------------- ------------- ------------
0 Dependency Published SCOTT.T 317353 SCOTT.T 0 13205444736
1 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444736
SCOTT@test> commit ;
Commit complete.
SCOTT@test> SELECT id,TYPE,status,NAME,object_no,cache_id,invalidations,scn FROM v$result_cache_objects;
ID TYPE STATUS NAME OBJECT_NO CACHE_ID INVALIDATIONS SCN
--- ----------- --------- ------------------------------ ------------ --------------------------- ------------- ------------
0 Dependency Published SCOTT.T 317353 SCOTT.T 1 13205445199
1 Result Invalid select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444736
SCOTT@test> select * from GV$RESULT_CACHE_DEPENDENCY;
no rows selected
--对比提交前后，可以发现INVALIDATIONS+1，记录的scn从13205444736=>13205445199.
--id=1 的STATUS从Published=> Invalid.
4.打开另外的会话，执行：
--session 2:
SCOTT@test> set autot traceonly stat;
SCOTT@test> execute dbms_flashback.enable_at_system_change_number(13205444736);
PL/SQL procedure successfully completed.
SCOTT@test> select count(*) from t;
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
26 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--回到session 1:
SCOTT@test> SELECT id,TYPE,status,NAME,object_no,cache_id,invalidations,scn FROM v$result_cache_objects;
ID TYPE STATUS NAME OBJECT_NO CACHE_ID INVALIDATIONS SCN
------------ ---------------------------------------- --------- ------------------------------ ------------ -------------------------------------------------- ------------- ------------
0 Dependency Published SCOTT.T 317353 SCOTT.T 1 13205445199
2 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444736
1 Result Invalid select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444736
SCOTT@test> select * from GV$RESULT_CACHE_DEPENDENCY;
no rows selected
--注意看ID=2的情况，又建立新的结果集，加入1条记录scn=13205444736.
--如果回到会话2执行：
--session 2:
SCOTT@test> select count(*) from t;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SCOTT@test> select count(*) from t ;
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--第二次执行我加入一个空格在分号前，这样分析递归增加1次。逻辑读依旧是0.说明执行sql语句的sql_id可以不同。
5.继续测试：
-- scn再提前1点，加1.
--session 2：
SCOTT@test> execute dbms_flashback.enable_at_system_change_number(13205444737);
PL/SQL procedure successfully completed.
SCOTT@test> set autot traceonly stat;
SCOTT@test> select count(*) from t;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
26 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SCOTT@test> select count(*) from t;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--第1次执行存在逻辑读。第2次没有逻辑读。
--session 1：
SCOTT@test> SELECT id,TYPE,status,NAME,object_no,cache_id,invalidations,scn FROM v$result_cache_objects;
ID TYPE STATUS NAME OBJECT_NO CACHE_ID INVALIDATIONS SCN
--- ----------- --------- ------------------------------ ------------ --------------------------- ------------- ------------
0 Dependency Published SCOTT.T 317353 SCOTT.T 1 13205445199
3 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444737
2 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444736
1 Result Invalid select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444736
SCOTT@test> select * from GV$RESULT_CACHE_DEPENDENCY;
no rows selected
--可以发现result cache又增加1条。scn=13205444737.而查询视图GV$RESULT_CACHE_DEPENDENCY为空。
--通过这个测试说明查询旧scn时，GV$RESULT_CACHE_DEPENDENCY并不会增加记录。
--如果这个时候再执行select count(*) from t;会建立新的依赖，视图GV$RESULT_CACHE_DEPENDENCY会增加1条记录。
--session 2:
SCOTT@test> exec DBMS_FLASHBACK.DISABLE;
PL/SQL procedure successfully completed.
SCOTT@test> select count(*) from t;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
25 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--session 1:
SCOTT@test> SELECT id,TYPE,status,NAME,object_no,cache_id,invalidations,scn FROM v$result_cache_objects;
ID TYPE STATUS NAME OBJECT_NO CACHE_ID INVALIDATIONS SCN
--- ----------- --------- ------------------------------ ------------ --------------------------- ------------- ------------
0 Dependency Published SCOTT.T 317353 SCOTT.T 1 13205445199
5 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205447549
4 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444738
3 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444737
2 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444736
1 Result Invalid select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444736
6 rows selected.
SCOTT@test> select * from GV$RESULT_CACHE_DEPENDENCY;
INST_ID RESULT_ID DEPEND_ID OBJECT_NO
------------ ------------ ------------ ------------
1 5 0 317353
--可以发现这时GV$RESULT_CACHE_DEPENDENCY会增加1条依赖记录。
--从以上测试可以说明result cache依赖scn。
--但是通过如下查询：
SCOTT@test> execute dbms_flashback.enable_at_system_change_number(13205447549);
PL/SQL procedure successfully completed.
SCOTT@test> select count(*) from t;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
25 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SCOTT@test> exec DBMS_FLASHBACK.DISABLE;
PL/SQL procedure successfully completed.
--依旧存在逻辑读。
SCOTT@test> SELECT id,TYPE,status,NAME,object_no,cache_id,invalidations,scn FROM v$result_cache_objects;
ID TYPE STATUS NAME OBJECT_NO CACHE_ID INVALIDATIONS SCN
--- ----------- --------- ------------------------------ ------------ --------------------------- ------------- ------------
0 Dependency Published SCOTT.T 317353 SCOTT.T 1 13205445199
6 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205447549
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
5 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205447549
4 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444738
3 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444737
2 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444736
1 Result Invalid select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444736
--依旧会增加1条。
SCOTT@test> SELECT id,TYPE,status,NAME,object_no,cache_id,invalidations,scn,depend_count FROM v$result_cache_objects;
ID TYPE STATUS NAME OBJECT_NO CACHE_ID INVALIDATIONS SCN DEPEND_COUNT
--- ----------- --------- ------------------------------ ------------ --------------------------- ------------- ------------ ------------
0 Dependency Published SCOTT.T 317353 SCOTT.T 1 13205445199 1
6 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205447549 0
5 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205447549 1
4 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444738 0
3 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444737 0
2 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444736 0
1 Result Invalid select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444736 1
7 rows selected.
--仅仅后面DEPEND_COUNT不同，对比id=5，6的记录。而如果查询result cache里面有的scn，逻辑读就是0.测试如下：
SCOTT@test> execute dbms_flashback.enable_at_system_change_number(13205444737);
PL/SQL procedure successfully completed.
SCOTT@test> select count(*) from t;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SCOTT@test> exec DBMS_FLASHBACK.DISABLE;
PL/SQL procedure successfully completed.
--如果查询当前的情况。
SCOTT@test> select count(*) from t;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--逻辑读就是0。
SCOTT@test> SELECT id,TYPE,status,NAME,object_no,cache_id,invalidations,scn,depend_count FROM v$result_cache_objects;
ID TYPE STATUS NAME OBJECT_NO CACHE_ID INVALIDATIONS SCN DEPEND_COUNT
--- ----------- --------- ------------------------------ ------------ --------------------------- ------------- ------------ ------------
0 Dependency Published SCOTT.T 317353 SCOTT.T 1 13205445199 1
6 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205445199 0
5 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205447549 1
4 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444738 0
3 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444737 0
2 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444736 0
1 Result Invalid select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205444736 1
7 rows selected.
SCOTT@test> select * from GV$RESULT_CACHE_DEPENDENCY;
INST_ID RESULT_ID DEPEND_ID OBJECT_NO
------------ ------------ ------------ ------------
1 5 0 317353
--有点乱。
--我的理解：我的理解如果执行当前语句，通过查询CACHE_ID=gp1k5qq6gk8spd439xt91vnwb0,查询最大scn，并且DEPEND_COUNT>0,status=Published.
--再通过(RESULT_ID=5)GV$RESULT_CACHE_DEPENDENCY查询依赖，DEPEND_ID=0.定位v$result_cache_objects id=0的scn，如果此scn小于当前查询时的scn，
--result cache有效。也许我的理解存在错误。