【Oracle】遅い SQL を特定する方法完全ガイド｜V$SQL・V$SESSION・AWR・ASH・ADDM・Statspack まで解説

SELECT * FROM (
    SELECT
        sql_id,
        ROUND(elapsed_time / 1000000, 2) AS elapsed_sec,
        executions,
        ROUND(elapsed_time / NULLIF(executions, 0) / 1000000, 2) AS avg_sec,
        ROUND(cpu_time / 1000000, 2) AS cpu_sec,
        disk_reads,
        buffer_gets,
        SUBSTR(sql_text, 1, 100) AS sql_text_short
    FROM v$sql
    ORDER BY elapsed_time DESC
)
WHERE ROWNUM <= 10;

SELECT * FROM (
    SELECT sql_id,
           ROUND(cpu_time/1000000, 2) AS cpu_sec,
           executions,
           ROUND(cpu_time/NULLIF(executions,0)/1000000, 2) AS avg_cpu_sec,
           SUBSTR(sql_text, 1, 100) AS sql_text_short
    FROM v$sql
    ORDER BY cpu_time DESC
)
WHERE ROWNUM <= 10;

SELECT * FROM (
    SELECT sql_id,
           disk_reads,
           executions,
           ROUND(disk_reads/NULLIF(executions,0)) AS avg_disk_reads,
           buffer_gets,
           SUBSTR(sql_text, 1, 100) AS sql_text_short
    FROM v$sql
    ORDER BY disk_reads DESC
)
WHERE ROWNUM <= 10;

SELECT * FROM (
    SELECT sql_id,
           executions,
           ROUND(elapsed_time/NULLIF(executions,0)/1000000, 2) AS avg_elapsed_sec,
           ROUND(cpu_time/NULLIF(executions,0)/1000000, 2) AS avg_cpu_sec,
           ROUND(buffer_gets/NULLIF(executions,0)) AS avg_buffer_gets,
           SUBSTR(sql_text, 1, 100) AS sql_text_short
    FROM v$sql
    WHERE executions > 0
    ORDER BY elapsed_time/executions DESC
)
WHERE ROWNUM <= 10;

-- V$SQL の sql_text は先頭 1000 文字のみ
-- 完全なテキストは V$SQLTEXT で取得
SELECT sql_text
FROM v$sqltext
WHERE sql_id = 'abc123def456'
ORDER BY piece;

-- または V$SQL_FULLTEXT（CLOB）
SELECT sql_fulltext FROM v$sql WHERE sql_id = 'abc123def456';

-- アクティブセッションと実行中 SQL の一覧
SELECT
    s.sid, s.serial#,
    s.username,
    s.status,
    s.event,
    s.wait_class,
    s.seconds_in_wait,
    s.sql_id,
    SUBSTR(q.sql_text, 1, 80) AS sql_text_short
FROM v$session s
LEFT JOIN v$sql q ON s.sql_id = q.sql_id AND s.sql_child_number = q.child_number
WHERE s.status = 'ACTIVE'
  AND s.type = 'USER'
ORDER BY s.seconds_in_wait DESC;

-- アプリケーションユーザーの実行中 SQL に絞る
SELECT s.sid, s.sql_id, s.event,
       s.seconds_in_wait AS wait_sec,
       q.elapsed_time/1000000 AS total_elapsed_sec,
       SUBSTR(q.sql_text, 1, 100) AS sql_text
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.username = 'APP_USER'
  AND s.status = 'ACTIVE';

-- 5 秒以上実行中または並列実行の SQL が自動記録される
SELECT
    sql_id,
    status,
    username,
    ROUND(elapsed_time/1000000, 1) AS elapsed_sec,
    ROUND(cpu_time/1000000, 1) AS cpu_sec,
    buffer_gets,
    disk_reads,
    sql_text
FROM v$sql_monitor
WHERE status = 'EXECUTING'
ORDER BY elapsed_time DESC;

-- 特定 SQL の詳細レポート（HTML 形式）
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
    sql_id => 'abc123def456',
    type   => 'TEXT'
) AS report
FROM DUAL;
-- 実行計画の各ステップの進捗率や待機時間が確認できる

