一、 什么是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