访问buffer cache里的数据块时需要先以独占模式申请管理hash bucket的latch，这个latch的名称就是所谓的cache buffers chains，系统中出现latch:cache buffers chains争用往往意味着以下两种可能：
1、多个会话并发访问相同的数据块(这是最常见的一种情况)
2、不同的数据块挂载在同一个hash bucket下，或者虽然挂载在不同的hash bucket下但这些hash bucket恰好受同一个cache buffer chains latch管理(一个latch可以管理多个hash bucket)
以上只是概念上的笼统描述，是否真会引起latch:cache buffers chains等待还与SQL语句的访问路径有关：Full Table Scan的访问路径下确实同上面所描述的，
但在用到索引的情况下是否会触发latch:cache buffers chains取决于以下5种因素：
1、如果是针对于唯一性索引的叶块的等值访问，这里包括仅访问唯一性索引本身和既访问唯一性索引又通过唯一性索引访问数据表两种情况，访问时都会以共享模式申请管理hash bucket的latch，所以即便多个会话并发访问buffer cache里的同一个唯一性索引的块，也不会出现latch:cache buffers chains等待事件，这时能观察到的等待事件一般是"cursor: pin S"
2、如果是针对于唯一性索引的叶块的非等值访问，当然也包含仅访问索引和既访问索引又访问数据表两种情况，访问时都会以独占模式申请管理hash bucket的latch，多个会话并发访问buffer cache里的同一个唯一性索引的块，会出现latch:cache buffers chains等待事件
3、对于非唯一性索引的叶块，和表里的数据块一样会以独占模式申请cache buffers chains latch
4、对于唯一性索引里的同一个索引块如果两个session同时以等值方式访问不会出现latch:cache buffers chains等待
5、对于唯一性索引里的同一个索引块如果分别被两个session以等值、非等值方式访问时会出现latch:cache buffers chains等待
6、没有索引
这里之所以强调叶块，是因为索引里的非叶块访问时都是以共享方式持有cache buffers chains latch的，对于唯一性和非唯一性索引均是如此
以下我们构造几个测试场景了解一下Oracle是如何管理cache buffers chains latch的：其中1~3测试在不同block被同一个latch管理的场景下进行；4~5测试在同一个block被多个session并发访问的场景下进行
#####场景1：一个非唯一性索引的数据块、一个表的数据块被不同的session访问引起latch: cache buffers chains争用
---创建表和索引
create table scott.t1119_cb1 tablespace ts1116 as select * from all_users;
create index scott.ind_t1119_cb1 on scott.t1119_cb1(user_id) tablespace ts1116 ;
---执行计划用到了索引
explain plan for select * from scott.t1119_cb1 where user_id=0;
set linesize 120 pagesize 100
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1836694578
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1119_CB1 | 1 | 39 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T1119_CB1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
---获取表、索引的object_id
select * from dba_objects where object_name in ('T1119_CB1','IND_T1119_CB1');
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID
SCOTT IND_T1119_CB1 41332
SCOTT T1119_CB1 41331
set linesize 150
select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where obj=41332;
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41332 82300 00000001109A5358 07000001BCD67618 35651584 0 9 1 5 60713 070000012ACE6000 1
41332 156749 00000001109A5358 07000001BCE25910 35651584 0 1 1 5 60715 0700000123020000 5 <---index block
41332 202672 00000001109A5358 07000001BCE98500 35651584 0 8 1 5 60712 070000014820E000 1
41332 286932 00000001109A5358 07000001BCF567F8 35651584 0 4 1 5 60714 07000000F3F54000 4
SELECT * FROM SCOTT.T1119_CB1 where user_id=0;
SYS@tstdb1-SQL> select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where obj=41332;
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41332 82300 00000001109A5358 07000001BCD67618 35651584 0 9 1 5 60713 070000012ACE6000 1
41332 156750 00000001109A5358 07000001BCE25910 35651584 0 1 1 5 60715 0700000123020000 6 <---index block
41332 202673 00000001109A5358 07000001BCE98500 35651584 0 8 1 5 60712 070000014820E000 1
41332 286933 00000001109A5358 07000001BCF567F8 35651584 0 4 1 5 60714 07000000F3F54000 4
---由上述输出我们查看07000001BCE25910这个Latch下还包含哪些对象
select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where hladdr='07000001BCE25910';
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
4294967295 156742 00000001109A5358 07000001BCE25910 35651584 0 102 1 10 12442 070000015C9C0000 1
4294967295 156743 00000001109A5210 07000001BCE25910 0 4 98 0 10 12442 0700000131352000 0
4294967295 156744 00000001109A50C8 07000001BCE25910 0 4 96 0 10 12442 07000001660F6000 0
40172 156745 00000001109A5358 07000001BCE25910 524288 0 1 1 2 25634 070000011C380000 2
4294967295 156746 00000001109A5358 07000001BCE25910 0 4 96 0 10 43041 07000001593E6000 0
4294967295 156747 00000001109A5210 07000001BCE25910 0 4 102 0 10 43041 0700000182822000 0
41332 156748 00000001109A5358 07000001BCE25910 35651584 0 1 1 5 60715 0700000123020000 6
---选取obj=40172对应的表SYS.WRH$_LATCH(注：obj=4294967295对应的block均来自于undo block，bitmap block等块，这些块不属于任何表或者索引)，
col owner format a30
col object_name format a20
set linesize 120
select owner,object_name,data_object_id from dba_objects where data_object_id=40172;
OWNER OBJECT_NAME DATA_OBJECT_ID
------------------------------ -------------------- --------------
SYS WRH$_LATCH 40172
---构造出block 2/25634 第一行的rowid
select dbms_rowid.rowid_create(1,40172,2,25634,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAJzsAACAAAGQiAAA
---开启session 1通过索引方式访问SCOTT.T1119_CB1表
declare
type typ1 is record (p1 varchar2(30),p2 number,p3 date);
v_typ1 typ1;
begin
while ( true )
loop
select * into v_typ1 from scott.t1119_cb1 where user_id=0;
end loop;
end;
/
---接着开启session 2访问SYS.WRH$_LATCH表block 2/25634里的第一行
declare
type typ2 is record (p1 number,p2 number,p3 number);
v_typ2 typ2;
begin
while ( true )
loop
select snap_id,dbid,instance_number into v_typ2 from sys.WRH$_LATCH where rowid='AAAJzsAACAAAGQiAAA';
end loop;
end;
/
---查询v$session可以看到有两个session都在等待"latch: cache buffers chains"事件
select sid,event,p1text,to_char(p1,'xxxxxxxx'),p2text,p2,p3text,p3 from v$session where event like 'latch%';
SID EVENT P1TEXT TO_CHAR(P1,'XXXX P2TEXT P2 P3TEXT P3
-------------------- ----------------- ----------------- ---------------- ----------------- -------------------- ----------------- --------------------
469 latch: cache buff address 7000001bce25910 number 155 tries 0
ers chains
995 latch: cache buff address 7000001bce25910 number 155 tries 0
ers chains
看出他们的P1值都为07000001BCE25910，表示他们都在争抢同一个catch buffers chains latch，
---根据P1、P2值到v$latch_children里匹配出该latch的访问统计，misses，大约占到了4%，另外还有sleeps
SYS@tstdb1-SQL> select addr,latch#,child#,name,gets,misses,sleeps,spin_gets from v$latch_children where latch#=155 and addr='07000001BCE25910';
ADDR LATCH# CHILD# NAME GETS MISSES SLEEPS SPIN_GETS
---------------- -------------------- -------------------- ------------------------- -------------------- -------------------- -------------------- --------------------
07000001BCE25910 155 16361 cache buffers chains 212854610 9318472 780 9317745
SYS@tstdb1-SQL> r
1* select addr,latch#,child#,name,gets,misses,sleeps,spin_gets from v$latch_children where latch#=155 and addr='07000001BCE25910'
ADDR LATCH# CHILD# NAME GETS MISSES SLEEPS SPIN_GETS
---------------- -------------------- -------------------- ------------------------- -------------------- -------------------- -------------------- --------------------
07000001BCE25910 155 16361 cache buffers chains 213145448 9329338 781 9328610
#####场景2：两个同属于一个latch管理的唯一性索引块同时被以等值条件访问时，不会引起latch: cache buffers chains争用
---创建表和索引
create table scott.t1119_cb2 tablespace ts1116 as select * from all_users;
create unique index scott.ind_t1119_cb2 on scott.t1119_cb2(user_id) tablespace ts1116 ;
---执行计划用到了索引
explain plan for select * from scott.t1119_cb2 where user_id=0;
set linesize 120 pagesize 100
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1423192252
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1119_CB2 | 1 | 39 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IND_T1119_CB2 | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
---获取表、索引的object_id
select OWNER,OBJECT_NAME,OBJECT_ID,data_object_id from dba_objects where object_name in ('T1119_CB2','IND_T1119_CB2');
OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
------------------------------ -------------------- ---------- --------------
SCOTT IND_T1119_CB2 41334 41334
SCOTT T1119_CB2 41333 41333
set linesize 150
select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where obj=41334;
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41334 47225 0000000110B36A50 07000001B6B76538 33554433 0 9 1 5 60729 07000000F8434000 1
41334 121764 0000000110B36A50 07000001BCDCA8D0 33554433 0 1 1 5 60731 0700000147812000 1 <---index block
41334 167768 0000000110B36A50 07000001B6CA7420 33554433 0 8 1 5 60728 07000000FD0CC000 1
41334 242213 0000000110B36A50 07000001BCEFB7B8 33554433 0 4 1 5 60730 07000001676C2000 1
select * from scott.t1119_cb2 where user_id=0;
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41334 47226 0000000110B36A50 07000001B6B76538 35651584 0 9 1 5 60729 07000000F8434000 1
41334 121766 0000000110B36A50 07000001BCDCA8D0 35651584 0 1 1 5 60731 0700000147812000 2 <---index block
41334 167772 0000000110B36A50 07000001B6CA7420 35651584 0 8 1 5 60728 07000000FD0CC000 1
41334 242219 0000000110B36A50 07000001BCEFB7B8 35651584 0 4 1 5 60730 07000001676C2000 1
---由上述输出我们查看07000001BCE25910这个Latch下还包含哪些对象
select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where hladdr='07000001BCDCA8D0';
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
4294967295 121764 0000000110A91130 07000001BCDCA8D0 35651584 0 102 1 10 12458 07000001651DE000 4
4294967295 121765 0000000110A90FE8 07000001BCDCA8D0 0 4 98 0 10 12458 0700000107ED0000 0
4294967295 121766 0000000110A90EA0 07000001BCDCA8D0 0 4 96 0 10 12458 07000001660E8000 0
40166 121767 0000000110A91130 07000001BCDCA8D0 524288 0 1 1 2 25650 0700000110EB6000 2
4294967295 121768 0000000110A91130 07000001BCDCA8D0 0 4 96 0 10 43057 07000001593F2000 0
4294967295 121769 0000000110A90FE8 07000001BCDCA8D0 0 4 102 0 10 43057 0700000186482000 0
41334 121770 0000000110A91130 07000001BCDCA8D0 35651584 0 1 1 5 60731 0700000147812000 2
---选取obj=40166对应的索引SYS.WRH$_SYSTEM_EVENT_PK(注：obj=4294967295对应的block均来自于undo block，bitmap block等块，这些块不属于任何表或者索引)
col owner format a30
col object_name format a20
set linesize 120
select owner,object_name,data_object_id from dba_objects where data_object_id=40166;
OWNER OBJECT_NAME DATA_OBJECT_ID
------------------------------ -------------------- --------------
SYS WRH$_SYSTEM_EVENT_PK 40166
col column_name format a30
set linesize 100
select index_owner,table_name,index_name,column_name from dba_ind_columns where index_name='WRH$_SYSTEM_EVENT_PK';
INDEX_OWNER TABLE_NAME INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
SYS WRH$_SYSTEM_EVENT WRH$_SYSTEM_EVENT_PK DBID
SYS WRH$_SYSTEM_EVENT WRH$_SYSTEM_EVENT_PK SNAP_ID
SYS WRH$_SYSTEM_EVENT WRH$_SYSTEM_EVENT_PK INSTANCE_NUMBER
SYS WRH$_SYSTEM_EVENT WRH$_SYSTEM_EVENT_PK EVENT_ID
---对索引块2/25650做dump后截取了如下片段
Leaf block dump
===============
header address 4572180580=0x11085f064
kdxcolev 0 <---表明是leaf block
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 4
kdxcosdc 2
kdxconro 258
kdxcofbo 552=0x228
kdxcofeo 562=0x232
kdxcoavs 10
kdxlespl 0
kdxlende 0
kdxlenxt 8414260=0x806434
kdxleprv 8414259=0x806433
kdxledsz 6
kdxlebksz 8032
row#0[562] flag: ------, lock: 0, len=29, data:(6): 00 80 6c da 00 29 <----0000000010|0000000110110011011010|0000000000101001=>file_id|blkno|row#=>2|27866|41，表示这条索引指向表中数据块的位置是block 2/27866里的第41行
col 0; len 6; (6): c5 15 1f 42 30 4c
col 1; len 3; (3): c2 29 29
col 2; len 2; (2): c1 02
col 3; len 6; (6): c5 11 2f 4f 09 53
row#1[591] flag: ------, lock: 0, len=29, data:(6): 00 80 6c da 00 2a
col 0; len 6; (6): c5 15 1f 42 30 4c
col 1; len 3; (3): c2 29 29
col 2; len 2; (2): c1 02
col 3; len 6; (6): c5 12 51 07 3d 0b
---找到数据块2/27866对应的表分区
select owner,segment_name,partition_name from dba_extents where file_id=2 and 27866 between block_id and block_id+blocks;
OWNER SEGMENT_NAME PARTITION_NAME
------------------------------ --------------------------------------------------------------------------------- ------------------------------
SYS WRH$_SYSTEM_EVENT WRH$_SYSTEM_2030654775_4012
col object_name format a30
set linesize 120
select data_object_id,object_name,subobject_name from dba_objects where object_name='WRH$_SYSTEM_EVENT' and subobject_name='WRH$_SYSTEM_2030654775_4012';
DATA_OBJECT_ID OBJECT_NAME SUBOBJECT_NAME
-------------- ------------------------------ ------------------------------
40164 WRH$_SYSTEM_EVENT WRH$_SYSTEM_2030654775_4012
---构造出block 2/27866第41行的rowid
select dbms_rowid.rowid_create(1,40164,2,27866,41) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAJzkAACAAAGzaAAp
---找出这一行的数据
select DBID,SNAP_ID,INSTANCE_NUMBER,EVENT_ID from WRH$_SYSTEM_EVENT partition (WRH$_SYSTEM_2030654775_4012) where rowid='AAAJzkAACAAAGzaAAp';
DBID SNAP_ID INSTANCE_NUMBER EVENT_ID
---------- ---------- --------------- ----------
2030654775 4040 1 1646780882
---根据索引的4个字段为条件进行查询两次
select DBID,SNAP_ID,INSTANCE_NUMBER,EVENT_ID from WRH$_SYSTEM_EVENT partition (WRH$_SYSTEM_2030654775_4012) where dbid=2030654775 and snap_id=4040 and instance_number=1 and event_id=1646780882;
DBID SNAP_ID INSTANCE_NUMBER EVENT_ID
---------- ---------- --------------- ----------
2030654775 4040 1 1646780882
select DBID,SNAP_ID,INSTANCE_NUMBER,EVENT_ID from WRH$_SYSTEM_EVENT partition (WRH$_SYSTEM_2030654775_4012) where dbid=2030654775 and snap_id=4040 and instance_number=1 and event_id=1646780882;
DBID SNAP_ID INSTANCE_NUMBER EVENT_ID
---------- ---------- --------------- ----------
2030654775 4040 1 1646780882
---查询后发现BA=07000000DDDCA000这行的TCH增加为2
select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where hladdr='07000001BCDCA8D0'
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
4294967295 132198 00000001109D91A0 07000001BCDCA8D0 0 4 102 0 10 12458 07000001651DE000 0
4294967295 132199 00000001109D9058 07000001BCDCA8D0 0 4 98 0 10 12458 0700000107ED0000 0
4294967295 132200 00000001109D8F10 07000001BCDCA8D0 0 4 96 0 10 12458 07000001660E8000 0
40166 132201 00000001109D91A0 07000001BCDCA8D0 0 0 1 1 2 25650 07000000DDDCA000 2
40166 132202 00000001109D9058 07000001BCDCA8D0 0 4 1 0 2 25650 0700000110EB6000 0
4294967295 132203 00000001109D91A0 07000001BCDCA8D0 0 4 96 0 10 43057 07000001593F2000 0
4294967295 132204 00000001109D9058 07000001BCDCA8D0 0 4 102 0 10 43057 0700000186482000 0
41334 132205 00000001109D91A0 07000001BCDCA8D0 0 4 1 0 5 60731 0700000147812000 0
---至此同一个CBC latch控制下的两个block的访问语句都已经确定，我们在两个不同的session里执行下面两段代码，观察是否会发生latch: Cache buffers chains等待事件
***session 1运行：
declare
type rec_t1119 is record (p1 varchar2(30),p2 number,p3 date);
v_rec_t1119 rec_t1119;
begin
while ( true ) loop
select * into v_rec_t1119 from scott.t1119_cb2 where user_id=0;
end loop;
end;
/
***session 2运行：
declare
type rec_wrh is record (p1 number,p2 number,p3 number,p4 number);
v_rec_wrh rec_wrh;
begin
while ( true ) loop
select DBID,SNAP_ID,INSTANCE_NUMBER,EVENT_ID into v_rec_wrh from WRH$_SYSTEM_EVENT partition (WRH$_SYSTEM_2030654775_4012) where dbid=2030654775 and snap_id=4040 and instance_number=1 and event_id=1646780882;
end loop;
end;
/
***session 3：期间没有观察到latch:cache buffer chains的等待，misses约占了2.8%，没有出现sleeps
SYS@tstdb1-SQL> select * from v$session where event like '%latch%';
no rows selected
SYS@tstdb1-SQL> select * from v$session where event like '%latch%'
no rows selected
SYS@tstdb1-SQL> select * from v$session where event like '%latch%';
no rows selected
SYS@tstdb1-SQL> select addr,latch#,child#,name,gets,misses,sleeps,spin_gets from v$latch_children where latch#=155 and addr='07000001BCDCA8D0';
ADDR LATCH# CHILD# NAME GETS MISSES SLEEPS SPIN_GETS
---------------- ---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ----------
07000001BCDCA8D0 155 12705 cache buffers chains 37874388 1071789 0 1071789
正在执行的两个session一直处于空闲等待事件"SQL*Net message from client"
select sid,event from v$session where sid in (927,336) <---正在执行的两个session id
SID EVENT
---------- ----------------------------------------------------------------
336 SQL*Net message from client
927 SQL*Net message from client
结论2：以上测试证明同一个CBC Latch管理下的两个block如果都来自于唯一性索引，且访问形式都是等值访问，那么这两个块访问的时候不会引起latch:cache buffers chains等待
#####场景3：一个唯一性索引的数据块，一个表的数据块，在同一个latch管理的情况下，同时被访问(索引块使用等值访问)不会出现latch:cache buffers chains争用
create table scott.t1121_1 tablespace ts1116 as select * from all_users;
create unique index scott.uni_ind_uid on scott.t1121_1(user_id) tablespace ts1116;
col object_name format a30
col owner format a30
set linesize 150
select object_id,data_object_id,owner,object_name from dba_objects where object_name='UNI_IND_UID';
OBJECT_ID DATA_OBJECT_ID OWNER OBJECT_NAME
---------- -------------- ------------------------------ ------------------------------
41548 41548 SCOTT UNI_IND_UID
set linesize 150
select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where obj=41548;
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41548 784 0000000111A29570 07000001B6AFE8D0 0 0 1 1 5 60763 0700000189C66000 0 <---index block
41548 1471 0000000111A29570 07000001BCC9E678 0 0 8 1 5 60760 0700000189B7C000 0
41548 2547 0000000111A29570 07000001BCCA45C8 0 0 4 1 5 60762 070000018998A000 0
41548 4346 0000000111A29570 07000001BCCADE20 0 0 9 1 5 60761 0700000189A86000 0
---查找同一CBC latch下的其它对象
select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where hladdr='07000001B6AFE8D0';
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
2 839 0000000111A29570 07000001B6AFE8D0 524288 0 1 1 1 13134 0700000188834000 1
6493 840 0000000111A29570 07000001B6AFE8D0 0 0 1 1 2 16540 07000001894A6000 0
160 841 0000000111A29570 07000001B6AFE8D0 524288 0 1 1 1 105164 0700000189BB8000 1
38 842 0000000111A29570 07000001B6AFE8D0 0 0 1 1 1 353 07000001881DE000 2
41548 843 0000000111A29570 07000001B6AFE8D0 0 0 1 1 5 60763 0700000189C66000 0
101 844 0000000111A29570 07000001B6AFE8D0 524288 0 1 1 1 819 0700000189A38000 2
---确定对象名、根据file_id/block_id构造出rowid
select object_id,data_object_id,owner,object_type,object_name from dba_objects where data_object_id=6493;
OBJECT_ID DATA_OBJECT_ID OWNER OBJECT_TYPE OBJECT_NAME
---------- -------------- ------------------------------ ------------------- ------------------------------
6493 6493 SYS TABLE WRH$_SYSMETRIC_SUMMARY
select dbms_rowid.rowid_create(1,6493,2,16540,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAABldAACAAAECcAAA
---事先验证一下两个block的访问语句一个是走unique index scan，另一个是走rowid直接访问
***scott.t1121_1表的访问语句
set linesize 120 pagesize 120
explain plan for select user_id from scott.t1121_1 where user_id=0;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2735981831
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| UNI_IND_UID | 1 | 13 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------
***sys.WRH$_SYSMETRIC_SUMMARY表的访问语句是access by rowid
set linesize 120 pagesize 120
explain plan for select * from sys.WRH$_SYSMETRIC_SUMMARY where rowid='AAABldAACAAAECcAAA';
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2659935131
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 75 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| WRH$_SYSMETRIC_SUMMARY | 1 | 75 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
---开启两个session同时访问同一latch下的两个block，是否能观察到latch:cache buffers chains争用
***session 1:
declare
v_rec_t1121_1 number;
begin
while ( true ) loop
select user_id into v_rec_t1121_1 from scott.t1121_1 where user_id=0;
end loop;
end;
/
***session 2:
declare
v_rec_wrh_sum number;
begin
while ( true ) loop
select average into v_rec_wrh_sum from sys.WRH$_SYSMETRIC_SUMMARY where rowid='AAABldAACAAAECcAAA';
end loop;
end;
/
---观察session 1、session 2的等待事件，均为空闲等待，未观察到latch:cache buffer chains
SYS@tstdb1-SQL> select sid,event from v$session where sid in (467,532);
SID EVENT
---------- ----------------------------------------------------------------
467 SQL*Net message from client
532 SQL*Net message from client
SYS@tstdb1-SQL> select sid,event from v$session where sid in (467,532)
SID EVENT
---------- ----------------------------------------------------------------
467 SQL*Net message from client
532 SQL*Net message from client
---观察latch的统计信息，没有sleeps
SYS@tstdb1-SQL> select addr,latch#,child#,name,gets,misses,sleeps,spin_gets from v$latch_children where latch#=155 and addr='07000001B6AFE8D0'
ADDR LATCH# CHILD# NAME GETS MISSES SLEEPS SPIN_GETS
---------------- ---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ----------
07000001B6AFE8D0 155 169 cache buffers chains 22062069 637837 0 637836
SYS@tstdb1-SQL> select addr,latch#,child#,name,gets,misses,sleeps,spin_gets from v$latch_children where latch#=155 and addr='07000001B6AFE8D0'
ADDR LATCH# CHILD# NAME GETS MISSES SLEEPS SPIN_GETS
---------------- ---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ----------
07000001B6AFE8D0 155 169 cache buffers chains 22121216 639607 0 639606
结论3：如果同一个CBC latch下的两个block里只要有一个是unique index scan形式的等值访问，那么这两个block之间不会存在latch:cache buffers chains争用
上面的三个场景都是在不同的session访问两个不同的数据块时产生的，因为数据块所属的hash bucket受同一个cache buffers chains latch管理所致。
最后来看一下对于来自唯一性索引的同一个block如果以多个session并发访问，是否会出现等待
#####场景4：多个session并发以等值条件访问同一个唯一性索引块，不会出现latch:cache buffers chains争用
---还是延用之前的表scott.t1121_1及之上的唯一性索引作为例子
col owner format a30
col table_name format a40
set linesize 100
select owner,table_name from dba_tables where table_name='T1121_1';
OWNER TABLE_NAME
------------------------------ ----------------------------------------
SCOTT T1121_1
col object_name format a30
col owner format a30
set linesize 150
select object_id,data_object_id,owner,object_name from dba_objects where object_name='UNI_IND_UID';
OBJECT_ID DATA_OBJECT_ID OWNER OBJECT_NAME
---------- -------------- ------------------------------ ------------------------------
41548 41548 SCOTT UNI_IND_UID
set linesize 150
select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where obj=41548;
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41548 13555 00000001109D7528 07000001B6AFE8D0 0 0 1 1 5 60763 0700000189C66000 176
41548 25617 00000001109D7528 07000001BCC9E678 0 0 8 1 5 60760 0700000189B7C000 0
41548 45048 00000001109D7528 07000001BCCA45C8 0 0 4 1 5 60762 070000018998A000 1
41548 76570 00000001109D7528 07000001BCCADE20 0 0 9 1 5 60761 0700000189A86000 0
---session 1、session 2使用相同的语句
***session 1:
declare
v_rec_t1121_1 number;
begin
while ( true ) loop
select user_id into v_rec_t1121_1 from scott.t1121_1 where user_id=0;
end loop;
end;
/
***session 2:
declare
v_rec_t1121_1 number;
begin
while ( true ) loop
select user_id into v_rec_t1121_1 from scott.t1121_1 where user_id=0;
end loop;
end;
/
---此时从v$session观察到关于这两个session的等待事件是"cursor: pin S"
select sid,event from v$session where sid in (467,266);
SID EVENT
---------- ----------------------------------------------------------------
266 cursor: pin S
467 cursor: pin S
这里解释一下：因为sess 1和sess 2执行的语句完全相同所以在parse阶段就出现了关于library cache的严重竞争，所以大部分时间观察到的都是"cursor: pin S"这个等待事件
---为了避开"cursor: pin S"这个等待我们重新改写一下session 2里的sql语句加入一个无用的hint，重新测试一下
***session 1:
declare
v_rec_t1121_1 number;
begin
while ( true ) loop
select user_id into v_rec_t1121_1 from scott.t1121_1 where user_id=0;
end loop;
end;
/
***session 2:
declare
v_rec_t1121_1 number;
begin
while ( true ) loop
select /*+ session_2 */ user_id into v_rec_t1121_1 from scott.t1121_1 where user_id=0;
end loop;
end;
/
---最终我们看到的是空闲等待，并没有latch:cache buffers chains等待
SYS@tstdb1-SQL> select sid,event from v$session where sid in (467,266);
SID EVENT
---------- ----------------------------------------------------------------
266 SQL*Net message from client
467 SQL*Net message from client
SYS@tstdb1-SQL> select sid,event from v$session where sid in (467,266)
SID EVENT
---------- ----------------------------------------------------------------
266 SQL*Net message from client
467 SQL*Net message from client
再扩展一下，如果select结果字段不含在唯一性索引里，即需要通过访问数据表来得到最终结果的，一样不会发生latch:cache buffers chains争用
set linesize 120 pagesize 100
explain plan for select username from scott.t1121_1 where user_id=0;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3873470818
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1121_1 | 1 | 12 | 1 (0)| 00:00:01 | <--- rowid访问表
|* 2 | INDEX UNIQUE SCAN | UNI_IND_UID | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
***session 1:
declare
v_rec_t1121_1 varchar2(30);
begin
while ( true ) loop
select username into v_rec_t1121_1 from scott.t1121_1 where user_id=0;
end loop;
end;
/
***session 2:
declare
v_rec_t1121_1 varchar2(30);
begin
while ( true ) loop
select /*+ session 2 */ username into v_rec_t1121_1 from scott.t1121_1 where user_id=0;
end loop;
end;
/
SYS@tstdb1-SQL> select sid,event from v$session where sid in (664,598);
SID EVENT
---------- ----------------------------------------------------------------
598 SQL*Net message from client
664 SQL*Net message from client
SYS@tstdb1-SQL> select sid,event from v$session where sid in (664,598)
SID EVENT
---------- ----------------------------------------------------------------
598 SQL*Net message from client
664 SQL*Net message from client
结论4：唯一性索引里的同一个block被不同的session同时以等值条��访问时，无论是仅访问索引本身还是也访问表，都不会存在latch:cache buffers chains争用
#####场景5：一个唯一性索引块被两个不同的session分别以等值与非等值方式访问时，能观察到latch:cache buffers chains争用
col owner format a30
col table_name format a40
set linesize 100
select owner,table_name from dba_tables where table_name='T1121_1';
OWNER TABLE_NAME
------------------------------ ----------------------------------------
SCOTT T1121_1
col object_name format a30
col owner format a30
set linesize 150
select object_id,data_object_id,owner,object_name from dba_objects where object_name='UNI_IND_UID';
OBJECT_ID DATA_OBJECT_ID OWNER OBJECT_NAME
---------- -------------- ------------------------------ ------------------------------
41548 41548 SCOTT UNI_IND_UID
set linesize 150
select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where obj=41548;
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41548 2804 0000000110994888 07000001B6AFE8D0 0 0 1 1 5 60763 070000018737A000 135
41548 9178 0000000110994888 07000001BCCA45C8 0 0 4 1 5 60762 0700000181D54000 1
---确认非等值访问走的也是索引扫描
set linesize 120 pagesize 100
explain plan for select user_id from scott.t1121_1 where user_id<=10;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2894018642
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 12 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| UNI_IND_UID | 3 | 12 | 1 (0)| 00:00:01 |
---session 1: 等值访问
declare
v_rec_t1121_1 number;
begin
while ( true ) loop
select user_id into v_rec_t1121_1 from scott.t1121_1 where user_id=0;
end loop;
end;
/
---session 2: 非等值访问
DECLARE
TYPE t_t1121_user_id is table of scott.t1121_1.user_id%type;
v_t_1121_user_id t_t1121_user_id;
BEGIN
while ( true ) loop
select user_id bulk collect into v_t_1121_user_id from scott.t1121_1 where user_id<=10;
end loop;
END;
/
---观察两个session的等待事件：最终还是会观察到两个Session不断在等待latch: cache buffers chains事件
select sid,event,p1raw from v$session where sid in (401,266);
SID EVENT P1RAW
---------- ---------------------------------------------------------------- ----------------
266 latch: cache buffers chains 07000001B6AFE8D0
401 latch: cache buffers chains 07000001B6AFE8D0
---latch统计信息变化：
SYS@tstdb1-SQL> r
1* select addr,latch#,child#,name,gets,misses,sleeps,spin_gets from v$latch_children where addr='07000001B6AFE8D0'
ADDR LATCH# CHILD# NAME GETS MISSES SLEEPS SPIN_GETS
---------------- ---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ----------
07000001B6AFE8D0 155 169 cache buffers chains 479766692 21527545 493 21527097
SYS@tstdb1-SQL> r
1* select addr,latch#,child#,name,gets,misses,sleeps,spin_gets from v$latch_children where addr='07000001B6AFE8D0'
ADDR LATCH# CHILD# NAME GETS MISSES SLEEPS SPIN_GETS
---------------- ---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ----------
07000001B6AFE8D0 155 169 cache buffers chains 479897592 21535649 494 21535200
SYS@tstdb1-SQL> r
1* select addr,latch#,child#,name,gets,misses,sleeps,spin_gets from v$latch_children where addr='07000001B6AFE8D0'
ADDR LATCH# CHILD# NAME GETS MISSES SLEEPS SPIN_GETS
---------------- ---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ----------
07000001B6AFE8D0 155 169 cache buffers chains 480225332 21554044 495 21553594
结论5：当两个session分别以等值、非等值方式访问同一个唯一性索引块的时候，会出现cache buffers chains等待，因为非等值的session这时需要以独占方式持有CBC latch为的是修改指向索引块的Buffer header中的占位符，所以等值session访问buffer header时要等前面的独占模式结束后才能继续进行。反之非等值的session也要等待等值访问的session释放了以共享方式持有的CBC latch后才能以独占方式重新持有CBC latch并修改buffer header。