#####场景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
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=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;
---开启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、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";
#####场景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
---获取表、索引的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
---选取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";
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;
---根据索引的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"
***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";
正在执行的两个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
#####场景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;
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
***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
---开启两个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"
#####场景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
---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
---为了避开"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
***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
#####场景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_na
易网时代-编程资源站