✨ 2.2 Oracle数据库等待事件检查

一、实时等待事件检查（11g 兼容）

1.1 查看自实例启动以来的 Top 等待事件

-- 使用 ROWNUM 实现 Top N
SELECT * FROM (
  SELECT event, 
         total_waits, 
         time_waited_micro/1000000 AS time_waited_sec,
         average_wait_micro/1000 AS avg_wait_ms,
         wait_class
  FROM v$system_event
  WHERE wait_class != 'Idle'
  ORDER BY time_waited_micro DESC
)
WHERE ROWNUM <= 10;

1.2 查看当前正在发生的等待事件（非空闲）

SELECT s.sid,
       s.serial#,
       s.username,
       s.program,
       sw.event,
       sw.wait_time_micro/1000000 AS wait_sec,
       sw.state,
       sw.seconds_in_wait,
       s.sql_id
FROM v$session s,
     v$session_wait sw
WHERE s.sid = sw.sid
  AND sw.event NOT LIKE '%Idle%'
  AND s.status = 'ACTIVE'
ORDER BY sw.seconds_in_wait DESC;

1.3 针对特定等待事件定位 SQL

SELECT s.sid,
       s.serial#,
       s.username,
       sw.event,
       sw.seconds_in_wait,
       sq.sql_text
FROM v$session s,
     v$session_wait sw,
     v$sql sq
WHERE s.sid = sw.sid
  AND s.sql_id = sq.sql_id (+)
  AND sw.event = '&event_name'   -- 替换为具体等待事件
  AND sw.seconds_in_wait > 10
ORDER BY sw.seconds_in_wait DESC;

二、历史等待事件分析（ASH / AWR）

2.1 ASH – 最近1小时内 Top 等待事件

SELECT * FROM (
  SELECT event, COUNT(*) AS sample_count
  FROM v$active_session_history
  WHERE sample_time > SYSDATE - 1/24
  GROUP BY event
  ORDER BY sample_count DESC
)
WHERE ROWNUM <= 10;

2.2 查看指定时间段的等待事件趋势

SELECT TO_CHAR(sample_time, 'HH24:MI') AS time_slot,
       event,
       sql_id,
       COUNT(*) AS wait_count
FROM v$active_session_history
WHERE sample_time BETWEEN SYSDATE - 2/24 AND SYSDATE
GROUP BY TO_CHAR(sample_time, 'HH24:MI'), event, sql_id
ORDER BY time_slot DESC, wait_count DESC;

若数据量大，可添加 WHERE ROWNUM <= 100 限制输出。

2.3 生成 ASH 报告（11g 同样支持）

-- 交互式生成 ASH 报告
@?/rdbms/admin/ashrpt.sql

2.4 查看 AWR 快照中的等待事件历史

SELECT * FROM (
  SELECT snap.snap_id,
         TO_CHAR(snap.begin_interval_time, 'YYYY-MM-DD HH24:MI') AS begin_time,
         se.event,
         se.total_waits,
         se.time_waited_micro/1000000 AS time_waited_sec
  FROM dba_hist_system_event se,
       dba_hist_snapshot snap
  WHERE se.snap_id = snap.snap_id
    AND se.wait_class != 'Idle'
    AND snap.begin_interval_time > SYSDATE - 7
  ORDER BY snap.snap_id DESC, se.time_waited_micro DESC
)
WHERE ROWNUM <= 20;

三、11g 特有的等待事件诊断视图

3.1 SQL Monitor（11g 引入）

-- 查看被监控的 SQL（默认执行超过 5 秒）
SELECT sql_id,
       sql_text,
       status,
       elapsed_time/1000000 AS elapsed_sec,
       cpu_time/1000000 AS cpu_sec,
       buffer_gets,
       disk_reads
FROM v$sql_monitor
WHERE status = 'EXECUTING'
ORDER BY last_refresh_time DESC;

-- 生成 SQL Monitor 报告（需输入 sql_id）
SET LONG 1000000
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => '&sql_id') FROM dual;

3.2 阻塞链分析 – V$WAIT_CHAINS（11g 新增）

-- 查看当前阻塞链（无需 hanganalyze）
SELECT * FROM v$wait_chains;

四、11g 常用等待事件及处理建议（汇总）

五、一个完整的 11g 等待事件检查脚本

SET PAGES 100 LINES 200
COL event FORMAT A40
COL wait_class FORMAT A15
COL sql_text FORMAT A50

PROMPT ==================== Top 10 System Wait Events ====================
SELECT * FROM (
  SELECT event, total_waits,
         ROUND(time_waited_micro/1000000, 2) AS time_waited_sec,
         ROUND(average_wait_micro/1000, 2) AS avg_wait_ms,
         wait_class
  FROM v$system_event
  WHERE wait_class NOT IN ('Idle', 'Network')
  ORDER BY time_waited_micro DESC
) WHERE ROWNUM <= 10;

PROMPT ==================== Current Active Sessions Waiting ====================
SELECT s.sid,
       s.serial#,
       s.username,
       sw.event,
       ROUND(sw.seconds_in_wait, 0) AS wait_sec,
       s.sql_id,
       SUBSTR(sq.sql_text, 1, 50) AS sql_text
FROM v$session s,
     v$session_wait sw,
     v$sql sq
WHERE s.sid = sw.sid
  AND s.sql_id = sq.sql_id (+)
  AND sw.event NOT LIKE '%Idle%'
  AND sw.wait_time_micro > 0
  AND s.status = 'ACTIVE'
ORDER BY sw.seconds_in_wait DESC;

PROMPT ==================== Wait Chain Analysis ====================
SELECT * FROM v$wait_chains;

六、11g 诊断流程小结

1. 整体等待​：从 v$system_event 找出 Top 5 等待事件（使用 ROWNUM 子查询）。
2. 实时会话​：查询 v$session_wait 查看当前正在等待的会话。
3. 定位 SQL​：通过 sql_id​ 关联 v$sql 获取 SQL 文本。
4. 执行计划​：使用 EXPLAIN PLAN FOR​ 或 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id', child_no)); 分析。
5. 历史趋势​：通过 ASH (v$active_session_history) 和 AWR 历史视图分析等待变化。

注意：所有语句均已去除 12c+ 特性（如 FETCH FIRST​），完全兼容 Oracle 11g。若遇到权限问题，请确保用户拥有 SELECT ANY DICTIONARY 或相应视图的访问权限。