Oracle等待事件之07(latch: cache buffe

1. TOP Latch

SELECT l.latch#,
       l.name as "latch名称",
       l.gets as "願意等待模式的请求数",
       l.misses as "初次尝试不成功次数",
       l.sleeps as "成功获取前sleeping次数",
       l.immediate_gets as "立即模式latch请求数",
       l.immediate_misses as "请求失败数",
       l.spin_gets as "首次失败後續成功",
       l."WAIT_TIME" as "花费在等待latch的时间",
       trunc(MISSES*100/GETS,2) as "MISSES/GETS(PCT>10-warn)"
FROM   v$latch l
WHERE  l.misses > 0
and trunc(MISSES*100/GETS,2)>0.1
ORDER BY l.misses DESC;

image.png

2. 确认为latch: cache buffers chains引起的故障后，查看latch的命中率

SELECT name, gets, misses, sleeps,immediate_gets
,immediate_misses,trunc(MISSES*100/GETS,2) as "栓的命中率"
FROM v$latch
WHERE name = 'cache buffers chains';

image.png

3. 根据等待事件latch: cache buffers chains查询相关的SQL_ID，引起争用的对象所在的文件号和块号

select * from 
   (
    select count(*), sql_id, nvl(o.object_name,ash.current_obj#) objn,
    substr(o.object_type,0,10) otype,CURRENT_FILE# file#,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(*) desc 
   )where rownum <=10;

image.png

4. 查看latch: cache buffers chains引起争用的具体会话及开始时间

select sid,username,status,sql_id,to_char(logon_time,'yyyy-mm-dd hh24:mi:ss') start_exec
from v$session s  where event='latch: cache buffers chains' order by logon_time;

5. 再根据文件号，块号查具体的争用对象

select * from dba_extents where file_id=步驟3中的File and 步驟3中的blockn between block_id and block_id + blocks - 1;

可以看出引起cbc争用的对象是一个分区索引的一个分区，这是典型的索引热块争用。
   临时处理：查找相关的会话，kill掉；
   后期处理：优化分区索引结构，本次决定将该分区索引修改成list分区。