Oracle等待事件（一）—— latch cache buffers chains 分析与优化思路

一、 什么是CBC等待

首先我们需要知道CBC等待发生在哪里，为什么会发生，才能理解应该如何定位，如何处理。

首先，CBC latch是用于保护buffer cache的，因此CBC等待一定发生在buffer cache部分。

 

1. 如何定位数据块是否在buffer cache中

首先，通过对数据块所在的文件号和块号进行hash计算，算出对应bucket号（hash bucket）。

沿着对应hash bucket所在hash chain list访问链上的buffer header（bh，相关信息由x$bh视图描述），hash chain list上挂载了一或多个bh，bh与Data block一一对应。

整体定位流程大致如下

 

2. CBC等待是如何发生的

从上图中可以看到，一个latch负责保护多个hash bucket，如果有多个会话需要同时访问一个hash chain list，就会在latch处发生争用，只有一个会话可以持有latch，其余会话需要等待 latch cache buffers chains。

根据会话集中访问对象的不同，cbc等待主要有两种成因：

• 同一个cache buffers chains下不同block被频繁访问，称为hot chains
• 同一个cache buffers chains下同一个block被频繁访问，称为hot block

一个块的访问过程，一般会有2次cbc latch的获取、释放。

 

二、 如何分析CBC等待

分为两种情况：等待正在发生（能从v$session查到），分析历史的CBC等待（需要从v$ash或者dba_ash中获取信息）。

1. CBC等待正在发生

当大量会话出现cbc等待，或者慢sql长时间处于cbc等待时可用。特征是v$session的event字段为 latch: cache buffers chains，可以根据 v$session P1RAW字段（内存地址原始值，16进制）找到内存地址对应对象。

select FILE#,DBARFIL,DBABLK,TCH from X$bh where HLADDR='v$session P1RAW字段' order by TCH desc;
-- 多跑几次找到排在前列的几个
FILE#    DBARFIL     DBABLK        TCH
---------- ---------- ---------- ----------
        48         48    2454391        240
        48         48     488777        226
       401        401      29196        224

DBARFIL为文件号，DBABLK为块号，从dba_extents 查询对应对象名，看在慢sql执行计划中能否找到（是否为sql monitor activity占比最大的对象），如果有则可以对应处理（参考解决方法部分）。

select /*+ parallel(t,16)*/ * from dba_extents t where file_id=48 and 2454391 between block_id and block_id+blocks-1;

 

也可以使用以下语句直接查询

select 
        name, file#, dbablk, obj, tch, hladdr 
from x$bh bh
    , obj$ o
 where 
       o.obj#(+)=bh.obj and
      hladdr in 
(
    select ltrim(to_char(p1,'XXXXXXXXXX') )
    from v$active_session_history 
    where event like 'latch: cache buffers chains'
    group by p1 
    having count(*) > 5
)
   and tch > 5
order by tch   

example output

NAME          FILE# DBABLK    OBJ TCH HLADDR
------------- ----- ------ ------ --- --------
BBW_INDEX         1 110997  66051  17 6BD91180
IDL_UB1$          1  54837     73  18 6BDB8A80
VIEW$             1   6885     63  20 6BD91180
VIEW$             1   6886     63  24 6BDB8A80
DUAL              1   2082    258  32 6BDB8A80
DUAL              1   2081    258  32 6BD91180
MGMT_EMD_PING     3  26479  50312 272 6BDB8A80

 

2. 分析历史的CBC等待

• 查询哪些sql遇到过大量cbc等待

