【Oracle】EXPLAIN PLAN 完全ガイド｜DBMS_XPLAN.DISPLAY・実行計画の読み方・チューニングへの活用まで解説

-- 実行計画を取得する（SQL は実行されない）
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
ORDER BY e.salary DESC;

-- 実行計画を整形して表示する
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- フォーマットオプションを指定する（ALL: 最も詳細、BASIC: 最小限）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'BASIC'));

-- STATEMENT_ID を指定して複数の計画を管理する
EXPLAIN PLAN SET STATEMENT_ID = 'query_01' FOR
SELECT * FROM employees WHERE department_id = 60;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(statement_id => 'query_01'));

-- 実行計画の出力例:
--
-- Plan hash value: 3502854950
--
-- ---------------------------------------------------------------------------------
-- | Id  | Operation                    | Name         | Rows | Bytes | Cost (%CPU)|
-- ---------------------------------------------------------------------------------
-- |   0 | SELECT STATEMENT             |              |   10 |   640 |     8   (13)|
-- |   1 |  SORT ORDER BY               |              |   10 |   640 |     8   (13)|
-- |*  2 |   HASH JOIN                  |              |   10 |   640 |     7   (15)|
-- |   3 |    TABLE ACCESS FULL         | DEPARTMENTS  |   27 |   432 |     3    (0)|
-- |*  4 |    TABLE ACCESS FULL         | EMPLOYEES    |   10 |   480 |     4    (0)|
-- ---------------------------------------------------------------------------------
--
-- Predicate Information (identified by operation id):
-- ---------------------------------------------------
--    2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
--    4 - filter("E"."SALARY">5000)
--
-- 列の意味:
-- Id:        各操作のステップ番号（* が付くと述語条件あり）
-- Operation: 実行する処理の種類
-- Name:      対象のテーブル名またはインデックス名
-- Rows:      オプティマイザが推定する出力行数（E-Rows）
-- Bytes:     推定バイト数
-- Cost:      処理コスト（数値が小さいほど効率的）
-- %CPU:      CPU コストの割合

-- 読み方のポイント:
-- 1. インデントが最も深い行（Id=3, 4）から順に実行される
-- 2. * が付いた Id（2, 4）の述語情報は Predicate Information 欄で確認する
-- 3. HASH JOIN（Id=2）の場合、先に取得した DEPARTMENTS の結果をハッシュ表に格納し
--    EMPLOYEES の結果とマッチングする

-- ① 直前に実行した SQL の実行計画を確認する
SELECT * FROM employees WHERE department_id = 60;

-- 直前の SQL の SQL_ID と CHILD_NUMBER を確認する
SELECT prev_sql_id, prev_child_number
FROM V$SESSION
WHERE sid = SYS_CONTEXT('USERENV', 'SID');

-- SQL_ID を使って実行計画を取得する
-- （NULL を渡すと直前に実行した SQL が対象になる）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
    sql_id      => NULL,        -- NULL: 直前の SQL, または 'abc123xyz01' などの SQL_ID
    cursor_child_no => 0,       -- 通常は 0
    format      => 'ALLSTATS LAST'  -- 実測値を含む詳細フォーマット
));

-- ② V$SQL から特定の SQL の SQL_ID を調べて確認する
SELECT sql_id, child_number, sql_text
FROM V$SQL
WHERE sql_text LIKE '%employees%department_id%'
  AND sql_text NOT LIKE '%V$SQL%'  -- このクエリ自体を除外
ORDER BY last_active_time DESC;

-- 調べた SQL_ID で実行計画を確認する
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
    sql_id      => 'abc123xyz01',
    cursor_child_no => 0,
    format      => 'TYPICAL'
));

-- format オプションの主な値:
-- BASIC:    最小限（Operation・Name のみ）
-- TYPICAL:  標準（デフォルト）
-- ALL:      最大詳細（Predicate・Column・Alias 含む）
-- ALLSTATS LAST: 実測値（A-Rows・A-Time）を表示（GATHER_PLAN_STATISTICS と組み合わせる）

-- GATHER_PLAN_STATISTICS ヒントを付けて SQL を実行する（実測値の収集が有効になる）
SELECT /*+ GATHER_PLAN_STATISTICS */
    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
ORDER BY e.salary DESC;

-- 直前の SQL の実行計画を ALLSTATS LAST フォーマットで確認する
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

-- 出力例（A-Rows: 実際の行数, E-Rows: 推定行数, A-Time: 実際の時間）:
--
-- | Id | Operation            | Name        | Starts | E-Rows | A-Rows | A-Time       |
-- |----|----------------------|-------------|--------|--------|--------|--------------|
-- |  0 | SELECT STATEMENT     |             |      1 |        |     10 | 00:00:00.03  |
-- |  1 |  SORT ORDER BY       |             |      1 |     10 |     10 | 00:00:00.03  |
-- |* 2 |   HASH JOIN          |             |      1 |     10 |     10 | 00:00:00.02  |
-- |  3 |    TABLE ACCESS FULL | DEPARTMENTS |      1 |     27 |     27 | 00:00:00.01  |
-- |* 4 |    TABLE ACCESS FULL | EMPLOYEES   |      1 |     10 |    107 | 00:00:00.01  |
--                                                         ↑推定   ↑実際
-- Id=4 の E-Rows=10（推定）に対して A-Rows=107（実際）→ 推定が大きく外れている
-- → salary > 5000 のカラムヒストグラムが取れていない可能性がある
-- → DBMS_STATS.GATHER_TABLE_STATS(..., method_opt => 'FOR COLUMNS salary SIZE AUTO') で対処

