逻辑读产生Cache Buffer Chain(简称CBC) Latch的解析

最新推荐文章于 2020-11-11 10:59:53 发布

深圳gg

最新推荐文章于 2020-11-11 10:59:53 发布

阅读量1.7k

点赞数

分类专栏： oracle内部原理

oracle内部原理专栏收录该内容

27 篇文章 0 订阅

订阅专栏

测试环境：版本11gR2
SQL> select * fromv$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11gEnterprise 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> selectrowid,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 fromx$bh where file#=6 and dbablk=135;
　　HLADDR
----------------
00000003A4282A90

四、根据CBC Latch的地址可以查出这个CBC Latch被获得的次数
1.获取CBC Latch的次数为245
SQL> select addr,name,getsfrom v$latch_children  where addr='00000003A4282A90';
ADDR         NAME                                                              GETS
-------------------------------------------------------------------------------- ----------
00000003B09F7C58cache buffers chains                                                245
2.再次读取T1表的第一数据，即产生一次逻辑读
SQL>select id,name fromgyj.t1 where rowid='AAASP9AAGAAAACHAAA';
ID　　　　　　NAME
-------- ------------
　　　　1　　　　　gyj1
3.CBC Latch的次数变为247，说明一次逻辑读产生两次CBC Latch
SQL> select addr,name,getsfrom v$latch_children  where addr='00000003A4282A90';
ADDR         NAME                                                              GETS
-------------------------------------------------------------------------------- ----------
00000003A4282A90 cache bufferschains                                               247

五、查本会话下的进程号
SQL> select spid fromv$session s,v$process b where s.paddr=b.addr and s.sid in(select sid fromv$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 00 23dc9f0
  1  53939          kssadf_numa_intl:entry i=596PID::entry:==pid2416:oracle:kssadf_numa_intl:entry 23 3a4145900 3a487d9e80 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=598PID::entry:==pid2416:oracle:sskgsldecr:entry 3a4282a901 1 0 1 2000000000000000
  1  63951                   kcbcge:entry i=599 PID::entry:==pid2416:oracle:kcbcge:entryfffffd7ffc98bc00 0 77 0 fffffd7ffc98b7dc 0
  1  64116                kcbds2pbh:entry i=600 PID::entry:==pid2416:oracle:kcbds2pbh:entryfffffd7ffc98bc00 0 77 0 fffffd7ffc98b7dc 0
  1  60179                   ktcckv:entry i=601 PID::entry:==pid2416:oracle:ktcckv:entryfffffd7ffc98bc00 fffffd7ffc98b7d4 77 0 3a3c935f0 0
  1 104774                   kafger:entry i=602 PID::entry:==pid2416:oracle:kafger:entry 39572e064fffffd7fffdfb200 39d84e4f02 1 1
  1  83919                   kpofcr:entry i=603 PID::entry:==pid2416:oracle:kpofcr:entryfffffd7fffdfb4b0 1 39d84e4f02 4c30d40 3a4fd3790
  1 124023                   ttcrxh:entry i=604 PID::entry:==pid2416:oracle:ttcrxh:entry d8055e8 d7f58f0fffffd7fffdfafe0 48 214 1
  1 209006                   memcpy:entryi=605 PID::entry:==pid2416:libc.so.1:memcpy:entry ddfa329 fffffd7fffdfafe0 3048 ddfa359 d7f5a00
  1 209006                   memcpy:entry i=606 PID::entry:==pid2416:libc.so.1:memcpy:entry ddfa35b39572fff5 2 2 2 d7f5a00
  1 123964                   ttcc2u:entry i=607 PID::entry:==pid2416:oracle:ttcc2u:entry d8055e8 d7f58f039572fff8 4 1 1
  1 209006                   memcpy:entry i=608 PID::entry:==pid2416:libc.so.1:memcpy:entry ddfa35e39572fff8 4 852 d7f5a00 d7f5a00
  1  61112          ktsmg_max_query:entry i=609PID::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:entryd805218 1a 494b3f016 8 3a443a46039c253698
  1 211401                    times:entry i=612 PID::entry:==pid2416:libc.so.1:times:entryfffffd7fffdfb240 1a 494b3f016 8 3a443a46039c253698
  1 131260    kglHandleInvalidations:entry i=613PID::entry:==pid2416:oracle:kglHandleInvalidations:entry d8055e8 39df04bb0 0 8d7f1a10 39c2d1140
  1 131261          kglHandleLoads:entry i=614PID::entry:==pid2416:oracle:kglHandleLoads:entry d8055e8 39df04bb0 0 8 d7f1a1039c2d1140
  1  91756             kksGetStats:entryi=615 PID::entry:==pid2416:oracle:kksGetStats:entry 39d738198 39df04bb0 0 8 d7f1a1039c2d1140
  1  72794          kews_sqlcol_end:entry i=616PID::entry:==pid2416:oracle:kews_sqlcol_end:entry 3a4fd2210 fffffd7ffc9e1ad8 39d737e88 70 1 1
  1 110094                qecrlssub:entry i=617 PID::entry:==pid2416:oracle:qecrlssub:entry39d84ed10 fffffd7ffc9e1ad8 9 0 fffffd7ffc98ac58 0
  1 108487             qertbRelease:entry i=618PID::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 fffffd7ffc9f49f839572fff8 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:entryfffffd7ffc98bc00 39572fff5 39572fff7 0 fffffd7ffc98bbe8 2c1
--释放Buffer Pin，调用函数sskgslcas(目的是获取CBC Latch)
　
  1  63940                   kcbrls:entry i=623 PID::entry:==pid2416:oracle:kcbrls:entryfffffd7ffc98bc00 39572fff5 39572fff7 0 3a3c935f0 2c1
  --获取CBC Latch
  1 128720                sskgslcas:entry i=624 PID::entry:==pid2416:oracle:sskgslcas:entry 3a4282a90 0 200000000000001d 9 3a4c952882000000000000000
  1  64198                   kcbzar:entry i=625 PID::entry:==pid2416:oracle:kcbzar:entry 3a3c936703a4282a90 108000 -2748781186960 0 10
--释放CBC Latch
  1 128723               sskgsldecr:entry i=626PID::entry:==pid2416:oracle:sskgsldecr:entry 3a4282a90200000000000001d 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 whererownum=1;
SID
----------
     13

７.在13会话下再查询T1表的第一行，这时已被阻塞
select id,name from gyj.t1where rowid='AAASP9AAGAAAACHAAA';

８.再开一个会话，查看会话号是13的等待事件，产生了CBC Latch的等待事件
selectsid,event,p1raw,p2raw,p3raw from v$session where sid=13;
  SID EVENT                                                       P1RAW          P2RAW          P3RAW
------------------------------------------------------------------------------------------ ---------------- ----------------
     13 latch: cache buffers chains                                   00000003A4282A90 0000000000000096 00