邏輯讀産生Cache Buffer Chain(簡稱CBC) Latch的解析

測試環境：版本11gR2

SQL> select * from v$version where rownum=1;

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

一、邏輯讀的過程

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

二、取T1表的第一行資料及ROWID，根據dbms_rowid包查出這行資料的檔案号、塊号

SQL> select rowid,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,id,name from gyj.t1　where rownum=1;

ROWID FILE# BLOCK# ID NAME

AAASP9AAGAAAACHAAA 6 135 1 aaaaa

這裡的DBA(Data Block Address)就是由6号檔案和135号塊組成

三、根據檔案号塊号擷取CBC Latch的位址

SQL> select hladdr from x$bh where file#=6 and dbablk=135;

　　HLADDR

00000003A4282A90

四、根據CBC Latch的位址可以查出這個CBC Latch被獲得的次數

1.擷取CBC Latch的次數為245

SQL> select addr,name,gets from v$latch_children where addr='00000003A4282A90';

ADDR NAME GETS

00000003B09F7C58 cache buffers chains 245

2.再次讀取T1表的第一資料，即産生一次邏輯讀

SQL>select id,name from gyj.t1 where rowid='AAASP9AAGAAAACHAAA';

ID　　　　　　NAME

-------- ------------

　　　　1　　　　　gyj1

3.CBC Latch的次數變為247，說明一次邏輯讀産生兩次CBC Latch

00000003A4282A90 cache buffers chains 247

五、查本會話下的程序号

SQL> select spid from v$session s,v$process b where s.paddr=b.addr and s.sid in(select sid from v$mystat where rownum=1);

SPID

2416

六、利用Dtrace工具跟蹤

１.編寫個簡單的dtrace腳本

vi dtrace.d

!/usr/sbin/dtrace -s -n

dtrace:::BEGIN

{

i=1;           

}

pid$1:::entry

printf("i=%d PID::entry:==%s:%s:%s:%s %x %x %x %d %x %x",i, probeprov, probemod, probefunc, probename,arg0,arg1,arg2,arg3,arg4,arg5);
    i=i+1;           

２.授權限

chmod 755 dtrace.d

３.執行dtrace指令生成跟蹤日志

./dtrace.d -x switchrate=1hz -b 32m 2416　> dtracecbc.log --用這條指令加大了緩存,避免丢失日志

４.顯示主要dtrace部分日志，在dtracecbc.log中搜尋latch位址:3A4282A90

CPU ID FUNCTION:NAME

--邏輯讀開始

1 63919 kcbgtcr:entry i=592 PID::entry:==pid2416:oracle:kcbgtcr:entry fffffd7ffc98bc00 0 2fe 0 23dede0 fffffd7fffdfa7a0

--擷取CBC Latch

1 128720 sskgslcas:entry i=593 PID::entry:==pid2416:oracle:sskgslcas:entry 3a4282a90 0 1 0 0 3a4c953d0　　　　　　　　　　

1 60714 ktrexf:entry i=594 PID::entry:==pid2416:oracle:ktrexf:entry fffffd7fffdfa7a0 d7fcd60 0 15392235540 1fe8 23dc9f0

1 64185 kcbzgs:entry i=595 PID::entry:==pid2416:oracle:kcbzgs:entry 1 d7fcd60 0 0 0 23dc9f0

1 53939 kssadf_numa_intl:entry i=596 PID::entry:==pid2416:oracle:kssadf_numa_intl:entry 23 3a4145900 3a487d9e8 0 0 1d

--根據DBA找到BH(Buffer Header)加Buffer Pin

1 128720 sskgslcas:entry i=597 PID::entry:==pid2416:oracle:sskgslcas:entry 3957f6280 0 1 0 3957f6238 3957f6238

--加完Buffer Pin馬上釋放CBC Latch

1 128723 sskgsldecr:entry i=598 PID::entry:==pid2416:oracle:sskgsldecr:entry 3a4282a90 1 1 0 1 2000000000000000

1 63951 kcbcge:entry i=599 PID::entry:==pid2416:oracle:kcbcge:entry fffffd7ffc98bc00 0 77 0 fffffd7ffc98b7dc 0

1 64116 kcbds2pbh:entry i=600 PID::entry:==pid2416:oracle:kcbds2pbh:entry fffffd7ffc98bc00 0 77 0 fffffd7ffc98b7dc 0

1 60179 ktcckv:entry i=601 PID::entry:==pid2416:oracle:ktcckv:entry fffffd7ffc98bc00 fffffd7ffc98b7d4 77 0 3a3c935f0 0

1 104774 kafger:entry i=602 PID::entry:==pid2416:oracle:kafger:entry 39572e064 fffffd7fffdfb200 39d84e4f0 2 1 1

1 83919 kpofcr:entry i=603 PID::entry:==pid2416:oracle:kpofcr:entry fffffd7fffdfb4b0 1 39d84e4f0 2 4c30d40 3a4fd3790

1 124023 ttcrxh:entry i=604 PID::entry:==pid2416:oracle:ttcrxh:entry d8055e8 d7f58f0 fffffd7fffdfafe0 48 214 1

1 209006 memcpy:entry i=605 PID::entry:==pid2416:libc.so.1:memcpy:entry ddfa329 fffffd7fffdfafe0 30 48 ddfa359 d7f5a00

1 209006 memcpy:entry i=606 PID::entry:==pid2416:libc.so.1:memcpy:entry ddfa35b 39572fff5 2 2 2 d7f5a00

