【Oracle】SQL Plan Management（SPM）完全ガイド｜SQLプランベースライン・DBMS_SPM・DBA_SQL_PLAN_BASELINES まで解説

本番環境で突然 SQL の実行計画が変わり、パフォーマンスが急激に悪化した経験はないでしょうか。統計情報の更新・パッチ適用・ヒストグラムの変化などをきっかけに、オプティマイザが別の実行計画を選択することがあります。

SQL Plan Management（SPM）は、SQL の実行計画をプランベースラインとして管理し、承認されていない新しい計画が使われないように制御する Oracle の機能です。計画の退行（パフォーマンス悪化）を防ぎながら、検証済みのより良い計画への移行もコントロールできます。

この記事でわかること

SQL Plan Management の仕組みと Accepted / Fixed / Enabled の概念
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES でベースラインを自動キャプチャする方法
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE でカーソルキャッシュからベースラインを手動作成する
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE でベースラインを評価・承認する
DBA_SQL_PLAN_BASELINES でベースラインの状態を確認・管理する
Fixed プランで特定の実行計画を強制固定する方法

SQL Plan Management の仕組み
SPM 関連パラメータの確認
自動キャプチャでベースラインを作成する
手動でベースラインを作成する（推奨）
ベースラインの状態を確認する
EVOLVE でベースラインを評価・承認する
Fixed プランで実行計画を強制固定する
ベースラインの削除・SMB 領域管理
実行時にベースラインが使われているか確認する
まとめ — SPM ベースライン管理の運用フロー

SQL Plan Management の仕組み

SPM では SQL ごとにプランベースライン（SQL Plan Baseline）を SYSAUX 表領域のSQL Management Base（SMB）に保管します。SQL が実行されるとき、オプティマイザは通常のコストベース最適化でプランを選択しますが、その SQL にベースラインが存在する場合はベースライン内のプランのみを使用できます。

プランの状態	意味	実行での利用
Accepted	承認済み。使用可能なプラン	使用される（コストが最良のものを選択）
Not Accepted	未承認。新規キャプチャ時の初期状態	使用されない（評価・昇格が必要）
Fixed	固定プラン（Accepted + Fixed）	Fixed なプランが最優先で選択される
Enabled=NO	無効化されたプラン	使用されない

SPM のプラン選択フロー
① オプティマイザがコストベースで最良プランを選択する
② 対象 SQL にベースラインが存在するか確認する
③ ベースラインがある場合、Accepted なプランのみを候補にする
④ Accepted プランの中でコスト最小のプランを使用する
⑤ もし①で選んだプランがベースライン内にない場合、Not Accepted として記録する（自動キャプチャが有効な場合）

SPM 関連パラメータの確認

SPM 関連パラメータを確認する

-- SPM の有効/無効状態を確認する
SHOW PARAMETER optimizer_use_sql_plan_baselines;
-- デフォルト: TRUE（ベースラインが存在する SQL はベースラインに従う）

SHOW PARAMETER optimizer_capture_sql_plan_baselines;
-- デフォルト: FALSE（自動キャプチャは無効）

-- まとめて確認
SELECT name, value, description
FROM V$PARAMETER
WHERE name LIKE 'optimizer_%sql_plan_baselines'
ORDER BY name;

自動キャプチャでベースラインを作成する

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE にすると、2回以上実行された SQL の実行計画を自動的にキャプチャしてベースラインに記録します。最初のキャプチャ時は Accepted、その後別のプランが現れると Not Accepted として追加されます。

自動キャプチャを有効にする（本番では計画的に）

-- セッションレベルで自動キャプチャを有効化（テスト・検証用）
ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;

-- システムレベルで有効化（本番では SMB が肥大化するリスクに注意）
ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = TRUE SCOPE=BOTH;

-- キャプチャを無効に戻す
ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = FALSE SCOPE=BOTH;
-- ※ optimizer_use_sql_plan_baselines=TRUE はそのまま維持することが多い
--   既存ベースラインは引き続き有効になる

