[20170821]Cache Buffers chains与共享模式疑问4.txt
--//昨天别人问的问题,就是在读读模式下,访问相同数据块,11.2.0.4不再出现cache buffers chains latch等待事件.
--//我查询我以前写的博客,链接如下:http://blog.itpub.net/267265/viewspace-1822491/
--//也就是oracle在这样模式下不再采用EXCLUSIVE模式获取cache buffers chains latch.当时的讨论链接如下:
--//http://www.itpub.net/thread-1941761-2-1.html
--//随便解答为什么开始出现latch: shared pool,而在增加会话出现cursor: pin S的问题.
--//时间有点久,重新温习补充一些测试,顺便纠正自己以前理解上的一些错误.
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> select count(*) from (SELECT ADDR FROM V$LATCH_CHILDREN WHERE LOWER (name) LIKE 'cache buffers chains' ORDER BY 1) ;
COUNT(*)
----------
4096
--//我的测试环境共有4096个cache buffers chains.
SCOTT@book> select * from (SELECT ADDR FROM V$LATCH_CHILDREN WHERE LOWER (name) LIKE 'cache buffers chains' ORDER BY 1) where rownum<=10;
ADDR
----------------
0000000084436020
00000000844360E8
00000000844361B0
0000000084436278
0000000084436340
0000000084436408
00000000844364D0
0000000084436598
0000000084436660
0000000084436728
10 rows selected.
--//0x84436020=2219008032
--//0x844360E8=2219008232
--//0x844361B0=2219008432
--//可以发现上下相减就是200.
2.首先按照vage书介绍,如果通过主键或者唯一索引等值访问,Oracle将采用共享CBC Latch，也就是这时多进程同时读同一块，将不会有CBC Latch等待。
并且仅仅获取1次latch,而如果执行语句使用rowid访问,要获取2次latch.
SCOTT@book> select rowid,emp.* from emp where empno=7369;
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAVREAAEAAAACXAAA 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
SCOTT@book> @&r/rowid AAAVREAAEAAAACXAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
87108 4 151 0 0x1000097 4,151 alter system dump datafile 4 block 151 ;
SYS@book> @ &r/bh 4 151
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000084BCF6E0 4 151 1 data block xcur 1 0 0 0 0 0 0000000074538000 EMP
SYS@book> select addr,name,gets,misses,sleeps from v$latch_children where addr=hextoraw('0000000084BCF6E0');
ADDR NAME GETS MISSES SLEEPS
---------------- -------------------- ---------- ---------- ----------
0000000084BCF6E0 cache buffers chains 421 0 0
--//gets=421.
SCOTT@book> select rowid,emp.* from emp where empno=7369;
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAVREAAEAAAACXAAA 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
SYS@book> select addr,name,gets,misses,sleeps from v$latch_children where addr=hextoraw('0000000084BCF6E0');
ADDR NAME GETS MISSES SLEEPS
---------------- -------------------- ---------- ---------- ----------
0000000084BCF6E0 cache buffers chains 422 0 0
--//gets=422.仅仅增加1次. 这种情况下读取方式非常特殊,仅仅获取1次latch.
SCOTT@book> select rowid,emp.* from emp where rowid ='AAAVREAAEAAAACXAAA';
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAVREAAEAAAACXAAA 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
SYS@book> select addr,name,gets,misses,sleeps from v$latch_children where addr=hextoraw('0000000084BCF6E0');
ADDR NAME GETS MISSES SLEEPS
---------------- -------------------- ---------- ---------- ----------
0000000084BCF6E0 cache buffers chains 424 0 0
--//gets=424.仅仅增加2次. 参考链接:http://blog.itpub.net/267265/viewspace-1453630/
逻辑读的过程
1、Oracle以每个块的文件号、块号和类型做HASH运算,得到HASH值。根据HASH值，到HASH表中取出指定块的内存地址
2、获取CBC Latch(实验的重点测试部分)
3、根据HASH值,搜索CBC链表
4、根据DBA找到BH(Buffer Header)加Buffer Pin
5、加完Buffer Pin马上释放CBC Latch
6、访问Buffer开始fetch数据
7、获取CBC Latch
8、释放Buffer Pin
9、释放CBC Latch
3.测试在读读模式下是否会出现cache buffers chains latch.
SCOTT@book> select rowid,empno,'sqlplus -s scott/book @h2 2e6 '||rowid c60 from emp ;
ROWID EMPNO C60
------------------ ---------- ------------------------------------------------------------
AAAVREAAEAAAACXAAA 7369 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAA
AAAVREAAEAAAACXAAB 7499 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAB
AAAVREAAEAAAACXAAC 7521 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAC
AAAVREAAEAAAACXAAD 7566 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAD
AAAVREAAEAAAACXAAE 7654 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAE
AAAVREAAEAAAACXAAF 7698 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAF
AAAVREAAEAAAACXAAG 7782 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAG
AAAVREAAEAAAACXAAH 7788 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAH
AAAVREAAEAAAACXAAI 7839 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAI
AAAVREAAEAAAACXAAJ 7844 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAJ
AAAVREAAEAAAACXAAK 7876 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAK
AAAVREAAEAAAACXAAL 7900 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAL
AAAVREAAEAAAACXAAM 7902 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAM
AAAVREAAEAAAACXAAN 7934 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAN
14 rows selected.
--//建立脚本:
$ cat bbb.sh
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAA
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAB
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAC
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAD
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAE
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAF
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAG
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAH
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAI
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAJ
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAK
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAL
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAM
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAN
$ cat h2.sql
set verify off
declare
m_id number;
m_data varchar2(200);
begin
for i in 1 .. &&1 loop
select ename into m_data from emp where rowid='&&2';
end loop;
end ;
/
quit
--//执行,注我没有使用绑定变量..
SCOTT@book> alter system flush shared_pool;
System altered.
SCOTT@book> select * from emp ;
...
$ . bbb.sh
SCOTT@book> @ &r/wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ----- ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ------------------- --------------- ---------------
000000006010D810 0000000000000150 00 1611716624 336 0 94 507 74 latch: shared pool WAITED SHORT TIME 108 8
000000006010D810 0000000000000150 00 1611716624 336 0 107 399 64 latch: shared pool WAITED SHORT TIME 132 8
000000006010D810 0000000000000150 00 1611716624 336 0 119 163 65 latch: shared pool WAITED SHORT TIME 96 8
000000006010D810 0000000000000150 00 1611716624 336 0 146 33 55 latch: shared pool WAITED SHORT TIME 92 8
000000006010D810 0000000000000150 00 1611716624 336 0 248 21 59 latch: shared pool WAITED SHORT TIME 146 8
000000006010D810 0000000000000150 00 1611716624 336 0 184 23 72 latch: shared pool WAITED SHORT TIME 141 8
000000006010D810 0000000000000150 00 1611716624 336 0 197 2639 79 latch: shared pool WAITED SHORT TIME 138 8
000000006010D810 0000000000000150 00 1611716624 336 0 237 21 79 latch: shared pool WAITED SHORT TIME 115 8
000000006010D810 0000000000000150 00 1611716624 336 0 172 41 58 latch: shared pool WAITED SHORT TIME 124 8
9 rows selected.
--//注:前面可能还看到library cache: mutex X.
--//另外不要以为看到是占用时间很长,实际上很快,如果你多次执行检查WAIT_TIME_MICRO列信息基本不变.
--//这是因为我没有使用绑定变量,瞬间执行这么多存在共享池的少量争用. 不要被一直看到这个等待时间迷惑住.
--//wait.sql脚本如下:
select p1raw,p2raw,p3raw,p1,p2,p3,sid,serial#,seq#,event,state,wait_time_micro,seconds_in_wait from v$session where wait_class<>'Idle'
and sid not in (select sid from v$mystat where rownum=1)
order by event ;
--//如果你在执行以上脚本,因为执行的sql语句已经在共享池,这样就并不会出现等待事件latch: shared pool.
SCOTT@book> @ &r/wait
no rows selected
--//再追加会话(也就是执行脚本bbb.sh),这个时候执行的sql语句就存在争用.这样就会出现cursor: pin S等待事件.
SCOTT@book> @ &r/wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ------------------- --------------- ---------------
00000000FED04CCB 000000D200000002 0000000900000000 4275064011 9.0194E+11 3.8655E+10 2 1 26 cursor: pin S WAITED KNOWN TIME 21989 1
000000005ACF2B33 0000009000000002 0000000900000000 1523526451 6.1848E+11 3.8655E+10 15 1 26 cursor: pin S WAITED KNOWN TIME 21997 1
00000000442BC338 0000008400000001 0000000900000000 1143718712 5.6694E+11 3.8655E+10 55 83 25 cursor: pin S WAITED KNOWN TIME 10520 1
00000000EF787C90 0000006B00000002 0000000900000000 4017650832 4.5956E+11 3.8655E+10 80 49 26 cursor: pin S WAITED KNOWN TIME 10323 1
000000000F1DE22E 00 0000000300000000 253616686 0 1.2885E+10 275 45 31 cursor: pin S WAITED SHORT TIME 2 0
00000000BF44A91D 0000007800000001 0000000300000000 3208947997 5.1540E+11 1.2885E+10 106 23 27 cursor: pin S WAITED KNOWN TIME 10927 1
000000008CF6D633 0000012000000002 0000000900000000 2364986931 1.2370E+12 3.8655E+10 158 17 26 cursor: pin S WAITED SHORT TIME 134 1
00000000FED04CCB 0000000200000001 0000000900000000 4275064011 8589934593 3.8655E+10 210 19 26 cursor: pin S WAITED KNOWN TIME 10715 0
00000000077D57C8 000000AB00000001 0000000300000000 125654984 7.3444E+11 1.2885E+10 261 5 28 cursor: pin S WAITED KNOWN TIME 10366 0
0000000039E57F03 00 0000000300000000 971341571 0 1.2885E+10 94 41 25 cursor: pin S WAITED SHORT TIME 4 0
10 rows selected.
--//这说明一个问题,如果程序大量执行相同的语句,就会在cursor上产生争用,出现cursor: pin S.
--//如果改用绑定变量:
$ cat h2.sql
set verify off
declare
m_id number;
m_rowid varchar2(20);
m_data varchar2(200);
begin
m_rowid := '&2';
for i in 1 .. &&1 loop
/* select ename into m_data from emp where rowid='&&2'; */
select ename into m_data from emp where rowid =m_rowid ;
end loop;
end ;
/
quit
--//这样全部语句就共享相同的光标,问题更加严重.注意看下面的P1都是85994372,这个表示sql语句的hash_value.
SCOTT@book> @ &r/wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ------------------- --------------- ---------------
0000000005202B84 000000AC0000000A 0000000300000000 85994372 7.3873E+11 1.2885E+10 94 525 37 cursor: pin S WAITED SHORT TIME 7 3
0000000005202B84 000000AC0000000A 0000000900000000 85994372 7.3873E+11 3.8655E+10 119 167 43 cursor: pin S WAITED SHORT TIME 6 3
0000000005202B84 0000000000000008 0000000300000000 85994372 8 1.2885E+10 133 71 42 cursor: pin S WAITED SHORT TIME 2 1
0000000005202B84 0000000000000009 0000000900000000 85994372 9 3.8655E+10 146 35 44 cursor: pin S WAITED SHORT TIME 2 0
0000000005202B84 000000AC0000000A 0000000900000000 85994372 7.3873E+11 3.8655E+10 158 25 41 cursor: pin S WAITED KNOWN TIME 11008 3
0000000005202B84 0000000000000009 0000000900000000 85994372 9 3.8655E+10 172 43 53 cursor: pin S WAITED SHORT TIME 2 3
0000000005202B84 000000AC0000000A 0000000300000000 85994372 7.3873E+11 1.2885E+10 276 11 43 cursor: pin S WAITED SHORT TIME 7 3
0000000005202B84 000000AC0000000A 0000000900000000 85994372 7.3873E+11 3.8655E+10 197 2657 49 cursor: pin S WAITED KNOWN TIME 11008 3
0000000005202B84 000000AC0000000A 0000000900000000 85994372 7.3873E+11 3.8655E+10 210 237 29 cursor: pin S WAITED SHORT TIME 15 8
0000000005202B84 0000009200000008 0000000900000000 85994372 6.2707E+11 3.8655E+10 224 23 49 cursor: pin S WAITED SHORT TIME 2 1
0000000005202B84 000001050000000B 0000000900000000 85994372 1.1210E+12 3.8655E+10 237 23 47 cursor: pin S WAITED SHORT TIME 4 0
0000000005202B84 000000AC0000000A 0000000900000000 85994372 7.3873E+11 3.8655E+10 248 23 35 cursor: pin S WAITED SHORT TIME 9 3
0000000005202B84 0000000000000009 0000000900000000 85994372 9 3.8655E+10 261 25 43 cursor: pin S WAITED SHORT TIME 2 0
0000000005202B84 000000AC0000000A 0000000900000000 85994372 7.3873E+11 3.8655E+10 184 25 35 cursor: pin S WAITED SHORT TIME 4 3
14 rows selected.
SCOTT@book> @ &r/ev_name 'cursor: pin S'
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------- ---------------------------------------- -------------------- -------------------- -------------------- ------------- ----------- --------------------
281 352301881 cursor: pin S idn value where 3875070507 4 Concurrency
282 1729366244 cursor: pin S wait on X idn value where 3875070507 4 Concurrency
--//idn 对应sql语句hash_values.
SCOTT@book> select sql_text,sql_id,hash_value,executions from v$sql where hash_value=85994372;
SQL_TEXT SQL_ID HASH_VALUE EXECUTIONS
------------------------------------------------------------ ------------- ---------- ----------
SELECT ENAME FROM EMP WHERE ROWID =:B1 2gvj95w2k0aw4 85994372 27249112
--//而这次看到主要等待事件是cursor: pin S,另外注意一个细节 WAIT_TIME_MICRO值是不断变化的,说明因为执行的sql语句是使用绑定变量,在这里存在争用.
--//如果改用如下,这样相当于每个会话执行的语句不能光标不能共享(注解不一样),这样看到的等待事件应该与第1次测试一样.
$ cat h2.sql
set verify off
declare
m_id number;
m_rowid varchar2(20);
m_data varchar2(200);
begin
m_rowid := '&2';
for i in 1 .. &&1 loop
/* select ename into m_data from emp where rowid='&&2'; */
/* select ename into m_data from emp where rowid =m_rowid ; */
select /*+ &2 */ ename into m_data from emp where rowid =m_rowid ;
end loop;
end ;
/
quit
SCOTT@book> @ &r/wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ----- ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ------------------- --------------- ---------------
000000006010D810 0000000000000150 00 1611716624 336 0 81 761 28 latch: shared pool WAITED SHORT TIME 110 2
000000006010D810 0000000000000150 00 1611716624 336 0 107 413 26 latch: shared pool WAITED SHORT TIME 130 2
000000006010D810 0000000000000150 00 1611716624 336 0 248 25 26 latch: shared pool WAITED SHORT TIME 31 2
000000006010D810 0000000000000150 00 1611716624 336 0 146 37 29 latch: shared pool WAITED SHORT TIME 40 2
000000006010D810 0000000000000150 00 1611716624 336 0 237 25 25 latch: shared pool WAITED SHORT TIME 82 2
000000006010D810 0000000000000150 00 1611716624 336 0 119 169 25 latch: shared pool WAITED SHORT TIME 92 2
6 rows selected.
--//从上面三个测试,都可以发现没有出现cache buffers chains.也就是在读读模式下,不会出现cache buffers chains latch的等待事件.
4.进一步深入:
--// 如果你执行上面的脚本,在使用oradebug观察地址0000000084BCF6E0.
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000000 00000000 17074276 000000B1 00000001 00000712 00000000 00000000 00000010 02A6DA36 00000000 00000000 00000000 00000000 00000000
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000001 00000000 17D7C2E5 000000B1 00000001 00000712 00000000 00000000 00000010 02BB22A7 00000000 00000000 00000000 00000000 00000000
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000003 00000000 189D40E2 000000B1 00000001 00000712 00000000 00000000 00000010 02DDE106 00000000 00000000 00000000 00000000 00000000
--//可以发现地址0x0000000084BCF6E0,前面4个字节会存在一些变动.随便提一下,后面的信息实际上与视图v$latch_children看到的信息相对应.
SYS@book> select addr,name,gets,misses,sleeps from v$latch_children where addr=hextoraw('0000000084BCF6E0');
ADDR NAME GETS MISSES SLEEPS
---------------- -------------------- ---------- ---------- ----------
0000000084BCF6E0 cache buffers chains 496135311 63750503 0
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000000 00000000 1D926C8F 000000B1 00000001 00000712 00000000 00000000 00000010 03CCC167 00000000 00000000 00000000 00000000 00000000
~~~~~~~~ ~~~~~~~~
SYS@book> @ &r/10to16 496135311
10 to 16 HEX REVERSE16
-------------- ------------------
000001d926c8f 0x8f6c921d
SYS@book> @ &r/10to16 63750503
10 to 16 HEX REVERSE16
-------------- ------------------
0000003ccc167 0x67c1cc03
--//注意看下划线内容1D926C8F.
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('84436020', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FC15B5B01F8 66 1 1 1 permanent memor 0000000084434000 3964408 perm 0 00
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('84436728', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FC15B5B0358 66 1 1 1 permanent memor 0000000084434000 3964408 perm 0 00
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('84BCF6E0', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FC15B5AE5B0 58 1 1 1 permanent memor 0000000084834000 3977832 perm 0 00
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('0000000084834000', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FC15B5B0B98 58 1 1 1 permanent memor 0000000084834000 3977832 perm 0 00
00007FC15B5B0B40 59 1 1 1 reserved stoppe 0000000084833FD0 48 R-freea 0 00
--//我不知道为什么看到的KSMCHSIZ存在变化???? 不过这块内存区域应该是perm.已经超出我的知识范围...
5.如何看到cache buffers chains latch,自然是读写,写写模式.
--//改写脚本如何:(仅仅测试写写模式)
$ cat h2.sql
set verify off
declare
m_id number;
m_rowid varchar2(20);
m_data varchar2(200);
begin
m_rowid := '&2';
for i in 1 .. &&1 loop
/* select ename into m_data from emp where rowid='&&2'; */
/* select ename into m_data from emp where rowid =m_rowid ; */
--select /*+ &2 */ ename into m_data from emp where rowid =m_rowid ;
select /*+ &2 */ ename into m_data from emp where rowid =m_rowid for update;
end loop;
end ;
/
quit
SCOTT@book> column event format a28
SCOTT@book> @ &r/wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------- ------------------- --------------- ---------------
0000000000000004 0000000000000097 0000000000000001 4 151 1 81 117 39773 buffer busy waits WAITED SHORT TIME 292 0
0000000000000004 0000000000000097 0000000000000001 4 151 1 94 63 48969 buffer busy waits WAITED SHORT TIME 299 0
0000000000000004 0000000000000097 0000000000000001 4 151 1 107 71 50595 buffer busy waits WAITING 6 0
0000000000000004 0000000000000097 0000000000000001 4 151 1 119 33 40399 buffer busy waits WAITED SHORT TIME 324 0
0000000000000004 0000000000000097 0000000000000001 4 151 1 132 21 41135 buffer busy waits WAITED SHORT TIME 176 0
0000000000000004 0000000000000097 0000000000000001 4 151 1 158 21 57854 buffer busy waits WAITING 137 0
0000000000000004 0000000000000097 0000000000000001 4 151 1 237 19 48746 buffer busy waits WAITING 503 0
0000000000000004 0000000000000097 0000000000000001 4 151 1 248 19 46966 buffer busy waits WAITED SHORT TIME 549 0
0000000000000004 0000000000000097 0000000000000001 4 151 1 184 21 42532 buffer busy waits WAITED SHORT TIME 9 0
0000000000000004 0000000000000097 0000000000000001 4 151 1 197 103 43240 buffer busy waits WAITING 380 0
0000000000000004 0000000000000097 0000000000000001 4 151 1 210 25 44443 buffer busy waits WAITED SHORT TIME 288 0
0000000000000004 0000000000000097 0000000000000001 4 151 1 224 19 52215 buffer busy waits WAITING 186 0
0000000084BCF6E0 00000000000000B1 00 2226976480 177 0 171 21 44199 latch: cache buffers chains WAITED SHORT TIME 2 0
0000000084BCF6E0 00000000000000B1 00 2226976480 177 0 144 23 56962 latch: cache buffers chains WAITED SHORT TIME 3 0
14 rows selected.
--//可以看到 buffer busy waits,latch: cache buffers chains等待事件.
SCOTT@book> @ &r/ev_name 'buffer busy waits'
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------- ---------------------------------------- -------------------- -------------------- -------------------- ------------- ----------- --------------------
95 2161531084 buffer busy waits file# block# class# 3875070507 4 Concurrency
--// 0x97=151 ,正好与块号对上.
SCOTT@book> @ &r/ev_name 'latch: cache buffers chains'
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------- ---------------------------------------- -------------------- -------------------- -------------------- ------------- ----------- --------------------
88 2779959231 latch: cache buffers chains address number tries 3875070507 4 Concurrency
--//address =0000000084BCF6E0 ,正好就是前面的v$latch_children.addr对上.
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000029 20000000 214BC58A 000000B1 00000001 00000706 00000000 00000000 00000228 0696103B 00000434 00000000 02A04F71 00000000 001E2015
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000025 00000000 2150B8E5 000000B1 00000001 00000706 00000000 00000000 00000228 0699C54E 00000438 00000000 02A4042A 00000000 001E48B0
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000022 20000000 21560B2D 000000B1 00000001 000006D0 01000097 00000000 00000235 069DBE5A 0000043A 00000000 02A7C1FE 00000000 001E73BF
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000001 00000000 215B2FB1 000000B1 00000001 0000070F 01000097 00000000 00000239 06A195B5 0000043B 00000000 02ABA03C 00000000 001E9F12
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000000 00000000 21659548 000000B1 00000001 0000077A 00000000 00000000 00000248 06A959E2 00000448 00000000 02B323B2 00000000 001EF435
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000000 00000000 216C34BA 000000B1 00000001 000006DD 01000097 00000000 0000024A 06AE4F8E 0000044C 00000000 02B7D81C 00000000 001F29DB
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000002 00000000 21711904 000000B1 00000001 0000070A 00000000 00000000 0000024E 06B1F640 0000044D 00000000 02BB5D60 00000000 001F51F3
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000023 20000000 21927DA7 000000B1 00000001 00000706 00000000 00000000 00000263 06CAEE7A 0000046F 00000000 02D3263D 00000000 00205F51
6.模式cache buffers chains latch看看?
SYS@book> oradebug poke 0x0000000084BCF6E0 8 0x0000000100000002
BEFORE: [084BCF6E0, 084BCF6E8) = 00000000 00000000
AFTER: [084BCF6E0, 084BCF6E8) = 00000002 00000001
--//8个字节修改顺便反过来.
SCOTT@book> select rowid,emp.* from emp where rowid ='AAAVREAAEAAAACXAAA';
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAVREAAEAAAACXAAA 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
--//并没有出现阻塞.观察前面出现阻塞的都是0x20000000(位置0x0000000084BCF6E4).
SYS@book> oradebug poke 0x0000000084BCF6E0 8 0x2000000000000000
BEFORE: [084BCF6E0, 084BCF6E8) = 00000002 00000001
AFTER: [084BCF6E0, 084BCF6E8) = 00000000 20000000
SCOTT@book> select rowid,emp.* from emp where rowid ='AAAVREAAEAAAACXAAA';
--挂起!!
SYS@book> @ &r/wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ---------- --------------- ---------------
0000000084BCF6E0 00000000000000B1 00 2226976480 177 0 54 29 389 latch: cache buffers chains WAITING 24307480 24
SYS@book> oradebug poke 0x0000000084BCF6E0 8 0x0000000000000000
BEFORE: [084BCF6E0, 084BCF6E8) = 00000000 20000000
AFTER: [084BCF6E0, 084BCF6E8) = 00000000 00000000
SCOTT@book> select rowid,emp.* from emp where rowid ='AAAVREAAEAAAACXAAA';
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAVREAAEAAAACXAAA 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
--//ok!正常..
7.总结:
--//在11.2.0.4下读读不再出现cache buffers chains latch等待事件,当然并意味着逻辑读高的sql语句不需要优化,只不过估计比原来执行快一些.
--//其他东西仅仅是自己乱摸索,切记不要在生产系统做这样的测试!!
--//另外还有一个视图X$KSUPRLAT 观察.select * from X$KSUPRLAT ;
SYS@book> select * from X$KSUPRLAT ;
ADDR INDX INST_ID KSUPRPID KSUPRSID KSUPRLLV KSUPRLTY KSUPRLAT KSUPRLNM KSUPRLMD KSULAWHY KSULAWHR KSULAGTS
---------------- ---- ------- -------- -------- ---------- ---------- ---------------- -------------------- ------------ -------- ---------- ----------
00007FC15B73A4C8 0 1 36 158 1 1 00000000844DFCF8 cache buffers chains MAYBE-SHARED 12584790 1778 31285956
00007FC15B73A4C8 1 1 41 224 1 0 0000000084BCF6E0 cache buffers chains EXCLUSIVE 0 1914 750666907
00007FC15B73A4C8 2 1 42 237 1 0 0000000084BCF6E0 cache buffers chains EXCLUSIVE 16777367 1807 750666911
--//不小心写的有点长,思路有点乱.,,
--//补充测试使用的事件绑定变量与非绑定变量.
$ cat h2.sql
set verify off
column t1 format a20 new_value t1
column t2 format a20 new_value t2
select sysdate t1 from dual ;
declare
m_id number;
m_rowid varchar2(20);
m_data varchar2(200);
begin
m_rowid := '&2';
for i in 1 .. &&1 loop
select ename into m_data from emp where rowid='&&2';
/* select ename into m_data from emp where rowid =m_rowid ; */
--select /*+ &2 */ ename into m_data from emp where rowid =m_rowid ;
--select /*+ &2 */ ename into m_data from emp where rowid =m_rowid for update;
end loop;
end ;
/
select sysdate t2 from dual ;
spool /tmp/aa append
select ( to_date('&t2','yyyy-mm-dd hh24:mi:ss') - to_date('&t1','yyyy-mm-dd hh24:mi:ss'))*86400 n,'&&2' c20 from dual ;
spool off
quit
$ grep ' AAA' /tmp/aa.lst
40 AAAVREAAEAAAACXAAM
40 AAAVREAAEAAAACXAAL
41 AAAVREAAEAAAACXAAE
41 AAAVREAAEAAAACXAAN
41 AAAVREAAEAAAACXAAD
41 AAAVREAAEAAAACXAAK
41 AAAVREAAEAAAACXAAI
43 AAAVREAAEAAAACXAAH
45 AAAVREAAEAAAACXAAA
46 AAAVREAAEAAAACXAAG
47 AAAVREAAEAAAACXAAF
56 AAAVREAAEAAAACXAAC
56 AAAVREAAEAAAACXAAB
57 AAAVREAAEAAAACXAAJ
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
49 AAAVREAAEAAAACXAAN
49 AAAVREAAEAAAACXAAE
49 AAAVREAAEAAAACXAAG
50 AAAVREAAEAAAACXAAD
50 AAAVREAAEAAAACXAAF
50 AAAVREAAEAAAACXAAH
50 AAAVREAAEAAAACXAAL
53 AAAVREAAEAAAACXAAI
54 AAAVREAAEAAAACXAAC
55 AAAVREAAEAAAACXAAK
55 AAAVREAAEAAAACXAAA
56 AAAVREAAEAAAACXAAB
63 AAAVREAAEAAAACXAAM
64 AAAVREAAEAAAACXAAJ
--//分割线下面是使用绑定变量的情况,也就是调用select ename into m_data from emp where rowid =m_rowid ;
--//可以大量调用相同的sql语句,存在光标争用,执行时间反而增加,这种情况在优化中一样要注意,频繁执行相同的sql语句.