-- SQL*Plus で AWR レポートを生成
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

-- 対話形式で以下を入力:
-- レポート形式: html（推奨）
-- 日数: 1（直近 1 日のスナップショット一覧）
-- 開始スナップショット ID: 100
-- 終了スナップショット ID: 102（例: 2 時間分）
-- レポートファイル名: awr_report.html

-- 直近 1 時間で最も待機時間が長い SQL
SELECT sql_id,
       COUNT(*) AS sample_count,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct,
       MAX(event) AS top_event
FROM v$active_session_history
WHERE sample_time >= SYSDATE - INTERVAL '1' HOUR
  AND sql_id IS NOT NULL
GROUP BY sql_id
ORDER BY sample_count DESC
FETCH FIRST 10 ROWS ONLY;

-- 特定の sql_id で何を待っていたかを分析
SELECT event, wait_class,
       COUNT(*) AS samples,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct
FROM v$active_session_history
WHERE sql_id = 'abc123def456'
  AND sample_time >= SYSDATE - INTERVAL '1' HOUR
GROUP BY event, wait_class
ORDER BY samples DESC;

-- 特定時間帯の ASH レポートを生成
SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql
-- 開始時刻と終了時刻を指定 → Top SQL / Top Events が出力される

-- ADDM レポートを生成
SQL> @$ORACLE_HOME/rdbms/admin/addmrpt.sql
-- スナップショット範囲を指定すると、推奨事項付きレポートが生成される

-- 結果例:
-- FINDING 1: SQL ID "abc123" consumed 45% of DB Time
-- RECOMMENDATION: Consider creating an index on ORDERS(CUSTOMER_ID)

-- (1) Statspack のインストール（初回のみ）
-- SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql

-- (2) スナップショットの手動取得
EXEC STATSPACK.SNAP;

-- (3) レポートの生成
SQL> @$ORACLE_HOME/rdbms/admin/spreport.sql
-- 開始/終了のスナップショット ID を指定

-- (4) 自動スナップショット（DBMS_JOB で 1 時間ごと）
-- SQL> @$ORACLE_HOME/rdbms/admin/spauto.sql

-- (1) アクティブセッションを確認
SELECT sid, username, sql_id, event, seconds_in_wait
FROM v$session
WHERE status = 'ACTIVE' AND type = 'USER'
ORDER BY seconds_in_wait DESC;

-- (2) 特定した sql_id の SQL テキストを確認
SELECT sql_fulltext FROM v$sql WHERE sql_id = 'abc123def456';

-- (3) 実行計画を確認
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('abc123def456'));

-- (1) スナップショット ID を確認
SELECT snap_id, begin_interval_time
FROM dba_hist_snapshot
WHERE begin_interval_time >= TRUNC(SYSDATE-1) + 17/24  -- 昨日 17:00
  AND begin_interval_time <= TRUNC(SYSDATE-1) + 19/24  -- 昨日 19:00
ORDER BY snap_id;

-- (2) AWR レポートを生成
-- SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
-- 開始 snap_id / 終了 snap_id を指定

-- 毎朝実行: 前日の Top 10 SQL をログテーブルに記録
INSERT INTO sql_perf_log (log_date, sql_id, elapsed_sec, executions, avg_sec)
SELECT TRUNC(SYSDATE-1), sql_id,
       ROUND(elapsed_time/1000000, 2),
       executions,
       ROUND(elapsed_time/NULLIF(executions,0)/1000000, 2)
FROM (
    SELECT sql_id, elapsed_time, executions
    FROM dba_hist_sqlstat s
    JOIN dba_hist_snapshot sn ON s.snap_id = sn.snap_id
    WHERE sn.begin_interval_time >= TRUNC(SYSDATE-1)
      AND sn.begin_interval_time <  TRUNC(SYSDATE)
    ORDER BY elapsed_time DESC
)
WHERE ROWNUM <= 10;
COMMIT;