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 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 @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

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内核技术揭秘》