訪問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。

使用了索引就一定能避免cache buffers chains爭用嗎

相關文章