【Oracle】DBMS_XPLAN完全ガイド｜DISPLAY・DISPLAY_CURSOR・DISPLAY_AWR・実行計画の読み方まで解説

-- ① EXPLAIN PLAN でプランを PLAN_TABLE に格納
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 > 10000
ORDER BY e.last_name;

-- ② DBMS_XPLAN.DISPLAY で整形表示
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- デフォルト: statement_id=NULL（直近の EXPLAIN PLAN）, format='TYPICAL'

-- BASIC: 最小限（Operation と Name のみ）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'BASIC'));

-- TYPICAL: 標準（デフォルト）Operation, Cost, Card, Bytes, Time, Predicate 情報
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'TYPICAL'));

-- ALL: 最詳細（Column Projection, Alias なども含む）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'ALL'));

-- OUTLINE: SQL ヒントを再現するための Outline Data を表示
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'TYPICAL +OUTLINE'));

-- プランに識別名を付けて保存
EXPLAIN PLAN SET STATEMENT_ID = 'PLAN_A' FOR
SELECT * FROM employees WHERE department_id = 50;

EXPLAIN PLAN SET STATEMENT_ID = 'PLAN_B' FOR
SELECT * FROM employees WHERE salary > 5000;

-- 識別名を指定して表示
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'PLAN_A', 'TYPICAL'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'PLAN_B', 'TYPICAL'));

-- ① 対象の 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 > 10000;

-- ② 直前に実行した SQL のプランを確認（sql_id=NULL → 直前の SQL）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALLSTATS LAST'));

-- ① 対象の SQL の sql_id を取得
SELECT sql_id, sql_text, executions
FROM V$SQL
WHERE sql_text LIKE '%employees%'
  AND sql_text NOT LIKE '%V$SQL%'
ORDER BY last_active_time DESC
FETCH FIRST 5 ROWS ONLY;

-- ② sql_id と child_number を指定してプランを表示
SELECT * FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        sql_id       => 'abc123xyz',  -- V$SQL の sql_id を指定
        cursor_child_no => 0,         -- 通常は 0
        format       => 'ALLSTATS LAST'
    )
);

-- セッション全体で実行統計収集を有効にする（パフォーマンスに影響するため一時的に使用）
ALTER SESSION SET statistics_level = ALL;

-- 対象 SQL を実行
SELECT e.employee_id, e.last_name
FROM employees e
WHERE e.department_id = 50;

-- プランと実行統計を確認
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALLSTATS LAST'));

-- 確認後は設定を戻す
ALTER SESSION SET statistics_level = TYPICAL;

-- ① 対象 SQL の sql_id を DBA_HIST_SQLSTAT から確認
SELECT DISTINCT sql_id, plan_hash_value, snap_id
FROM DBA_HIST_SQLSTAT
WHERE sql_id = 'abc123xyz'
ORDER BY snap_id DESC
FETCH FIRST 10 ROWS ONLY;

-- ② AWR からプランを取得（plan_hash_value を指定して特定バージョンを参照）
SELECT * FROM TABLE(
    DBMS_XPLAN.DISPLAY_AWR(
        sql_id        => 'abc123xyz',
        plan_hash_value => 1234567890,  -- NULL なら全バージョン表示
        db_id         => NULL,          -- NULL = 現在の DB
        format        => 'TYPICAL'
    )
);

-- AWR に蓄積されている plan_hash_value の種類を確認
-- plan_hash_value が変わっている = プランが切り替わったタイミング
SELECT
    s.snap_id,
    TO_CHAR(sn.begin_interval_time, 'YYYY-MM-DD HH24:MI') AS snap_time,
    s.plan_hash_value,
    s.executions_delta,
    ROUND(s.elapsed_time_delta / NULLIF(s.executions_delta, 0) / 1000000, 2) AS avg_elapsed_sec
FROM DBA_HIST_SQLSTAT s
JOIN DBA_HIST_SNAPSHOT sn ON s.snap_id = sn.snap_id
WHERE s.sql_id = 'abc123xyz'
ORDER BY s.snap_id;

-- ① 現在実行中のセッションの sql_id を確認
SELECT s.sid, s.sql_id, s.sql_child_number, s.event, s.seconds_in_wait
FROM V$SESSION s
WHERE s.status = 'ACTIVE'
  AND s.wait_class != 'Idle'
ORDER BY s.seconds_in_wait DESC;

-- ② 該当セッションのプランを確認
SELECT * FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        sql_id          => 'abc123xyz',   -- ①で取得した sql_id
        cursor_child_no => 0,
        format          => 'TYPICAL'
    )
);

-- EXPLAIN PLAN と DISPLAY を連続実行（SQL*Plus / SQLcl で使用）
EXPLAIN PLAN FOR
SELECT COUNT(*) FROM orders WHERE order_date >= DATE '2024-01-01';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'TYPICAL'));

-- バインド変数を使ったクエリを実行
VARIABLE b_dept NUMBER;
EXEC :b_dept := 50;

SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM employees
WHERE department_id = :b_dept;

-- 実際に使われたプランを確認（バインドピークされた値でのプラン）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALLSTATS LAST +PEEKED_BINDS'));
-- +PEEKED_BINDS: バインドピークした値を出力に含める（Oracle 11g 以降）