1 123964 ttcc2u:entry i=607 PID::entry:==pid2416:oracle:ttcc2u:entry d8055e8 d7f58f0 39572fff8 4 1 1

1 209006 memcpy:entry i=608 PID::entry:==pid2416:libc.so.1:memcpy:entry ddfa35e 39572fff8 4 852 d7f5a00 d7f5a00

1 61112 ktsmg_max_query:entry i=609 PID::entry:==pid2416:oracle:ktsmg_max_query:entry 0 0 39572fffc 0 d805250 2

1 211309 gethrtime:entry i=610 PID::entry:==pid2416:libc.so.1:gethrtime:entry 92d 1a 39572fffc 8 3a4447d80 39de97dc8

1 128314 slcpums:entry i=611 PID::entry:==pid2416:oracle:slcpums:entry d805218 1a 494b3f016 8 3a443a460 39c253698

1 211401 times:entry i=612 PID::entry:==pid2416:libc.so.1:times:entry fffffd7fffdfb240 1a 494b3f016 8 3a443a460 39c253698

1 131260 kglHandleInvalidations:entry i=613 PID::entry:==pid2416:oracle:kglHandleInvalidations:entry d8055e8 39df04bb0 0 8 d7f1a10 39c2d1140

1 131261 kglHandleLoads:entry i=614 PID::entry:==pid2416:oracle:kglHandleLoads:entry d8055e8 39df04bb0 0 8 d7f1a10 39c2d1140

1 91756 kksGetStats:entry i=615 PID::entry:==pid2416:oracle:kksGetStats:entry 39d738198 39df04bb0 0 8 d7f1a10 39c2d1140

1 72794 kews_sqlcol_end:entry i=616 PID::entry:==pid2416:oracle:kews_sqlcol_end:entry 3a4fd2210 fffffd7ffc9e1ad8 39d737e88 70 1 1

1 110094 qecrlssub:entry i=617 PID::entry:==pid2416:oracle:qecrlssub:entry 39d84ed10 fffffd7ffc9e1ad8 9 0 fffffd7ffc98ac58 0

1 108487 qertbRelease:entry i=618 PID::entry:==pid2416:oracle:qertbRelease:entry 39d84dea8 fffffd7ffc98bae0 9 0 4c0 dbb56b0

1 104779 kafcpy:entry i=619 PID::entry:==pid2416:oracle:kafcpy:entry 39d84e4f0 2 9 0 fffffd7ffc987430 20

--通路Buffer開始fetch資料

1 209006 memcpy:entry i=620 PID::entry:==pid2416:libc.so.1:memcpy:entry fffffd7ffc9f49f8 39572fff8 4 0 30 20

1 209006 memcpy:entry i=621 PID::entry:==pid2416:libc.so.1:memcpy:entry fffffd7ffc9f49e0 39572fff5 2 1 30 20

1 63927 kcbipnns:entry i=622 PID::entry:==pid2416:oracle:kcbipnns:entry fffffd7ffc98bc00 39572fff5 39572fff7 0 fffffd7ffc98bbe8 2c1

--釋放Buffer Pin，調用函數sskgslcas(目的是擷取CBC Latch)

　

1 63940 kcbrls:entry i=623 PID::entry:==pid2416:oracle:kcbrls:entry fffffd7ffc98bc00 39572fff5 39572fff7 0 3a3c935f0 2c1

1 128720 sskgslcas:entry i=624 PID::entry:==pid2416:oracle:sskgslcas:entry 3a4282a90 0 200000000000001d 9 3a4c95288 2000000000000000

1 64198 kcbzar:entry i=625 PID::entry:==pid2416:oracle:kcbzar:entry 3a3c93670 3a4282a90 108000 -2748781186960 0 10

--釋放CBC Latch

1 128723 sskgsldecr:entry i=626 PID::entry:==pid2416:oracle:sskgsldecr:entry 3a4282a90 200000000000001d fffffffd 1 2000000000000000 1d

５.利用oradebug工具，邏輯讀擷取CBC Latch

SQL> oradebug setmypid

Statement processed.

SQL> oradebug peek 0x3a4282a90 4 --查0x3a4282a90位址開始的4位元組資訊的值為0

[3A4282A90, 3A4282A94) = 00000000

SQL> oradebug poke 0x3a4282a90 4 1　　 　 --修改0x3a4282a90位址開始的4位元組資訊的值為1,相當于擷取了Latch

BEFORE: [3A4282A90, 3A4282A94) = 00000000 --修改前的值

AFTER: [3A4282A90, 3A4282A94) = 00000001 --修改後的值

SQL> oradebug peek 0x3a4282a90 4　　　 --查0x3a4282a90位址開始的4位元組資訊的值為1　　

[3A4282A90, 3A4282A94) = 00000001

６.再開一個會話,會話号為13：

select sid from v$mystat where rownum=1;

SID

13
           

７.在13會話下再查詢T1表的第一行，這時已被阻塞

select id,name from gyj.t1 where rowid='AAASP9AAGAAAACHAAA';

８.再開一個會話，檢視會話号是13的等待事件，産生了CBC Latch的等待事件

select sid,event,p1raw,p2raw,p3raw from v$session where sid=13;

SID EVENT P1RAW P2RAW P3RAW

13 latch: cache buffers chains                                      00000003A4282A90 0000000000000096 00