【Oracle】SQL の実行計画を確認する方法｜EXPLAIN PLAN・AUTOTRACE・DBMS_XPLAN・読み方・主要オペレーションまで解説

-- (1) EXPLAIN PLAN を生成
EXPLAIN PLAN FOR
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 5000;

-- (2) 結果を表示
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

| Id | Operation                    | Name          | Rows | Bytes | Cost |
|----|------------------------------|---------------|------|-------|------|
|  0 | SELECT STATEMENT             |               |   30 |  1200 |    5 |
|  1 |  NESTED LOOPS                |               |   30 |  1200 |    5 |
|  2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |   30 |   900 |    3 |
|* 3 |    INDEX RANGE SCAN          | IDX_EMP_SAL   |   30 |       |    1 |
|  4 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS   |    1 |    10 |    1 |
|* 5 |    INDEX UNIQUE SCAN         | PK_DEPT       |    1 |       |    0 |

-- SQL*Plus で有効化
SET AUTOTRACE ON          -- 結果 + 実行計画 + 統計
SET AUTOTRACE ON EXPLAIN  -- 結果 + 実行計画のみ
SET AUTOTRACE ON STATISTICS -- 結果 + 統計のみ
SET AUTOTRACE TRACEONLY   -- 実行計画 + 統計（結果は非表示）

-- SQL を実行すると自動的に計画が表示される
SELECT * FROM employees WHERE salary > 5000;

-- 終了
SET AUTOTRACE OFF

-- SQL を実行
SELECT * FROM employees WHERE salary > 5000;

-- 直前の SQL の実行計画を表示
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

-- sql_id を指定（V$SQL で事前に確認）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('abc123def456', NULL, 'ALLSTATS LAST'));

-- ヒントで実際の行数を収集
SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM employees WHERE salary > 5000;

-- 実際の行数（A-Rows）と見積もり（E-Rows）を比較
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

-- 出力に A-Rows（実際の行数）が追加される
-- E-Rows と A-Rows に大きな乖離がある = 統計情報が不正確

-- sql_id を指定して実行計画を取得
SELECT id, operation, options, object_name,
       cost, cardinality AS est_rows, bytes
FROM v$sql_plan
WHERE sql_id = 'abc123def456'
  AND child_number = 0
ORDER BY id;

-- DBMS_XPLAN.DISPLAY_AWR で過去の計画を取得（AWR）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('abc123def456'));

-- 実行計画は「最もインデントが深い行」から読む
-- 同じインデントレベルでは上から順に実行

-- 例:
-- |  0 | SELECT STATEMENT        |           |  -- 最後に実行
-- |  1 |  NESTED LOOPS           |           |  -- 3 番目
-- |  2 |   TABLE ACCESS FULL     | EMPLOYEES |  -- 1 番目（最深）
-- |  3 |   INDEX UNIQUE SCAN     | PK_DEPT   |  -- 2 番目

-- テーブルの統計情報を更新
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

-- スキーマ全体の統計を更新
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');

-- 統計情報の最終更新日を確認
SELECT table_name, last_analyzed, num_rows
FROM all_tables
WHERE owner = 'HR'
ORDER BY last_analyzed;

-- (1) 遅い SQL の sql_id を V$SQL で特定
SELECT sql_id, elapsed_time/1000000 AS sec, executions,
       SUBSTR(sql_text, 1, 80) AS sql_text
FROM v$sql ORDER BY elapsed_time DESC FETCH FIRST 5 ROWS ONLY;

-- (2) 実行計画を表示
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('abc123', NULL, 'ALLSTATS LAST'));

-- (3) TABLE ACCESS FULL や E-Rows/A-Rows 乖離を確認
-- (4) 必要に応じてインデックス作成 / 統計更新

-- GATHER_PLAN_STATISTICS で実際の計画を確認
SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM orders WHERE customer_id = 100;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

-- INDEX RANGE SCAN が出ていればインデックスが使われている
-- TABLE ACCESS FULL が出ていればインデックスが使われていない

-- AWR に記録された過去の実行計画（EE + Diagnostics Pack）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('abc123def456'));