【Oracle】待機イベント（Wait Events）完全ガイド｜V$SESSION_WAIT・V$SYSTEM_EVENT・主要イベント別の原因と対処まで解説

-- V$SESSION_WAIT: 各セッションが今何を待っているかをリアルタイムで確認
SELECT
    sw.sid,
    sw.seq#,           -- 待機シーケンス番号（増加するほど待機が発生している）
    sw.event,          -- 待機イベント名
    sw.wait_class,     -- 待機クラス（User I/O, Concurrency, Application, Idle など）
    sw.p1text,         -- P1 の意味（例: file# / lock type など）
    sw.p1,             -- P1 の値
    sw.p2text,         -- P2 の意味
    sw.p2,
    sw.p3text,         -- P3 の意味
    sw.p3,
    sw.wait_time,      -- 0 = 現在待機中 / 正の値 = 最後の待機時間（1/100秒）
    sw.seconds_in_wait -- 現在の待機継続時間（秒）
FROM V$SESSION_WAIT sw
WHERE sw.wait_class != 'Idle'         -- Idle 待機を除外
  AND sw.seconds_in_wait > 0          -- 実際に待機しているセッションのみ
ORDER BY sw.seconds_in_wait DESC;

-- 誰が・何のSQLで・何を待っているかを一括確認
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.event,
    s.wait_class,
    s.seconds_in_wait,
    s.sql_id,
    SUBSTR(sq.sql_text, 1, 60) AS sql_preview
FROM V$SESSION s
LEFT JOIN V$SQL sq ON s.sql_id = sq.sql_id
WHERE s.wait_class != 'Idle'
  AND s.status = 'ACTIVE'
ORDER BY s.seconds_in_wait DESC;

-- 特定の待機イベントに絞り込む（例: ロック待ち）
SELECT s.sid, s.username, s.event, s.seconds_in_wait, s.sql_id
FROM V$SESSION s
WHERE s.event LIKE 'enq: TX%'          -- 行ロック競合
ORDER BY s.seconds_in_wait DESC;

-- Idle を除いた累積待機時間 Top 20（インスタンス全体のボトルネック分析）
SELECT
    event,
    wait_class,
    total_waits,
    total_timeouts,
    ROUND(time_waited / 100, 2)         AS time_waited_sec,    -- 1/100秒 → 秒に変換
    ROUND(average_wait / 100, 3)        AS avg_wait_sec,       -- 平均待機時間（秒）
    ROUND(time_waited / 100
          / NULLIF(total_waits, 0), 4)  AS avg_per_wait_sec    -- 1回あたりの待機時間
FROM V$SYSTEM_EVENT
WHERE wait_class != 'Idle'
ORDER BY time_waited DESC
FETCH FIRST 20 ROWS ONLY;

-- 特定の期間の待機を差分で取得する（スナップショット方式）
-- ① 計測開始時点のスナップショットをテーブルに保存
CREATE TABLE wait_snap AS
SELECT event, total_waits, time_waited FROM V$SYSTEM_EVENT WHERE wait_class != 'Idle';

-- ② 計測期間後に差分を確認
SELECT
    cur.event,
    cur.total_waits - NVL(snap.total_waits, 0) AS delta_waits,
    ROUND((cur.time_waited - NVL(snap.time_waited, 0)) / 100, 2) AS delta_sec
FROM V$SYSTEM_EVENT cur
LEFT JOIN wait_snap snap ON cur.event = snap.event
WHERE cur.wait_class != 'Idle'
  AND cur.total_waits - NVL(snap.total_waits, 0) > 0
ORDER BY delta_sec DESC
FETCH FIRST 20 ROWS ONLY;

-- 現在最も I/O 待機しているセッションを特定する
SELECT
    s.sid,
    s.username,
    s.event,
    s.seconds_in_wait,
    s.sql_id,
    DBMS_ROWID.ROWID_BLOCK_NUMBER(
        DBMS_ROWID.ROWID_CREATE(1,
            s.p1,   -- ファイル番号
            s.p2,   -- ブロック番号（db file sequential read の P2）
            0)
    )  AS block_num   -- 待機中のブロック番号（参考）
FROM V$SESSION s
WHERE s.event IN ('db file sequential read', 'db file scattered read')
  AND s.wait_time = 0    -- 現在待機中
ORDER BY s.seconds_in_wait DESC;

-- P1（ファイル番号）からどのテーブル・インデックスを読んでいるか確認
SELECT file_id, tablespace_name, file_name
FROM DBA_DATA_FILES
WHERE file_id = 4;   -- P1 の値を指定

-- ロックを待っているセッションと保持しているセッションを特定
SELECT
    wait.sid          AS waiting_sid,
    wait.username     AS waiting_user,
    wait.seconds_in_wait,
    hold.sid          AS blocking_sid,
    hold.username     AS blocking_user,
    hold.status       AS blocking_status,
    SUBSTR(sq.sql_text, 1, 60) AS blocking_sql
FROM V$SESSION wait
JOIN V$SESSION hold ON wait.blocking_session = hold.sid
LEFT JOIN V$SQL sq   ON hold.sql_id = sq.sql_id
WHERE wait.event LIKE 'enq: TX%'
ORDER BY wait.seconds_in_wait DESC;

-- ブロッキングセッションを強制終了する（緊急時）
-- ALTER SYSTEM KILL SESSION 'blocking_sid,blocking_serial#' IMMEDIATE;

-- DB time（CPU時間 + 非アイドル待機時間）の内訳を確認
SELECT
    stat_name,
    ROUND(value / 1000000, 2) AS seconds   -- マイクロ秒 → 秒に変換
FROM V$SYS_TIME_MODEL
WHERE stat_name IN (
    'DB time',           -- CPU + 非アイドル待機の合計
    'DB CPU',            -- CPU で処理した時間
    'sql execute elapsed time',   -- SQL実行にかかった時間
    'parse time elapsed'          -- SQL解析にかかった時間
)
ORDER BY seconds DESC;

-- チューニングの指針:
-- DB time の大半が DB CPU → CPU バウンド（SQL の論理的な最適化が有効）
-- DB time の大半が待機   → I/O・ロック・ラッチのボトルネック解消が有効

-- 直近のAWRスナップショット間の待機イベント差分を取得
SELECT
    e.event_name,
    e.wait_class,
    e.total_waits_fg        - b.total_waits_fg       AS delta_waits,
    ROUND((e.time_waited_fg - b.time_waited_fg)
          / 100, 2)                                  AS delta_sec,
    ROUND((e.time_waited_fg - b.time_waited_fg)
          / NULLIF(e.total_waits_fg - b.total_waits_fg, 0)
          / 100, 4)                                  AS avg_wait_sec
FROM DBA_HIST_SYSTEM_EVENT e
JOIN DBA_HIST_SYSTEM_EVENT b
  ON e.event_name = b.event_name
 AND e.snap_id = b.snap_id + 1     -- 隣接するスナップショット間の差分
 AND e.instance_number = b.instance_number
WHERE e.wait_class != 'Idle'
  AND e.snap_id = (SELECT MAX(snap_id) FROM DBA_HIST_SNAPSHOT)
ORDER BY delta_sec DESC
FETCH FIRST 15 ROWS ONLY;