手動でベースラインを作成する（推奨）

自動キャプチャはすべての繰り返し SQL を対象にするため、SMB が肥大化しやすいです。本番では重要な SQL のみを手動でベースライン化する方法が推奨されます。

カーソルキャッシュからベースラインを作成する（V$SQLAREA 経由）

-- 対象 SQL を V$SQLAREA で特定する
SELECT sql_id, plan_hash_value, executions, elapsed_time/1000 AS elapsed_ms,
       SUBSTR(sql_text, 1, 80) AS sql_text_head
FROM V$SQLAREA
WHERE sql_text LIKE '%SELECT%EMPLOYEES%'
  AND sql_text NOT LIKE '%V$%'
ORDER BY elapsed_time DESC
FETCH FIRST 5 ROWS ONLY;

-- sql_id と plan_hash_value を指定してベースラインを作成する
DECLARE
    v_cnt PLS_INTEGER;
BEGIN
    -- 特定の sql_id・plan_hash_value のプランをベースラインに登録
    v_cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
        sql_id          => 'abc123def456g',   -- V$SQLAREA で確認した sql_id
        plan_hash_value => 1234567890,         -- 登録したいプランの plan_hash_value
        fixed           => 'NO',               -- YES にすると Fixed プランになる
        enabled         => 'YES'
    );
    DBMS_OUTPUT.PUT_LINE('登録されたベースライン数: ' || v_cnt);
END;
/

sql_id だけ指定してすべてのプランをロードする

-- plan_hash_value を省略するとその sql_id の全プランをロードする
DECLARE
    v_cnt PLS_INTEGER;
BEGIN
    v_cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
        sql_id => 'abc123def456g'
    );
    DBMS_OUTPUT.PUT_LINE('登録数: ' || v_cnt);
END;
/

ベースラインの状態を確認する

DBA_SQL_PLAN_BASELINES でベースラインを確認する

-- 登録されているベースラインの一覧を確認する
SELECT
    sql_handle,           -- SQL のユニーク識別子（SQL テキストのハッシュ）
    plan_name,            -- プランのユニーク名（自動生成）
    sql_text,             -- SQL テキスト（LOB）
    accepted,             -- YES = 承認済み（使用される）
    fixed,                -- YES = 固定プラン（最優先）
    enabled,              -- YES = 有効
    reproduced,           -- YES = 再現可能（EVOLVE 時に検証済み）
    autopurge,            -- YES = 自動パージ対象
    last_executed,        -- 最後に実行された日時
    last_modified,        -- 最後に変更された日時
    created               -- 作成日時
FROM DBA_SQL_PLAN_BASELINES
ORDER BY created DESC
FETCH FIRST 20 ROWS ONLY;

-- 特定の SQL テキストでベースラインを絞り込む
SELECT sql_handle, plan_name, accepted, fixed, enabled
FROM DBA_SQL_PLAN_BASELINES
WHERE UPPER(sql_text) LIKE '%EMPLOYEES%'
ORDER BY created DESC;

ベースラインのプランを DBMS_XPLAN で確認する

-- ベースラインに登録されたプランの詳細を確認する
SELECT * FROM TABLE(
    DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
        sql_handle  => 'SQL_xxxxxxxxxxxxxxxx',  -- DBA_SQL_PLAN_BASELINES の sql_handle
        plan_name   => NULL,                     -- NULL = 全プランを表示
        format      => 'TYPICAL'
    )
);

EVOLVE でベースラインを評価・承認する

Not Accepted なプランを使用するにはEVOLVE（評価）プロセスが必要です。EVOLVE ではプランを実際に実行してパフォーマンスを比較し、既存の Accepted プランより優れている場合に自動承認します。

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE で評価と承認を行う

-- 特定の SQL のベースラインを評価する
DECLARE
    v_report CLOB;
