概述

分享一个我平时在数据库出故障从sql直接去找问题会涉及的几个sql，仅供参考。

查看系统等待事件（Wait Events）

SELECT wait_class,
 NAME,
 ROUND(time_secs, 2) time_secs,
 ROUND(time_secs * 100 / SUM(time_secs) OVER(), 2) pct
 FROM (SELECT n.wait_class, e.event NAME, e.time_waited / 100 time_secs
 FROM v$system_event e, v$event_name n
 WHERE n.NAME = e.event
 AND n.wait_class <> 'Idle'
 AND time_waited > 0
 UNION
 SELECT 'CPU', 'server CPU', SUM(VALUE / 1000000) time_secs
 FROM v$sys_time_model
 WHERE stat_name IN ('background cpu time', 'DB CPU'))
 ORDER BY time_secs DESC;

查看系统中，当前有哪些会话产生了db file sequential read等待事件？

select event,
 sid,
 p1,
 p2,
 p3,
 chr(bitand(p1, -16777216) / 16777215) ||
 chr(bitand(p1, 16711680) / 65535) "Name",
 (bitand(p1, 65535)) "Mode"
 from v$session_wait
 where event = 'db file sequential read';

查看系统中的当前会话，是在哪个对象上产生了产生了db file sequential read等待事件？

select ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where event='db file sequential read';

查看前面记录的数据库对象ROW_WAIT_OBJ#具体信息

select object_name,object_id from dba_objects where object_id=178246;

看看SID为79,162的会话到底在执行哪些操作导致db file sequential read等待事件？

select sid, sql_text
 from v$session a, v$sql b
 where sid in(79,162)
 and(b.sql_id = a.sql_id or b.sql_id = a.prev_sql_id);

先从当前TOP N的等待事件分析，有一个大概了解后再去定位相关的sql及会话，后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~