ITPub博客

首页 > 数据库 > Oracle > 使用了索引就一定能避免cache buffers chains争用吗

使用了索引就一定能避免cache buffers chains争用吗

原创 Oracle 作者:oliseh 时间:2015-11-24 15:59:49 0 删除 编辑
访问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。
       

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/53956/viewspace-1845543/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1637797