※本記事は、Nigel Bayliss による”SQL Plan Management Cheat Sheet – Part 1” (2024/1/12)を翻訳したものです。
2026年5月28日

SQL Plan Management Cheat Sheet – Part 1

これは、SQL plan management に関するブログ記事シリーズの第1回です。ここでは、よくある質問に回答し、混乱が生じやすい領域を取り上げます。以下のトピックに関連する質問は本記事では扱いません。これらは別の記事で扱っています。

  • automatic SQL plan management（automatic SPM、real-time SPM を含む）の新機能。
  • 受け入れ済みかつ有効なプランを複数持つ SQL 文については、Part 2 を参照してください。
  • SPM evolve については、Part 2 を参照してください。

SQL Plan Management

SQL plan management（SPM）は、SQL 実行計画を安定させ、別のプランの方が優れていることが証明されない限り、プランが変更されないようにします。SQL plan baseline と呼ばれるオブジェクトを使用して、特定の実行計画の使用を強制します。オプティマイザが SQL plan baseline に記録されたプランとは異なるプランを見つけた場合、そのプランは SQL plan history に取得されます。SPM は、既知のプランを取得し、SQL plan history 内の新しいプランをテストおよび実行し、最終的に使用を許可するプランを管理するための仕組みを提供します。

  • SQL 文のプランを制御するには、少なくとも 1 つの既知の良好なプランを取得し、受け入れる必要があります。SPM は、これまで実行されたことのない SQL 文に対して最良の性能を保証するものではありません。
  • SPM は、複数回実行される「再現可能な」SQL 文で機能します。一般的には、述語でリテラルではなくバインド変数を使用している必要があります。cursor sharing と組み合わせて SPM を使用することも可能です。

SQL Plan Management のライセンスはどうなっていますか？

最新情報については、必ず使用しているデータベース・バージョンの Database Licensing Information User Manual を参照してください。本記事執筆時点では、SQL plan management は Enterprise Edition と Standard Edition 2 で追加ライセンスなしに利用できます。また、Oracle Database 23c Free および BaseDB でも利用できます。

Standard Edition 2 では、非推奨となった stored outline の代替を提供することを目的としているため、一部の機能が制限されています。SE2 の機能範囲は、Table 1-6/Manageability セクションの SQL Plan Management の Notes 列に記載されています。

SQL Plan Baselines

SQL plan baseline は、データ・ディクショナリ・ビュー dba_sql_plan_baselines で確認できます。

select signature, plan_name, accepted, enabled, fixed
from   dba_sql_plan_baselines
where  accepted = 'YES';

内部的には、SQL plan baseline は outline と呼ばれる完全指定のヒントを格納しており、特定の SQL 実行計画を強制します。特定の SQL plan baseline に記録されている実行計画は、次のように確認できます。ここでは dba_sql_plan_baselines の plan_name を使用します。

SELECT *
FROM   table(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE
            (plan_name=>'SQL_PLAN_9nkq1mq5nss4wec1c7603'));

SQL 文のプランに対する SQL plan baseline はいくつかの方法で作成できますが、すばやく簡単な方法は、カーソル・キャッシュ内の SQL 文から作成することです。

var n number
exec :n := dbms_spm.load_plans_from_cursor_cache(sql_id=> …, plan_hash_value => … )

たとえば SQL ID と plan hash value を使用して、複数のソースからプランを取り込むことができます。GitHub の例はこちらです。また、既知の最良プランを見つけて有効化するための新しい「quick fix」プロシージャもあります。

SQL Plan History

dba_sql_plan_baselines ビューには、2 種類の異なるエンティティが表示されます。

  • SQL plan baseline は、個々の SQL 文に対して許可される SQL 実行計画を定義します。dba_sql_plan_baselines では、ACCEPTED 列の値が ‘YES’ であることで示されます。
  • SQL plan history は、オプティマイザが見つけたプランを記録するために使用されます。たとえば、既存の SQL plan baseline がある状態で、オプティマイザが新しい別のプランを見つけた場合、そのプランが記録されます。SQL plan history のエントリは、dba_sql_plan_baselines に ACCEPTED=‘NO’ として記録されます。

