oracle Latch: cache buffers chain争用定位

一、原理

即当对数据库中同一块的并发访问极高时，会发生等待事件“Latch: cache buffers chain”。对块的访问通常是一个快速操作，但如果并发用户足够快地重复访问块，则对该块的简单访问可能会成为瓶颈。当多个用户在表上运行嵌套循环连接并访问通过索引驱动的表时，最常见的 cbc（缓存缓冲区链）闩锁争用发生。

数据库中访问数据块过程如下：

1. 进程根据要访问块的文件号、块号，计算HASH值。
2. 根据HASH值找到HASH Bucket。
3. 搜索Bucket后的链表，查找哪个BH是目标BH。
4. 找到目标BH，从中取出Buffer的BA。
5. 按BA访问Buffer。
   搜索Bucket后的链表，还有访问BH中的BA，都需要Latch的保护。这个Latch就是Cache Buffers Chain Latch（简称CBC Latch）。

CBC Latch也有两种持有模式：共享和独占。但要注意的是，不同于Buffer Pin锁用读、写形式来决定锁的模式，就算为了“读”而持有CBC Latch，有时会是独占模式，而有时则会是共享模式。
CBC Latch的持有模式取决以下4个要素：

1. 对象类型（唯一索引、非唯一索引等）。
2. 块类型（根块、叶块或表块等）。
3. 操作（读、修改）。
4. 访问路径（Accees Path）。
   前面所提的流程一直都是独占CBC Latch的。除有唯一索引外，在大多数情况下，无论是读还是写，访问表块都将以独占模式获得CBC Latch。

出现CBC Latch等待的可能情况

1. 多个进程频繁地以不兼容的模式申请获得某一CBC Latch，访问此CBC Latch保护的不同链表和不同BH。
2. 多个进程频繁地以不兼容的模式申请获得某一CBC Latch，访问此CBC Latch保护的同一链表下的同一BH。

热链竞争最容易解决。多个进程其实访问的是不同的BH，只不过恰好这些BH在同一CBC Latch保护下（这种巧合的情况当然比较少见，但偶尔也会遇到），这时，解决方案很简单，可对两个隐藏参数中的一个进行修改，即_db_block_hash_buckets和_db_block_hash_latches，它们分别控制HASH Bucket的数量和CBC Latch的数量。这样一来，BH和HASH Bucket的对应关系就会被重新计算。原本在同一链表中的BH，重新计算后很可能就不在同一链表中了。

二、定位

1. sql

   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
       , dba_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(*)
   /        

找出对应sql和对象，找出的对象不一定就是对应的对象。还需要其它部分确认

3. event

SQL> select * from v$event_name    where name = 'latch: cache buffers chains';

    EVENT#   EVENT_ID NAME                           PARAMETER1                     PARAMETER2                     PARAMETER3                     WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------- ----------- --------------------
        88 2779959231 latch: cache buffers chains    address                        number                         tries                             3875070507           4 Concurrency

P1是cbc锁存器等待的锁存器的地址。找出等待次数最多的地址：

   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(*);   

3. 找出对象

   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

找到“TCH”或“touch次数”最高的块。计数是块被访问的次数的计数。计数有一些限制。计数每 3 秒只增加一次，即使每秒访问该块 100 万次，计数也只会每 3 秒增加一次。

4. 获取拥有最多cbc latch等待的对象

   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
   ;

5. 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(*);

6. 查找latch保护的热快
   https://tanelpoder.com/2009/08/27/latch-cache-buffers-chains-latch-contention-a-better-way-for-finding-the-hot-block/
   https://tanelpoder.com/2008/07/09/advanced-oracle-troubleshooting-guide-part-7-sampling-latch-holder-statistics-using-latchprof/

SQL> @bhla 27E5A780  <-- latch address reported by wait interface (Parameter1 in latch: cache buffers chains wait event)