-- Starts: 操作が何回実行されたか（NESTED LOOPS の内側は Starts > 1 になる）
-- Buffers: 読み込んだブロック数（I/O の指標）
-- OMem/1Mem: SORT/HASH JOIN のメモリ使用量（大きいと TEMP 使用量増加）

-- V$SQL_PLAN の構造を確認する
-- SQL_ID + CHILD_NUMBER + ID の組み合わせで一意になる
SELECT id, parent_id, depth, operation, options, object_name,
       cardinality, bytes, cost, access_predicates, filter_predicates
FROM   V$SQL_PLAN
WHERE  sql_id = 'abc123xyz01'
  AND  child_number = 0
ORDER BY id;

-- TABLE ACCESS FULL が多い SQL を探す（インデックス未使用の疑い）
SELECT p.sql_id, p.object_name, s.sql_text
FROM   V$SQL_PLAN p
JOIN   V$SQL s ON p.sql_id = s.sql_id AND p.child_number = s.child_number
WHERE  p.operation = 'TABLE ACCESS'
  AND  p.options   = 'FULL'
  AND  p.object_name NOT IN ('DUAL')
  AND  s.last_active_time > SYSDATE - 1/24  -- 直近1時間
ORDER BY s.executions DESC;

-- HASH JOIN が多い SQL のコストTop10 を確認する
SELECT sql_id, sql_text, cost
FROM (
    SELECT DISTINCT p.sql_id, s.sql_text, p.cost
    FROM   V$SQL_PLAN p
    JOIN   V$SQL s ON p.sql_id = s.sql_id AND p.child_number = s.child_number
    WHERE  p.operation = 'HASH JOIN'
    ORDER BY p.cost DESC
) WHERE ROWNUM <= 10;

-- V$SQL_PLAN_STATISTICS_ALL: GATHER_PLAN_STATISTICS の実測値をリアルタイムに確認する
SELECT operation, options, object_name,
       output_rows AS a_rows, starts,
       elapsed_time / 1000000 AS elapsed_sec
FROM   V$SQL_PLAN_STATISTICS_ALL
WHERE  sql_id = 'abc123xyz01'
  AND  child_number = 0
ORDER BY id;

-- ① 問題のある SQL をそのまま実行して計画を確認する
EXPLAIN PLAN SET STATEMENT_ID = 'before_tune' FOR
SELECT *
FROM   orders
WHERE  TO_CHAR(order_date, 'YYYY-MM') = '2026-01';  -- 関数ベースインデックスがない場合は FULL SCAN

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(statement_id => 'before_tune'));
-- → TABLE ACCESS FULL（order_date に TO_CHAR を適用しているのでインデックスが使えない）

-- ② 書き換えた SQL で計画を確認する
EXPLAIN PLAN SET STATEMENT_ID = 'after_tune' FOR
SELECT *
FROM   orders
WHERE  order_date >= DATE '2026-01-01'
  AND  order_date <  DATE '2026-02-01';  -- 範囲条件に変更

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(statement_id => 'after_tune'));
-- → INDEX RANGE SCAN（order_date のインデックスが使える）

-- ③ 関数ベースインデックスで対応する方法もある（SQL 変更できない場合）
CREATE INDEX orders_yearmonth_idx ON orders (TO_CHAR(order_date, 'YYYY-MM'));
-- → インデックスを作成後、元の SQL でも INDEX RANGE SCAN が使えるようになる

-- ヒント句で一時的に計画を変更する（本番環境でのテストに便利）
SELECT /*+ INDEX(e emp_dept_idx) */
    employee_id, salary
FROM employees e
WHERE department_id = 60;
-- → ヒントで特定インデックスを強制使用させて計画の違いを確認する

-- ① 現在の実行計画をベースラインに保存する（SQL_ID が必要）
-- まず SQL を実行してキャッシュに載せる
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM employees WHERE department_id = 60;

-- SQL_ID を確認する
SELECT sql_id FROM V$SQL
WHERE sql_text LIKE '%department_id = 60%'
  AND sql_text NOT LIKE '%V$SQL%'
ORDER BY last_active_time DESC
FETCH FIRST 1 ROWS ONLY;

-- ベースラインに登録する
DECLARE
    ret PLS_INTEGER;
BEGIN
    ret := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
        sql_id          => 'abc123xyz01',
        plan_hash_value => NULL  -- NULL: すべての子カーソルを登録
    );
    DBMS_OUTPUT.PUT_LINE('登録数: ' || ret);
END;
/

-- ② 登録したベースラインを確認する
SELECT sql_handle, plan_name, enabled, accepted, fixed,
       created, last_executed, sql_text
FROM   DBA_SQL_PLAN_BASELINES
ORDER BY created DESC;

-- ③ 不要になったベースラインを削除する
DECLARE
    ret PLS_INTEGER;
BEGIN
    ret := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
        sql_handle => 'SQL_abc123xyz',
        plan_name  => 'SQL_PLAN_abc123xyz_01'
    );
END;
/
-- SPM の詳細は Oracle Diagnostics + Tuning Pack のライセンスが必要な場合がある