SQL plan history のエントリにあるプランを、未受け入れの SQL plan baseline と呼んでも問題ありません。

select signature, plan_name, accepted, enabled, fixed
from   dba_sql_plan_baselines
where  accepted = 'NO';

一部の SQL 文では、データベース内のバインド値やデータの偏りに応じて、複数の SQL 実行計画が必要になる場合があります。そのため、一部の SQL 文が受け入れ済みかつ有効なプランを複数持つことは必要であり、可能でもあります。

ACCEPTED と ENABLED は何を意味しますか？

dba_sql_plan_baselines の ACCEPTED ステータスは、そのプランの使用が承認されていることを示します。ある SQL 文に対して最初のプランが取得されると、そのプランは ACCEPTED=YES としてマークされます。これは、そのプランが良好なプランであるという前提に基づいています。

ACCEPTED ステータスは、SPM evolve のプロセスによって管理されます。このシリーズの後続記事で扱います。また、次のようにして accepted ステータスを強制することもできます。

exec :report := dbsm_spm.evolve_sql_plan_baseline(…verify=>'NO'…)

いったんプランが ACCEPTED=YES になると、ACCEPTED=NO に戻すことはできません。ただし、次のように ENABLED ステータスを使用すれば、受け入れ済みのプランが使用されないようにできます。

exec :n := dbms_spm.alter_sql_plan_baseline('SQL_add0f20cd74a969f',null,'enabled','no')
exec :n := dbms_spm.alter_sql_plan_baseline('SQL_add0f20cd74a969f',null,'enabled','yes')

SQL plan baseline を使用するかどうかを手動で制御したい場合は、ENABLED フラグを使用します。SQL plan baseline を完全に破棄したい場合は、dbms_spm.drop_sql_plan_baseline を使用します。

SQL Plan Baseline の SIGNATURE とは何ですか？

データベースは、exact matching signature を使用して SQL plan baseline と SQL 文を照合します。例を示します。

select exact_matching_signature
from   v$sqlarea
where  sql_text like 'select col1, col2%';

EXACT_MATCHING_SIGNATURE
------------------------
     9739979040767481433

V$SQL/V$SQLAREA の EXACT_MATCHING_SIGNATURE 列を使用して、DBA_SQL_PLAN_BASELINES と結合できます。

select signature
from   dba_sql_plan_baselines
where  signature = (select exact_matching_signature
                    from   v$sqlarea
                    where  sql_text like 'select col1, col2%');

               SIGNATURE
------------------------
     9739979040767481433

signature はプラットフォームに依存せず、SQL テキストから生成されます。その際、空白や SQL 文の大文字小文字の違いは無視されるため、意味的に同一の SQL は同じ plan baseline を共有できます。そのため、複数の文が 1 つの plan baseline に一致することがあり、SQL ID と SIGNATURE の間には多対 1 の関係があります。例を示します。

SQL> select sql_id, exact_matching_signature, sql_text from v$sql;

SQL_ID          EXACT_MATCHING_SIGNATURE SQL_TEXT
------------ --------------------------  -----------------------------------------------
g903r4m0zma2q        2117080876756403865 select   SUM(num) from tab1 where code = :bind1
0tzpp5x6zd4cr        2117080876756403865 select sum(num) from tab1 where code = :bind1

関連する SQL plan baseline を特定するために SQL ID は使用できますか？

よくある質問の 1 つに、「SQL ID を使って、その SQL 文がどの SQL plan baseline を使用するかを特定できますか？」というものがあります。一般的な答えは「いいえ」です。SQL ID は DBA_SQL_PLAN_BASELINES のエントリには格納されておらず、エンコードもされていません。

SIGNATURE が照合メカニズムであるため、上記の 2 つと似た SQL 文が実行された場合に何が起きるかを考えてみます。

SELECT sum(num) FROM tab1 WHERE code = :bind1