select 
      count(*), 
      sql_id, 
      nvl(o.object_name,ash.current_obj#) objn,
      substr(o.object_type,0,10) otype,
      CURRENT_FILE# fn,
      CURRENT_BLOCK# blockn
from  v$active_session_history ash
    , all_objects o
where event like 'latch: cache buffers chains'
  and o.object_id (+)= ash.CURRENT_OBJ#
group by sql_id, current_obj#, current_file#,
               current_block#, o.object_name,o.object_type
order by count(*);

CNT SQL_ID        OBJN     OTYPE   FN BLOCKN
---- ------------- -------- ------ --- ------
  84 a09r4dwjpv01q MYDUAL   TABLE    1  93170

找到sql_id后，可以具体分析其v$ash视图P1,P2,P3字段，定位热块和对象信息。

• 查询v$ash视图P1,P2,P3字段含义

select * from v$event_name where name = 'latch: cache buffers chains';
   
EVENT#     NAME                         PARAMETER1 PARAMETER2 PARAMETER3 
---------- ---------------------------- ---------- ---------- ----------
        58 latch: cache buffers chains     address     number      tries 

• 将p1表示的地址值转换为16进制

select
    count(*),
    lpad(replace(to_char(p1,'XXXXXXXXX'),' ','0'),16,0) laddr
from v$active_session_history
where event='latch: cache buffers chains'
group by p1
order by count(*);

COUNT(*)  LADDR
---------- ----------------
      4933 00000004D8108330   

• 关联x$bh与obj$基表，找到热块及对应对象名，分析慢sql执行计划看是否符合慢的原因

select o.name, bh.dbarfil, bh.dbablk, bh.tch
from x$bh bh, obj$ o
where tch > 5
  and hladdr='00000004D8108330'
  and o.obj#=bh.obj
order by tch

NAME        DBARFIL DBABLK  TCH
----------- ------- ------ ----
EMP_CLUSTER       4    394  120    

 

三、 解决方法

总的思路其实就是打散热块。

1. 加大表或索引pctfree（大表谨慎操作）

alter table 表名 pctfree 90;
alter table 表名 move;
alter index 索引名 rebuild;

2. 表做hash分区

3. 使用hash簇表，打散数据存放位置

4. 过热表改用全表扫描

将nest loop join改为merge join或hash join，如果对应表不大，可以尝试将消耗最高的部分修改为全表扫描观察效果

5. 使用反键或hash索引（针对索引）

6. 减少表空间block size，使每个数据块中存放更少的行（慎用）

alter table <table_name> minimize records_pre_block;

 

四、 其他

查询遇到过最大cbc等待的块

col object_name for a35
col cnt for 99999

SELECT
  cnt, object_name, object_type,file#, dbablk, obj, tch, hladdr 
FROM (
  select count(*) cnt, rfile, block from (
    SELECT /*+ ORDERED USE_NL(l.x$ksuprlat) */ 
      --l.laddr, u.laddr, u.laddrx, u.laddrr,
      dbms_utility.data_block_address_file(to_number(object,'XXXXXXXX')) rfile,
      dbms_utility.data_block_address_block(to_number(object,'XXXXXXXX')) block
    FROM 
       (SELECT /*+ NO_MERGE */ 1 FROM DUAL CONNECT BY LEVEL <= 100000) s,
       (SELECT ksuprlnm LNAME, ksuprsid sid, ksuprlat laddr,
        TO_CHAR(ksulawhy,'XXXXXXXXXXXXXXXX') object
        FROM x$ksuprlat) l,
       (select  indx, kslednam from x$ksled ) e,
       (SELECT
                    indx
                  , ksusesqh     sqlhash
   , ksuseopc
   , ksusep1r laddr
             FROM x$ksuse) u
    WHERE LOWER(l.Lname) LIKE LOWER('%cache buffers chains%') 
     AND  u.laddr=l.laddr
     AND  u.ksuseopc=e.indx
     AND  e.kslednam like '%cache buffers chains%'
    )
   group by rfile, block
   ) objs, 
     x$bh bh,
     dba_objects o
WHERE 
      bh.file#=objs.rfile
 and  bh.dbablk=objs.block  
 and  o.object_id=bh.obj
order by cnt;

CNT  OBJECT_NAME       TYPE  FILE#  DBABLK    OBJ   TCH  HLADDR
---- ----------------- ----- ----- ------- ------ ----- --------
   1 WB_RETROPAY_EARNS TABLE     4   18427  52701  1129 335F7C00
   1 WB_RETROPAY_EARNS TABLE     4   18194  52701  1130 335F7C00
   3 PS_RETROPAY_RQST  TABLE     4   13253  52689  1143 33656D00
   3 PS_RETROPAY_RQST  INDEX     4   13486  52692   997 33656D00
   3 WB_JOB            TABLE     4   14443  52698   338 335B9080
   5 PS_RETROPAY_RQST  TABLE     4   13020  52689   997 33656D00
   5 WB_JOB            TABLE     4   14676  52698   338 335B9080

查询数据块在buffer cache中有多少副本

select 
       count(*)
     , name
     , file#
     , dbablk
     , hladdr 
from   x$bh bh
          , obj$ o
where 
      o.obj#(+)=bh.obj and
      hladdr in 
(
    select ltrim(to_char(p1,'XXXXXXXXXX') )
    from v$active_session_history 
    where event like 'latch: cache%'
    group by p1 
)
group by name,file#, dbablk, hladdr
having count(*) > 1
order by count(*);

CNT NAME        FILE#  DBABLK HLADDR
--- ---------- ------ ------- --------
 14 MYDUAL          1   93170 2C9F4B20

 

参考

https://sites.google.com/site/embtdbo/wait-event-documentation/oracle-latch-cache-buffers-chains

https://yq.aliyun.com/articles/208510

https://www.slideshare.net/khailey/oracle-10g-performance-chapter-06-buffer-cache