- TOP Latch
SELECT l.latch#,
l.name as "latch名稱",
l.gets as "願(yuàn)意等待模式的請(qǐng)求數(shù)",
l.misses as "初次嘗試不成功次數(shù)",
l.sleeps as "成功獲取前sleeping次數(shù)",
l.immediate_gets as "立即模式latch請(qǐng)求數(shù)",
l.immediate_misses as "請(qǐng)求失敗數(shù)",
l.spin_gets as "首次失敗後續(xù)成功",
l."WAIT_TIME" as "花費(fèi)在等待latch的時(shí)間",
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
- 確認(rèn)為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
- 根據(jù)等待事件latch: cache buffers chains查詢相關(guān)的SQL_ID，引起爭用的對(duì)象所在的文件號(hào)和塊號(hào)
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
- 查看latch: cache buffers chains引起爭用的具體會(huì)話及開始時(shí)間
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;
- 再根據(jù)文件號(hào)，塊號(hào)查具體的爭用對(duì)象
select * from dba_extents where file_id=步驟3中的File and 步驟3中的blockn between block_id and block_id + blocks - 1;
可以看出引起cbc爭用的對(duì)象是一個(gè)分區(qū)索引的一個(gè)分區(qū)晦雨，這是典型的索引熱塊爭用。
臨時(shí)處理：查找相關(guān)的會(huì)話拾枣，kill掉；
后期處理：優(yōu)化分區(qū)索引結(jié)構(gòu)葫辐，本次決定將該分區(qū)索引修改成list分區(qū)。