この文には、システム内でこれまで見たことのない新しい SQL ID が付与されます。しかし、可能であれば SQL plan baseline を使用させたいはずです。この文の signature は 2117080876756403865 で、上記の文と同じであるため、期待どおり機能します。

SQL テキストから signature を生成し、それを SQL 文とその SQL plan baseline の照合に使用できます。

var signature_v number
begin
   :signature_v :=
      dbms_sqltune.sqltext_to_signature(' SELECT sum(num) FROM tab1 WHERE code = :bind1');
end;
/
select *
from   dba_sql_plan_baselines
where  signature = :signature_v;

まとめると、複数の SQL ID が同じ SQL plan baseline を共有することがあり、SQL plan baseline のエントリには SQL ID 情報はエンコードされていません。

SQL plan baseline が使用されているかどうかは、どう確認できますか？

V$SQL の SQL_PLAN_BASELINE 列には、プランが ACCEPTED の SQL plan baseline と一致した場合に限り、SQL plan baseline の名前が格納されます。
同様に、DBMS_XPLAN の Note セクションにも、SQL plan baseline の名前が表示されます。ただし、これはそのプランが意図したプランである場合に限られます。

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(format=>'typical'));
SQL_ID  b72a70pvwywu2, child number 0
-------------------------------------
select sum(num),count(*) from tab1 where code = :bind1

Plan hash value: 3405812071
-------------------------------------------------------------------
| Id  | Operation                            | Name       | Rows  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |            |       |
|   1 |  SORT AGGREGATE                      |            |     1 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TAB1       |     1 |
|*  3 |    INDEX RANGE SCAN                  | TAB1_CODEI |     1 |
-------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("CODE"=:BIND1)

Note
-----
   - SQL plan baseline SQL_PLAN_769kr0bj4gfj8a077eb5c used for this statement

受け入れ済みで有効な SQL plan baseline が SQL 文に関連付けられているにもかかわらず使用されない場合、その SQL plan baseline が再現不能である可能性があります。これについては後ほど説明します。

DBA_SQL_PLAN_BASELINES の LAST_EXECUTED 列は、SQL plan baseline がプランの再現に最後に成功して使用された日時を示します。この列を解釈する際には注意が必要です。LAST_EXECUTED 列は最大でも週に 1 回しか更新されません。このビュー内の他の動的列についても同様です。特に、対象の問合せが非常にまれにしか実行されない場合に、問題があると早合点しないよう注意してください。

DBA_SQL_PLAN_BASELINES.REPRODUCED 列は、SQL plan baseline のプランが何らかの理由で再現されていないかどうかを示します。詳細は後述しますが、REPRODUCED 列の値がすべてのリリースで信頼できるわけではない点を理解しておくことが重要です。SQL plan baseline が再現不能になっても、この列が ‘NO’ に変わらない場合があります。この問題は bug 30220932 で修正されました（21.1 で修正）。

FIXED は何をしますか？

SQL plan baseline は次のように fixed に設定できます。

n := dbms_spm.alter_sql_plan_baseline('<sql_handle>', '<plan_name>','FIXED','YES')

現在の値は、DBA_SQL_PLAN_BASELINES ビューの FIXED 列で確認できます。

FIXED を YES に設定すると、その SQL plan baseline は特別なものとして扱われます。つまり、「このプランを使いたい。この SQL 文に対して代替プランを使ったり検出したりしたくない」という意思を示します。FIXED の SQL plan baseline は、fixed でない SQL plan baseline より優先して使用されます。

SQL 文に複数の FIXED SQL plan baseline がある場合は、次のようになります。

  • オプティマイザが、固定された SQL plan baseline のいずれにも一致しない新しいプランを選択した場合、各 fixed plan が解析され、その中でコストが最も低いプランが選択されます。

