背景现象:
应用系统响应速度慢,常因为堵塞锁过多导致系统堵死,应用无法使用。用top命令查看,CPU利用率居高不下
处理过程:
- 查看oracle的等待事件,
select event,count(*) from v$session_wait group by event;
发现有21个latch: Cache buffers chains事件,latch是内存的争用导致
- 确认为latch: cache buffers chains引起的故障后,查看latch的命中率
SQL>SELECT name, gets, misses/gets, sleeps,
immediate_gets, immediate_misses
FROM v$latch
WHERE name = ‘cache buffers chains’;
各列名称意义如下
NAME:latch名称
IMMEDIATE_GETS:以Immediate模式latch请求数
GETS:以Willing to wait请求模式latch的请求数
SPIN_GETS:第一次尝试失败,但在以后的轮次中成功
WAIT_TIME:花费在等待latch的时间
这里需要注意MISSES/GETS如果在达10%左右,则说明有比较严重的latch争用
查找问题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# 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(*) DESC)
WHERE ROWNUM <= 10;
--sql_id对应sql
select sql_fulltext from v$sqlarea where sql_id='&sqlid';
找出对应sql语句,分析其执行计划,根据业务进行针对性优化,最终问题解决