BEGIN
    v_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
        sql_handle  => 'SQL_xxxxxxxxxxxxxxxx',  -- DBA_SQL_PLAN_BASELINES の sql_handle
        plan_name   => NULL,                     -- NULL = 全 Not Accepted プランを対象
        time_limit  => 60,                       -- 評価の最大実行時間（秒）
        verify      => 'YES',                    -- YES = 実際に実行して比較
        commit      => 'YES'                     -- YES = 性能改善プランを自動承認
    );
    -- EVOLVE レポートを表示する
    DBMS_OUTPUT.PUT_LINE(SUBSTR(v_report, 1, 32767));
END;
/

-- verify='NO', commit='NO' で実際の変更を行わずレポートだけ取得する
DECLARE
    v_report CLOB;
BEGIN
    v_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
        sql_handle => 'SQL_xxxxxxxxxxxxxxxx',
        verify     => 'NO',   -- 実行せずにプランの情報だけ確認
        commit     => 'NO'
    );
    DBMS_OUTPUT.PUT_LINE(SUBSTR(v_report, 1, 32767));
END;
/

Not Accepted プランを手動で承認する

-- EVOLVE を使わず直接承認状態に変更する場合
DECLARE
    v_cnt PLS_INTEGER;
BEGIN
    v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
        sql_handle  => 'SQL_xxxxxxxxxxxxxxxx',
        plan_name   => 'SQL_PLAN_xxxxxxxxxx_yyyyyy',  -- 特定のプラン名
        attribute_name  => 'ACCEPTED',
        attribute_value => 'YES'
    );
    DBMS_OUTPUT.PUT_LINE('変更されたプラン数: ' || v_cnt);
END;
/

-- Accepted を取り消す（プランを無効化する場合はこちら）
DECLARE
    v_cnt PLS_INTEGER;
BEGIN
    v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
        sql_handle      => 'SQL_xxxxxxxxxxxxxxxx',
        plan_name       => 'SQL_PLAN_xxxxxxxxxx_yyyyyy',
        attribute_name  => 'ENABLED',
        attribute_value => 'NO'   -- プランを一時的に無効化
    );
    DBMS_OUTPUT.PUT_LINE('変更されたプラン数: ' || v_cnt);
END;
/

Fixed プランで実行計画を強制固定する

Fixed プランはAccepted プランの中で最優先で選択されます。コストベースの評価を無視して特定のプランを強制したい場面（ヒント句を変えずに計画を固定したいなど）に有効です。

Fixed プランを設定する

-- 既存の Accepted プランを Fixed に昇格させる
DECLARE
    v_cnt PLS_INTEGER;
BEGIN
    v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
        sql_handle      => 'SQL_xxxxxxxxxxxxxxxx',
        plan_name       => 'SQL_PLAN_xxxxxxxxxx_yyyyyy',
        attribute_name  => 'FIXED',
        attribute_value => 'YES'
    );
    DBMS_OUTPUT.PUT_LINE('Fixed に変更されたプラン数: ' || v_cnt);
END;
/

-- LOAD_PLANS_FROM_CURSOR_CACHE で作成時に Fixed を指定する
DECLARE
    v_cnt PLS_INTEGER;
BEGIN
    v_cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
        sql_id          => 'abc123def456g',
        plan_hash_value => 1234567890,
        fixed           => 'YES'   -- 作成時から Fixed プランにする
    );
    DBMS_OUTPUT.PUT_LINE('登録数: ' || v_cnt);
END;
/

Fixed プランの注意点

Fixed プランは EVOLVE による自動昇格の対象外。より良いプランがあっても自動では切り替わらない
オプティマイザの改善（統計情報更新・パッチ）の恩恵を受けられなくなる可能性がある
Fixed を設定したら定期的に手動で見直す運用ルールを設けることを推奨する

ベースラインの削除・SMB 領域管理

ベースラインを削除する

-- 特定のプランを削除する
DECLARE
    v_cnt PLS_INTEGER;