SQL plan baseline に記録されたプランをオプティマイザが使用していないために SQL plan baseline が使用されない、というケースで FIXED=YES が提案されるのをよく見かけます。FIXED=YES にすると、plan enforcement が何らかの形で「強く」なり、特定のプランを保証または強制できると考えられることがあります。しかし、これは正確ではありません。FIXED=YES は、FIXED=NO より fixed SQL plan baseline を優先して選択するようオプティマイザに指示するものです。ただし、特定の実行計画を強制する内部メカニズムは、その plan baseline が fixed であるかどうかに関係なく同じです。FIXED=YES でプランが再現されない場合、FIXED=NO でも再現されません。

オプティマイザが複数の SQL plan baseline から選択できる場合、使用したいものを fixed に設定することで優先度を示すことができます。ただし、私なら通常は ENABLED を使用します。これについては下記で説明します。

fixed にはもう 1 つの効果があります。オプティマイザが、plan baseline や history に存在しない新しいプランを SQL 文に対して見つけた場合、新しい未受け入れの SQL plan baseline が作成されます。ただし、その SQL 文の すべての SQL plan baseline が fixed かつ enabled である場合、新しいプランは SQL plan history に取得されません。

FIXED=YES を使用すべきですか？

fixed は次のシナリオで有用です。

  1. SQL 文に複数の受け入れ済みプランがある。オプティマイザにはその一部だけを使用させたいが、SPM による新しい、より良い可能性のあるプランの取得は引き続き許可したい。evolve によって新しいプランが受け入れられても、優先した fixed plan が引き続き使用されます。このようにして、新しいプランの使用を厳密に制御できます。
  2. automatic evolve task を使用して plan evolution を管理しているが、何らかの理由で、特定の SQL 文について すべての plan baseline を fixed にすることで、個々の SQL 文が新しいプランを取得しないようにしたい。

シナリオ 2 で fixed を使用する場合、新しいプランを取得することによる潜在的なメリットを閉ざしていることを忘れないでください。SQL 文に対するすべての SQL plan baseline を fixed にしなければ、オプティマイザが生成した代替プランは SQL plan history に格納されます。それらのプランは、plan evolution によって受け入れられるまで使用されませんが、その中の 1 つが現在使用中のプランより優れている可能性があります。通常、データは時間とともに変化し、物理的なデータベース設計も変わることがあります。新しい結合順序の方が高性能になるかもしれませんし、新しい索引を活用したい場合もあるでしょう。SPM evolution は、そのような変化に対応します。

新しく取得されたプランが SPM auto evolve によって自動的に受け入れられないようにしたい場合は、次のように設定できます。

BEGIN
   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' ,
      parameter => 'ACCEPT_PLANS',
      value     => 'FALSE');
END;
/

auto SPM evolve が新しいプランを受け入れないようにするのであれば、SQL plan baseline を fixed にする必要性はあまりありません。必要であれば、enable と disable を使って使用するプランを制御できます。新しいプランは取得され、SPM evolve report を確認して、それらがどのような推奨を行うかを確認できます。

SQL 文が SQL plan baseline を使用しているのに、plan hash value が間違っているように見えるのはなぜですか？

DBMS_XPLAN を使用して SQL plan baseline のプランを見ると、plan hash value（PHV）が表示されます。

SELECT *
FROM   table(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE
            (plan_name=> 'SQL_PLAN_5dq34f4n87kc518669c3b'));
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_5dq34f4n87kc518669c3b         Plan id: 409377851
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD-FROM-CURSOR-CACHE
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 1775246573

PHV は SQL 実行計画から生成されるハッシュであるため、SQL 実行計画が同一かどうかを確認するためによく使用されます。とはいえ、V$SQL.PLAN_HASH_VALUE が、SQL plan baseline に対して DBMS_XPLAN が表示する PHV と異なるケースを目にすることがあります。例を示します。

select plan_hash_value, sql_plan_baseline
from   v$sql
where  sql_plan_baseline = 'SQL_PLAN_5dq34f4n87kc518669c3b';

PLAN_HASH_VALUE SQL_PLAN_BASELINE
--------------- --------------------
       97405998 SQL_PLAN_5dq34f4n87kc518669c3b

間違ったプランだと思うかもしれませんが、そうではありません。

