[20151014]关于result cache.txt
--11G 开始支持result cache,把执行的结果保存在共享池,从而一定程度减少逻辑读,而那些对象或者那些语句适合做result cache呢?
--自己在前一段时间想把一些表设置RESULT_CACHE为MODE FORCE,访问这些对象时,可以利用result cache模式。
--另外我本来想一些语句通过sql patch的方式加入提示/*+ result_cache */ ,结果不成功。
--参考链接:http://blog.itpub.net/267265/viewspace-1377511/
--那些对象可以这样呢?
--我个人觉得那些执行频次很高的sql语句以及"只读"表(指很少修改的表)做为首选。测试看看:
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.
2.测试:
--注:为了测试方便,打开2个会话,一个设置了set autot traceonly stat;另外一个用来查询相关视图以及一些dml语句。
--session 2:
SCOTT@test> variable x number ;
SCOTT@test> exec :x :=1;
PL/SQL procedure successfully completed.
SCOTT@test> set autot traceonly stat;
SCOTT@test> select * from t where id=:x;
Statistics
----------------------------------------------------------
34 recursive calls
0 db block gets
34 consistent gets
0 physical reads
0 redo size
787 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SCOTT@test> select * from t where id=:x;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
787 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
--可以发现第2次执行逻辑读已经为0.说明使用result cache.
3.检查相关视图:
--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 0 13205305992
1 Result Published select * from t where id=:x 0 cxm3q1hv157ps2t68sgc4wp3cg 0 13205305992
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
SCOTT T 317353 317353
--可以发现依赖的对象的OBJECT_NO=317353,也就是表T。而不是索引(我以为也会依赖索引)。
4.现在修改id=1000;
--session 1:
SCOTT@test> update t set name=name where id=1000;
1 row updated.
SCOTT@test> commit ;
Commit complete.
--session 2:
SCOTT@test> select * from t where id=:x;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
787 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 * from t where id=:x;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
787 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
--可以发现存在逻辑读,这从一个侧面说明如果这个对象存在修改,会直接导致result cache失效。依赖的对象是表,而不是数据块(感
--觉如果这样代价有点高)。
5.继续测试:
--session 2:
SCOTT@test> select count(*) from t ;
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
25 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
--这个语句执行计划应该使用主键索引。
--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 13205306169
3 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205306238
2 Result Published select * from t where id=:x 0 cxm3q1hv157ps2t68sgc4wp3cg 0 13205306171
1 Result Invalid select * from t where id=:x 0 cxm3q1hv157ps2t68sgc4wp3cg 0 13205305992
SCOTT@test> select * from GV$RESULT_CACHE_DEPENDENCY;
INST_ID RESULT_ID DEPEND_ID OBJECT_NO
------------ ------------ ------------ ------------
1 3 0 317353
1 2 0 317353
--即使执行select count(*) from t ;,我开始以为这样会依赖主键索引,结果依赖的还是表T。
--说明一下:RESULT_ID=3的记录,对应DEPEND_ID=0,而SELECT * FROM v$result_cache_objects where DEPEND_ID=0的记录表scott.T .
--这样说明即使存在DML语句也会导致result cache失效。
6.修改记录看看:
-- session 1:
SCOTT@test> select * from t where id=999 for update ;
ID NAME
--- -----
999 test
SCOTT@test> commit ;
Commit complete.
-- session 2:
SCOTT@test> select count(*) from t ;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
25 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
--说明:我选择select .. for update 测试,结果一样。result cache失效。
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 3 13205306490
5 Result Published select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205306493
1 Result Invalid select * from t where id=:x 0 cxm3q1hv157ps2t68sgc4wp3cg 0 13205305992
2 Result Invalid select * from t where id=:x 0 cxm3q1hv157ps2t68sgc4wp3cg 0 13205306171
3 Result Invalid select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205306238
4 Result Invalid select count(*) from t 0 gp1k5qq6gk8spd439xt91vnwb0 0 13205306433
6 rows selected.
--我感觉依赖的本质是scn。继续测试就可以看到问题了:
7.修改记录然后回滚。
SCOTT@test> update t set name=name where id=998;
1 row updated.
SCOTT@test> rollback;
Rollback complete.
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
-- result cache 有效!
SCOTT@test> update t set name=name where id=998;
1 row updated.
--不提交
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
--我没有提交或者执行了rollback,都不会影响结果集。
SCOTT@test> commit ;
Commit complete.
SCOTT@test> select count(*) from t ;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
25 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
--consistent gets=25。结果集失效。
8.总结:
---从以上测试,可以说明result cache 使用范围仅仅合适频繁执行,涉及对象表少量修改的语句。或者也许一些数据仓库DML结果集很少变
--化的情况。这样才能一定程度减少逻辑读,提高性能。
--另外可以通过查询视图确定v$result_cache_objects,确定那些对象不合适做result cache。
--看看生产系统:
> SELECT id,TYPE,status,NAME,object_no,cache_id,invalidations,scn FROM v$result_cache_objects where INVALIDATIONS>=1000;
ID TYPE STATUS NAME OBJECT_NO CACHE_ID INVALIDATIONS SCN
------- ----------- --------- -------------------- ------------ ------------------- ------------- ------------
336708 Dependency Published XXXXXX_XXX.YF_DB01 96396 XXXXXX_XXX.YF_DB01 8420 13860014605
39171 Dependency Published XXXXXX_XXX.GY_XTCS 94089 XXXXXX_XXX.GY_XTCS 318486 13860074094
39283 Dependency Published XXXXXX_XXX.GY_YHCS 94111 XXXXXX_XXX.GY_YHCS 499397 13860074730
3 Dependency Published XXXXXX_XXX.GY_YGDM 94105 XXXXXX_XXX.GY_YGDM 4094 13859061872
--这些对象也许不适合修改表的result cache属性为force。当然要仔细评估。我们的系统刷屏语句实在太厉害了。
--我简单写一个脚本:
$ cat d_buffer.sql
col executions1 new_value x1
col buffer_gets1 new_value x2
col ELAPSED_TIME1 new_value x3
col ROWS_PROCESSED1 new_value x4
col executions2 new_value y1
col buffer_gets2 new_value y2
col ELAPSED_TIME2 new_value y3
col ROWS_PROCESSED2 new_value y4
select executions executions1,buffer_gets buffer_gets1,elapsed_time elapsed_time1,rows_processed rows_processed1 from v$sqlarea where sql_id='&&1';
prompt ... sleep &&2 , waiting ....
host sleep &&2
select executions executions2,buffer_gets buffer_gets2,elapsed_time elapsed_time2 ,rows_processed rows_processed2 from v$sqlarea where sql_id='&&1';
select (&y2-&x2)/(&y1-&x1) "每次buffer_gets",&y1- &x1 "执行次数" , (&y3-&x3)/(&y1-&x1) "每次执行时间" , (&y4-&x4)/(&y1-&x1) "平均处理记录数" from dual ;
SQL> select sql_id,sql_text,executions from v$sqlarea where sql_id='ff511u9ndctfr';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------------ ------------
ff511u9ndctfr Select CSZ From GY_XTCS Where CSMC =:1 250432
SQL> @d_buffer ff511u9ndctfr 10
EXECUTIONS1 BUFFER_GETS1 ELAPSED_TIME1 ROWS_PROCESSED1
------------ ------------ ------------- ---------------
255148 200085 15952863 255150
... sleep 10 , waiting ....
EXECUTIONS2 BUFFER_GETS2 ELAPSED_TIME2 ROWS_PROCESSED2
------------ ------------ ------------- ---------------
255657 200397 15980172 255659
每次buffer_gets 执行次数 每次执行时间 平均处理记录数
--------------- ------------ ------------ --------------
.61296660118 509 53.652259332 1
--10内执行509次,平均下来50次/秒。真不知道开发什么写代码的????