BEGIN
    v_cnt := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
        sql_handle => 'SQL_xxxxxxxxxxxxxxxx',
        plan_name  => 'SQL_PLAN_xxxxxxxxxx_yyyyyy'   -- NULL を指定すると sql_handle の全プランを削除
    );
    DBMS_OUTPUT.PUT_LINE('削除されたプラン数: ' || v_cnt);
END;
/

SQL Management Base（SMB）の使用状況を確認する

-- SMB が使用している SYSAUX 領域を確認する
SELECT
    space_used_mb,
    space_budget_mb,             -- SMB に割り当てられた最大サイズ
    plan_retention_weeks,        -- ベースラインの保存期間（週）
    ROUND(space_used_mb / space_budget_mb * 100, 1) AS usage_pct
FROM DBA_SQL_MANAGEMENT_CONFIG;

-- デフォルト: space_budget_mb = SYSAUX の 10%（最大 10GB）
-- デフォルト: plan_retention_weeks = 53（約1年）

-- SMB の設定を変更する（例: 保存期間を26週に短縮）
BEGIN
    DBMS_SPM.CONFIGURE(
        parameter_name  => 'PLAN_RETENTION_WEEKS',
        parameter_value => 26
    );
END;
/

-- SYSAUX の SMB 使用量を詳細確認（コンポーネント別）
SELECT occupant_name, space_used / 1024 / 1024 AS space_mb
FROM V$SYSAUX_OCCUPANTS
WHERE occupant_name LIKE 'SQL_MANAGEMENT%';

実行時にベースラインが使われているか確認する

実行計画にベースライン使用を示す “- baseline” を確認する

-- EXPLAIN PLAN + DBMS_XPLAN でベースライン使用を確認する
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 50;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'TYPICAL'));
-- Note 欄に "SQL plan baseline SQL_PLAN_xxxxx used for this statement" が表示されればベースライン使用中

-- V$SQL でベースライン使用を確認する
SELECT
    sql_id,
    plan_hash_value,
    sql_plan_baseline,    -- 使用されたベースラインのプラン名（NULL = ベースラインなし）
    executions,
    elapsed_time / 1000   AS elapsed_ms,
    SUBSTR(sql_text, 1, 80) AS sql_head
FROM V$SQL
WHERE sql_plan_baseline IS NOT NULL   -- ベースラインが適用されているものだけ
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

まとめ — SPM ベースライン管理の運用フロー

フェーズ	作業	主な手段
キャプチャ	重要 SQL のプランをベースライン化	LOAD_PLANS_FROM_CURSOR_CACHE（手動・推奨）または自動キャプチャ
確認	ベースラインの状態・承認状況を確認	DBA_SQL_PLAN_BASELINES、DISPLAY_SQL_PLAN_BASELINE
評価・承認	新しいプランの性能を評価して承認	EVOLVE_SQL_PLAN_BASELINE または ALTER_SQL_PLAN_BASELINE
固定	特定プランを強制固定	ALTER_SQL_PLAN_BASELINE（FIXED=YES）
メンテナンス	不要ベースラインの削除・SMB 領域管理	DROP_SQL_PLAN_BASELINE、DBA_SQL_MANAGEMENT_CONFIG

自動キャプチャは本番では慎重に：すべての繰り返し SQL をキャプチャするため SMB が肥大化しやすい。重要 SQL のみ手動ロードが推奨
USE_SQL_PLAN_BASELINES=TRUE がデフォルト：ベースラインが存在する SQL は必ずベースライン内のプランから選ばれる
Not Accepted は使われない：EVOLVE または手動承認（ALTER）が必要
Fixed プランは定期見直しを：統計情報やパッチの改善効果が得られなくなるため、長期的な運用ルールを設けることが重要

実行計画の詳細確認方法は DBMS_XPLAN完全ガイドを参照してください。待機イベントを使ったパフォーマンス分析は待機イベント完全ガイドを参照してください。AWR を使った実行計画の履歴確認は AWR・ASH完全ガイドも活用してください。