前面一个帖子,探讨了一些11G result cache的一些可能没被人所注意到的一些发现。
http://space.itpub.net/15415488/viewspace-615398
而这个帖子是在经过team meeting大家提出的疑问之后,
而对result cache做出的一些更加深入的研究和分析。
在team meeting中同事提出了一些问题,而这里先对这些问题做出一些解答。
--------------FAQ Part---------------
Q1:更新其他的行、或者更新其他block中的行 会不会invalidate 这个result cache?
A1:会。任何DML/DDL(甚至包括grant)都会使基于这个object的result cache invalidate。
所以说,result cache他的依耐性是object level的,既不是row level的,也不是block level的。
这样的话,我认为result cache只有对那些在平时几乎没有任何DML的只读表比较有用。
Q2:result cache使用什么latch保护?是不是用的shared pool latch?
A2:在平时读取阶段不是使用的shared pool latch,而是使用的result cache latch。
SQL> select * from v$latchname where name like 'Result Cache%';
LATCH# NAME HASH
---------- ------------------------------ ----------
373 Result Cache: Latch 1545889529
374 Result Cache: SO Latch 986859868
Q3:result cache有enqueue/lock保护么?
A3:是的。RC enqueue就是拿来保护并发修改的。
例如当我们创建一个新的result cache时。
SQL> select distinct ksqsttyp,ksqstexpl from x$ksqst where KSQSTEXPL like '%result%' order by ksqsttyp;
KS
--
KSQSTEXPL
--------------------------------------------------------------------------------
RC
Coordinates access to a result-set
Q4:使用result cache有什么overhead么?
A4:我做了一个简单的实验来得到他的overhead:
SQL> set timing on
SQL> exec sys.runstats_pkg.rs_start;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.19
-- 下面的PLSQL不使用result cache来查询100000次。
SQL> DECLARE
2 v_name varchar2(100);
3 BEGIN
FOR i IN 1 .. 100000 LOOP
select name into v_name from testbyhao where id=1000;
END LOOP;
END;
/ 4 5 6 7 8
PL/SQL procedure successfully completed.
Elapsed: 00:00:10.31
SQL> exec sys.runstats_pkg.rs_middle;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
--下面的PLSQL我使用result cache来查询100000次。
SQL> DECLARE
2 v_name varchar2(100);
3 BEGIN
FOR i IN 1 .. 100000 LOOP
select /*+result_cache*/ name into v_name from testbyhao where id=1000;
END LOOP;
END;
/ 4 5 6 7 8
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.85
--然后下面对比两种方式的statistics的差异(只取差值>1000的)
SQL> exec sys.runstats_pkg.rs_stop(1000);
Run1 ran in 1565 hsecs
Run2 ran in 1603 hsecs
run 1 ran in 97.63% of the time
Name Run1 Run2 Diff
STAT...session uga memory 8,144 3,880 -4,264
STAT...calls to kcmgrs 100,044 54 -99,990
STAT...rows fetched via callba 100,000 2 -99,998
STAT...table fetch by rowid 100,000 2 -99,998
STAT...index fetch by key 100,000 2 -99,998
LATCH.Result Cache: Latch 0 200,003 200,003
STAT...session logical reads 300,035 44 -299,991
STAT...consistent gets 300,011 19 -299,992
STAT...consistent gets from ca 300,011 19 -299,992
STAT...consistent gets - exami 300,005 12 -299,993
STAT...buffer is not pinned co 300,000 6 -299,994
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
1,349 201,389 200,040 .67%
PL/SQL procedure successfully completed.
我们可以很清楚地看到,在仅仅全部读取result cache的情况下,
所带来的overhead只在于更多的result cache latch。
而这个result cache latch他到底有多少个呢?
答案是1个,因为他们没有latch children。
SQL> select count(*)2 from v$latch_children
3 where name='Result Cache: Latch';
COUNT(*)
----------
0
--------------End of FAQ Part---------------
--------------Viadea's分割线-----------------
-------------Other Finding Part--------------
经过阅读国外很多牛人的实验方法,再通过自己切身实验,得到更多更深入的finding,
于是姑且将一些精华的东西分享给大家。
1.Trace result cache的event是:
ALTER SESSION SET EVENTS '43905 trace name context forever, level 1';
得到的结果类似于:
*** 2009-09-28 06:45:46.101
Objects for this node : 24006
-------------------------------------
Query[len=44]: SELECT /*+result_cache*/ NAME FROM TESTBYHAO
Normalized Query[len=25]: 172 670 70 1?"TESTBYHAO"
Cache id1: at33rkq5njw4z12qkkp17rwctj
Cache id2: at33rkq5njw4z12qkkp17rwctj
Column count: 1
NLS Dependent: NO User Referenced: NO Ordered: NO Auto: NO
Dependencies: (24006 - HAOZHU_USER.TESTBYHAO)
2.result cache 的background process是:
SQL> select name,description from V$BGPROCESS where name like 'RC%';
NAME DESCRIPTION
----- ----------------------------------------------------------------
RCBG Result Cache: Background
3.每次存取(读) result cache会得到2次latch。
SQL> select gets, misses, sleeps, wait_time
2 from v$latch
3 where name = 'Result Cache: Latch';
GETS MISSES SLEEPS WAIT_TIME
---------- ---------- ---------- ----------
601597 0 0 0
Elapsed: 00:00:00.01
SQL> DECLARE
2 v_name varchar2(100);
3 BEGIN
FOR i IN 1 .. 100000 LOOP
select /*+result_cache*/ name into v_name from testbyhao where id=1000;
END LOOP;
END;
/ 4 5 6 7 8
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.77
SQL> select gets, misses, sleeps, wait_time
2 from v$latch
3 where name = 'Result Cache: Latch';
GETS MISSES SLEEPS WAIT_TIME
---------- ---------- ---------- ----------
801597 0 0 0
Elapsed: 00:00:00.01
SQL>
SQL> select (801597-601597)/100000 from dual;
(801597-601597)/100000
----------------------
2
4.每次修改(例如新建一个result cache)会得到4次latch:
SQL> select count(*) from testbyhao;
COUNT(*)
----------
4000
SQL> select gets, misses, sleeps, wait_time
2 from v$latch
3 where name = 'Result Cache: Latch';
GETS MISSES SLEEPS WAIT_TIME
---------- ---------- ---------- ----------
817699 0 0 0
Elapsed: 00:00:00.01
SQL> DECLARE
2 v_name varchar2(100);
3 begin
for cur in (select * from testbyhao)
loop
select /*+result_cache*/ name into v_name from testbyhao where id=cur.id;
end loop;
end; 4 5 6 7 8
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.75
SQL> select gets, misses, sleeps, wait_time
2 from v$latch
3 where name = 'Result Cache: Latch';
GETS MISSES SLEEPS WAIT_TIME
---------- ---------- ---------- ----------
833698 0 0 0
Elapsed: 00:00:00.01
SQL> select (833698-817699)/4000 from dual;
(833698-817699)/4000
--------------------
3.99975
5.重要参数_result_cache_timeout和enq: RC - Result Cache: Contention的关系。
_result_cache_timeout默认值是60s,代表着一个session等待得到result cache多少秒。
下面的例子非常经典,我拿来主义自己测了一把。
--第一个session我让他试图创建一个result cache,但让他一直保持“NEW”的状态。
--这样其他session的同一条语句就没法获得这个enqueue。
Session A:
SQL> variable rc refcursor;
SQL> exec open :rc for select /*+ result_cache */ id from testbyhao2;
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> exec rc_fetch(:rc);
1469
PL/SQL procedure successfully completed.
--我们可以看到Session A拿到了这个RC enqueue,没有释放.
SQL> select sid, type, id1, id2, lmode
2 from v$lock
where sid=sys_context('userenv', 'sid'); 3
SID TY ID1 ID2 LMODE
---------- -- ---------- ---------- ----------
318 RC 1 1 6
318 AE 99 0 4
--从v$result_cache_objects,可以看到当前这个result cache的状态时NEW,并不是published。
SQL> /
STATUS NAME PIN_COUNT SCAN_COUNT
--------- -------------------------------------------------------------------------------------------------------------------------------- ---------- ----------
CACHE_ID
---------------------------------------------------------------------------------------------
CACHE_KEY
---------------------------------------------------------------------------------------------
Published HAOZHU_USER.TESTBYHAO2 0 0
HAOZHU_USER.TESTBYHAO2
HAOZHU_USER.TESTBYHAO2
New SELECT /*+ result_cache */ ID FROM TESTBYHAO2 2 0
0632rwbj2n03pfp9gymdv0ck68
6s5papz2k5hw4dkgus2h8yx9jr
Session B:
--从Session B再run同一条语句,而这条语句会试图得到RC enqueue
SQL> set serveroutput on
SQL> set timing on
SQL> variable rc refcursor;
SQL> exec open :rc for select /*+ result_cache */ id from testbyhao2;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> exec rc_fetch(:rc);
1469
PL/SQL procedure successfully completed.
Elapsed: 00:01:01.38
--我们可以看到Session B在执行如此简单的一条语句的时候居然等待了60s,
--而这个时间就是我们的_result_cache_timeout。
--当Session B没能获得他所希望的RC enqueue之后,他就会Bypass这个result cache,
--然后自己使用正常的执行方式获得想要的答案。
--在Session B等待的这60s中,我看到了所希望看到的result cache contention。
TEST oracle@xxx:/export/home/oracle > ora10 active
9:16am up 264 day(s), 11:19, 10 users, load average: 0.34, 0.32, 0.32
SID USERNAME MACHINE EVENT PARAM W WT SQL ST LT LOGON_TIME STATE
------ ---------- ------------------------------ ------------------------------ -------------------- ---- ---- ------------------------ -- ------ ---------- -------------------
636 HAOZHU_USE sqlplus@xxx enq: RC - Result Cache: Conten 1380122628/1/1 0 4 2393857412/1029988163 A 15 53 WAITING
SQL> /
STATUS NAME PIN_COUNT SCAN_COUNT
--------- -------------------------------------------------------------------------------------------------------------------------------- ---------- ----------
CACHE_ID
---------------------------------------------------------------------------------------------
CACHE_KEY
---------------------------------------------------------------------------------------------
Published HAOZHU_USER.TESTBYHAO2 0 0
HAOZHU_USER.TESTBYHAO2
HAOZHU_USER.TESTBYHAO2
Bypass SELECT /*+ result_cache */ ID FROM TESTBYHAO2 1 0
0632rwbj2n03pfp9gymdv0ck68
6s5papz2k5hw4dkgus2h8yx9jr
所以,result cache作为一个足够吸引人的new feature,也带来了一个新的现象:
Readers blocks Readers。
为了降低这种block的几率,DBA可以根据自己一段时间的monitor的数据,适当的降低_result_cache_timeout的大小。
6.为了感谢各位的捧场,特地再奉上一本讲result cache internal的PPT。
里面主要讲解了result cache的内存结构、算法的更加深入的东西。
喜欢研究更细致的同学可以看看。
http://www.juliandyke.com/Presentations/ResultCacheInternals.ppt
----------End of Other Finding Part----------
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15415488/viewspace-615873/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15415488/viewspace-615873/