V$SQL の plan hash value 列と DBMS_XPLAN の出力に表示される値は通常の plan hash value（PHV）ですが、内部的には SQL plan baseline は plan hash value 2（PHV2）を使用してプランを照合します。

データベースは各 SQL 実行計画について PHV と PHV2 を計算します。PHV の計算に使用されるアルゴリズムは、基盤となるシステムやその他の小さなプラン差異の影響を受けやすい一方、PHV2 の計算に使用されるアルゴリズムは、プラットフォームに依存せず、重要でないプラン差異の影響を受けません。そのため、SPM は PHV2 を使用してプラン照合を行います。
カーソル・キャッシュ内の SQL 文の PHV2 は、V$SQL_PLAN の OTHER_XML 列で確認できます。タグは次のようになります。

<info type="plan_hash_2">409377851</info>

DBMS_XPLAN で表示される “Plan Id:” ラベルの横に 409377851 が表示されます。

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_5dq34f4n87kc518669c3b            Plan id: 409377851     <------ Here
Enabled: YES         Fixed: NO       Accepted: YES   Origin: MANUAL-LOAD-FROM-CURSOR-CACHE 
Plan rows: From dictionary
--------------------------------------------------------------------------------

PHV2 は SQL plan baseline の plan name にエンコードされているため、DBA_SQL_PLAN_BASELINES ディクショナリ・ビューでは直接公開されていません。

SQL Profiles を使用して SQL 実行計画を強制しています。SQL Profiles の方が優れているのではありませんか？

一部の DBA は、SQL profile に outline hint をロードし、ある SQL 文から別の SQL 文へ適用することで実行計画を制御しています。内部的には、SQL plan baseline も hint outline を使用して特定の実行計画を強制します。SQL profile に追加の「魔法」はありません。また、SQL profile にヒントをロードしている場合、たとえ完全な outline を使用していても、得られるプランが意図したプランであると仮定することはできません。SQL profile の仕組みは、SQL Tuning Advisor を使用してカーディナリティ見積りを調整するために作られたものであり、特定のプランを強制するために設計されたものではありません。そのため、SQL profile はプランが再現されていないかどうかを示しません。どのようなプランが得られたとしても、V$SQL には常に非 NULL の SQL_PROFILE 列値が表示され、SQL 実行計画の Note セクションには SQL profile 名が表示されます。

SQL profile は単一のプランを成立させようとします。一方、SPM では、個々の SQL 文に対して複数のプランを持たせることを選択できます。これは、データに偏りがあり、バインド値によって異なるプランが有効になる場合に有益です。さらに、SQL profile には、時間の経過に伴ってプランを「evolve」させる仕組みや、データ量、データ分布、または新しい索引などのデータベース・スキーマ変更に対応する仕組みがありません。時間が経つにつれて、SQL profile は意図したプランを再現できなくなっても、それが静かに発生する可能性があります。

SQL 文に対して SQL profile を作成している場合は、その SQL 文に SQL plan baseline も作成することをお勧めします。その後、SQL profile を無効化できます。SQL plan baseline を使用すれば、プランを固定することも、時間の経過に合わせて evolve させることもできます。また、意図したプランが正しく再現されていない場合にも、それを把握できます。

SQL profile の利点は、force matching によって、バインド変数ではなくリテラル値を使用する複数の SQL 文に 1 つの SQL profile を適用できる点です。SQL がバインド値を使用している場合は、SQL plan baseline を推奨します。

SQL profile を使用して実行計画をある SQL 文から別の SQL 文へコピーする方法に慣れている場合は、SQL plan baseline で同様のことを行う方法を説明しているこちらのブログ記事を参照してください。

SQL plan baseline が再現されないのはなぜですか？

まれに、SQL plan baseline が期待どおり使用されないことがあります。SQL 文に受け入れ済みかつ有効な SQL plan baseline がある場合、必要なプランが使用され、対応するプラン名が V$SQL.SQL_PLAN_BASELINE に表示されるはずです。これが起きない場合、私たちはその SQL plan baseline のプランが「再現不能」であると表現します。