FLG_LRUFLG                 OBJ OBJECT_TYPE         object                                          TCH DBA     
------------------- ---------- ------------------- ---------------------------------------- ---------- ---------
80000:8                      2 TABLE               SYS.SUBCOLTYPE$                                   0 1 7873  
80000:8                      2 TABLE               SYS.ATTRCOL$                                      0 1 7873  
0:0                      50472 INDEX               SYSMAN.MGMT_JOB_EXEC_IDX04                        1 3 27699 
0:0                      50472 INDEX               SYSMAN.MGMT_JOB_EXEC_IDX04                        1 3 27699 
0:4                      50472 INDEX               SYSMAN.MGMT_JOB_EXEC_IDX04                        1 3 27699 
2202000:8                 3710 TABLE               SYS.WRI$_ADV_REC_ACTIONS                          1 3 972   
0:0                      50472 INDEX               SYSMAN.MGMT_JOB_EXEC_IDX04                        1 3 27699 
0:0                      50472 INDEX               SYSMAN.MGMT_JOB_EXEC_IDX04                        1 3 27699 
2202000:8                94227 TABLE PARTITION     SYS.WRH$_ROWCACHE_SUMMARY                         2 3 36142 
80000:8                  54880 INDEX               PERFSTAT.STATS$UNDOSTAT_PK                       14 4 118331
0:8                         37 INDEX               SYS.I_OBJ2                                       24 1 55591 
2000:8                    3680 TABLE               SYS.WRI$_ADV_TASKS                               26 3 739   
0:8                         75 TABLE               SYS.IDL_UB2$                                     35 1 11745 
0:8                         73 TABLE               SYS.IDL_UB1$                                     38 1 12211

7. 查找latch地址

SQL> @sw 138
 SID STATE   EVENT                        SEC_IN_WAIT                 P1
------- ------- ---------------------------- ----------- ------------------
    138 WAITING latch: cache buffers chains            0 address=0x27E5A780

SQL> @latchprofx sid,name,hmode,object % 27E5A780 100000

 SID NAME                   HMODE         OBJECT   Held  Gets  Held %  Held ms
---- ---------------------- ------------ ------- ------ ----- ------- --------
 138 cache buffers chains   exclusive     40EB02   3928  3797    3.93   41.637
 151 cache buffers chains   exclusive     40EB02   3711  3660    3.71   39.337
 138 cache buffers chains   shared        40EB02    623   623     .62    6.604
 151 cache buffers chains   shared        40EB02    544   544     .54    5.766

SQL> @dba 40EB02

 RFILE#     BLOCK#
---------- ----------
         1      60162

Press enter to find the segment using V$BH (this may take CPU time), CTRL+C to cancel:

STATE      BLOCK_CLASS        OBJECT_TYPE         object                                          TCH  MODE_HELD
---------- ------------------ ------------------- ---------------------------------------- ---------- ----------
xcur       data block         INDEX               SYS.SYS_IOT_TOP_94276                           331          1

Press enter to query what segment resides there using DBA_EXTENTS (this can be IO intensive), CTRL+C to cancel:

OWNER                          SEGMENT_NAME         PARTITION_NAME  TABLESPACE_NAME
------------------------------ -------------------- --------------- -----------------
SYS                            SYS_IOT_TOP_94276                    SYSTEM

SQL> @oid 94276

owner                     object_name                    object_type        CREATED
------------------------- ------------------------------ ------------------ -----------------
SYS                       KILL_CPU                       TABLE              20090825 23:19:49

SQL> select file_id from dba_extents where relative_fno = 1 and segment_name = 'SYS_IOT_TOP_94276';

 FILE_ID
----------
 1

三、解决方案

1. 修改应用逻辑
2. 判断是否为NL连接导致，调整执行计划
3. 分散数据，调整PCTFREE
4. 创建hash索引

四、引用

https://tanelpoder.com/2009/08/27/latch-cache-buffers-chains-latch-contention-a-better-way-for-finding-the-hot-block/
https://tanelpoder.com/2008/07/09/advanced-oracle-troubleshooting-guide-part-7-sampling-latch-holder-statistics-using-latchprof/
https://docwiki.embarcadero.com/DBOptimizer/en/Latch:_cache_buffers_chains
吕海波《oracle内核技术揭秘》