考えられる原因は次のとおりです。

  1. 物理スキーマが変更された。典型的な例は、SQL plan baseline のプランで必要とされる索引が削除された場合です。この場合、そのプランは使用できません。エラーは発生しません。代わりに、オプティマイザが新しいプランを生成し、それを SQL plan history に保存します。その新しいプランが SPM evolution によって受け入れられるまで、V$SQL.SQL_PLAN_BASELINE 列は NULL になります。
  2. まれに、データベースのアップグレードや重要な optimizer patch の適用後に、プランが再現不能になることがあります。重要な optimizer patch は副作用を伴う場合があります。たとえば、重要なパッチによって、特定の条件下で特定の query transformation が使用されなくなることがあります。その結果、SQL plan baseline のプランが再現不能になる場合があります。
  3. バグにより、SQL plan baseline に格納された outline hint が SQL 実行計画を適切に制約できない。SQL plan baseline は、前述の「SQL Profiles を使用して SQL 実行計画を強制しています。SQL Profiles の方が優れているのではありませんか？」のセクションで説明したように、outline hint を使用します。
  4. 一部の SQL 文は SQL plan baseline による制御の対象外です。次の MOS ノートを参照してください。SQL Plan Management (SPM) – SQL Plan Baseline Restrictions/Limitations – Cases Where Plan Baselines will Not be Used (Doc ID 2308153.1)

受け入れ済みの SQL plan baseline のプランが再現不能になった場合、新しい再現可能なプランが SQL plan history に取得されます（ACCEPTED=NO）。このプランは、受け入れられていないにもかかわらず使用されます。オプティマイザには他の選択肢がありません。エラーを投げてアプリケーションを停止させることはしないためです。ほとんどの場合、問合せ性能には大きな影響はありません。そのため、現実的な対処方法としては、SQL plan history に取得された新しいプランを受け入れ、今後はそれを使用することです。SPM evolution に自動的に受け入れさせることも、手動で evolve することもできます。次に、手動で evolve を開始する例を示します。代わりに auto evolve task に任せることもできます。

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
--------------------------------
select col1 from table1 where id = 100

…plan data…

Note
-----
   - Failed to use SQL plan baseline for this statement

SQL> -- We will see the
SQL> select sql_handle,plan_name,accepted
     from dba_sql_plan_baselines;

SQL_HANDLE               PLAN_NAME                         ACC SQL_TEXT
------------------------ --------------------------------- --- ---------
SQL_b0820672080b6cad     SQL_PLAN_b10h6f840qv5d05ce4c2e          YES select…
SQL_b0820672080b6cad     SQL_PLAN_b10h6f840qv5d1f191f3e          NO  select…

SQL> var report clob
SQL> exec :report := dbms_spm.evolve_sql_plan_baseline –
        ('SQL_b0820672080b6cad'-
         ,verify=>'no');

SQL> select sql_handle,plan_name,accepted
     from dba_sql_plan_baselines;

SQL_HANDLE               PLAN_NAME                         ACC SQL_TEXT
------------------------ --------------------------------- --- ---------
SQL_b0820672080b6cad     SQL_PLAN_b10h6f840qv5d05ce4c2e          YES select…
SQL_b0820672080b6cad     SQL_PLAN_b10h6f840qv5d1f191f3e          YES select…

性能低下が発生した場合、原因 #1 が該当する可能性があるかどうかを調べる方法については、こちらのブログ記事で説明しています。パッチ適用後やアップグレード後に、まれに #2 に遭遇することがあります。そのため、性能が低下している場合は、たとえば SQL tuning advisor を使用して問題の SQL 文をチューニングすることをお勧めします。#3 が疑われる場合、または #2 をさらに追跡したい場合は、MOS でパッチ推奨を確認したうえで、Oracle Support に対応してもらう必要があります。特に、まず次のノートを確認してください。Things to Consider to Avoid SQL Plan Management (SPM) Related Problems on 19c (Doc ID 2774029.1)

リンク