※本記事は、Nigel Bayliss による”SQL Plan Management Cheat Sheet – Part 2” を翻訳したものです。
2024年6月27日

はじめに

この記事では、SQL計画管理における計画の取得、複数の計画を持つSQL文、SPMの進化について扱います。

基本概念については、パート1を参照してください。

自動SQL計画管理については、こちらを参照してください。

計画の取得

いずれかの時点で、SQL文の初期SQL計画ベースラインを取得したくなるでしょう。つまり、ワークロードが初期の「良好な」計画セットを使い続けるようにしたい場合です。システムが良好に稼働しているときに、次のデータベース・パラメータを設定するのが簡単な方法です。

optimizer_capture_sql_plan_baselines = TRUE

1回を超えて実行されたSQL文について、計画が取得されます。1つのSQL文に複数の計画がある場合、最初にハード解析された計画は ACCEPTED=YES になり、それ以外は ACCEPTED=NO になります。この取得方法では、新しいSQL文または実行計画が検出されるたびに、データベースがSQL計画ベースラインの詳細を永続化する必要があるため、わずかなハード解析のオーバーヘッドがあります。

意図した以上に取得してしまうリスクはありますが、セッション・レベルでパラメータを設定して範囲を制限したり、フィルタを使用して取得対象のSQL文の範囲を制御したりできます（DBMS_SPM.CONFIGURE を使用）。

他の方法では、AWR、SQLチューニング・セット、あるいはカーソル・キャッシュから個々の計画を直接取得できます。たとえば、load_plans_from_cursor_cache を使用すると、任意の条件に基づいてSQL文の計画をロードし、受け入れることができます。すぐに有効化する必要もありません。次に例を示します。

declare
   n number;
begin
   for rec in (select sql_id
               from   v$sqlarea
               where  executions > 10000
               and    parsing_schema_name = 'THE_APPUSER')
   loop
      n := dbms_spm.load_plans_from_cursor_cache(
                 sql_id   => rec.sql_id,
                 enabled  => 'NO');
   end loop;
end;
/

このようにして、計画の問題が発生した場合にすばやく解決できるように、いくつかのSQL計画ベースラインを準備しておくことができます。

検討できる他の方法もあります。

  • アプリケーションのワークロードをSQLチューニング・セットに取得し、そこからSQL計画ベースラインを作成する。
  • AWRから計画を取得する。この方法では、ワークロード内で最もリソースを消費する問合せの計画を簡単に取得できます。

これらのソースから取得されたすべての計画は、ただちに ACCEPTED=YES としてマークされますが、有効化するかどうかは制御できます。

複数の計画を持つSQL文

バインド・センシティブとしてマークされたSQL文では、バインド変数の値に応じて複数のSQL実行計画が必要になることがあります。この機能は、適応カーソル共有（Adaptive Cursor Sharing）として知られています。SQL計画管理は、1つのSQL文に複数の計画を関連付けられるため、この機能と連携します。アプリケーションの問合せが、偏りの大きいデータセットをフィルタする次のシナリオを考えてみます。

SQL> -- この問合せのSQL計画ベースラインを取得します
SQL> alter session set optimizer_capture_sql_plan_baselines=true;
SQL> -- バインド値 1 は表内の1行のみに一致します
SQL> exec :bind1 := 1;
SQL> -- 自動取得されるには、SQL文を複数回実行する必要があります
SQL> select sum(num) from tab1 where code = :bind1;
SQL> select sum(num) from tab1 where code = :bind1;
  SUM(NUM)   COUNT(*)
---------- ----------
         1          1
SQL> -- 自動取得を終了します
SQL> alter session set optimizer_capture_sql_plan_baselines=false;
SQL> -- SQL計画ベースラインが作成されています
SQL> select plan_name, accepted, sql_text
     from dba_sql_plan_baselines;
PLAN_NAME                     ACC SQL_TEXT
------------------------------ --- ------------------------------------
SQL_PLAN_769kr0bj4gfj8a077eb5c YES select sum(num),count(*) from tab1 where code = :bind1
SQL> -- バインド値 0 は10000行に一致するため、この場合は
SQL> -- 索引を使用しない計画のほうが適しています
SQL> exec :bind1 := 0;
SQL> -- 問合せを複数回実行すると、ACSが別の計画を識別します
SQL> select sum(num),count(*) from tab1 where code = :bind1;
SQL> …
  SUM(NUM)   COUNT(*)
---------- ----------
  50005000      10000
SQL> -- 新しい計画はSQL計画履歴にあります
SQL> select plan_name, accepted, sql_text
     from dba_sql_plan_baselines;
PLAN_NAME                     ACC SQL_TEXT
------------------------------ --- ------------------------------------
SQL_PLAN_769kr0bj4gfj8a077eb5c YES select sum(num),count(*) from tab1 where code = :bind1
SQL_PLAN_769kr0bj4gfj8ec1c7603 NO  select sum(num),count(*) from tab1 where code = :bind1

新しい計画がSQL計画履歴に表示されるようになりました（ACCEPTED=NO）。この代替計画が取得されたのは、このSQL文に既存の有効なSQL計画ベースラインがあるためです。上の例では、optimizer_capture_sql_plan_baselines が FALSE に設定されていても、dba_sql_plan_baselines にSQL計画履歴のエントリが表示されていることがわかります。

SPMの進化

SPMの進化プロセスは、SQL計画履歴内の新しい計画を受け入れる前に、その計画が既存のSQL計画ベースラインの計画よりも優れた性能を示すことを確認する役割を担います。

念頭に置いておくべき点がいくつかあります。

  • 該当SQL文のすべてのSQL計画ベースラインが FIXED かつ ENABLED の場合、そのSQL文について新しい計画はSQL計画履歴に取得されません。
  • FIXED=YES かつ ENABLED=NO のSQL計画ベースラインは、固定計画とはみなされません。
  • ある文の既存の固定されていないSQL計画ベースラインが ENABLED=NO であっても、新しい計画はSQL計画履歴に取得されます。
  • 上の例では、bind = 0 に対する最適な計画は、計画 SQL_PLAN_769kr0bj4gfj8ec1c7603 が進化して受け入れられるまで使用されません（通常はメンテナンス・ウィンドウ中に行われます）。このため、オプティマイザによって新たに検出された、より良い計画が使用可能になるまでには、一定の時間が経過します。

進化とは、新しい計画をバックグラウンドでテスト実行し、既存の計画よりも優れているかどうかを確定するプロセスです。既存の計画よりも優れた性能を示すことが証明された計画については、ACCEPTED ステータスが YES に設定されます。つまり、データベースはコスト見積りだけに依存するのではなく、計画の性能をテストして比較し、どれが最良かを判断します。

進化は手動で開始できます。

SQL> var tname varchar2(100)
SQL> …
declare
   ename varchar2(100);
begin
   :tname := dbms_spm.create_evolve_task();
   ename  := dbms_spm.execute_evolve_task(:tname);
end;
/

SQL計画履歴内の計画によって無視できない性能改善が得られる場合、新しい計画ベースラインが作成されます。

SQL> select plan_name, accepted, sql_text
     from dba_sql_plan_baselines;
PLAN_NAME                     ACC SQL_TEXT
------------------------------ --- ------------------------------------
SQL_PLAN_769kr0bj4gfj8a077eb5c YES select sum(num),count(*) from tab1 where code = :bind1
SQL_PLAN_769kr0bj4gfj8ec1c7603 YES select sum(num),count(*) from tab1 where code = :bind1

SPMレポートを使用すると、進化中に何が起きたかを確認できます。

SQL> set long 100000
SQL> select dbms_spm.report_evolve_task(task_name=>:tname) report
     from dual;

自動進化

自動タスクによって、SQL計画履歴内の計画を進化させることができます。とはいえ、どの新しい計画を受け入れるかを厳密に制御したい場合は、いつでも次のようにできます。

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

これにより、データベースは新しい計画を取得し、バックグラウンドでテストできますが、自動的に受け入れられる計画はなくなります。どの計画がより優れているかを示すレポートを利用できます。

report := dbms_spm.report_auto_evolve_task( … );

レポート内の推奨事項に基づいて、受け入れる計画を選択できます。

複数の計画を持つSQL文 – 適応カーソル共有と統計フィードバック

前述のとおり、適応カーソル共有（ACS）により、Oracle Databaseはバインド変数の値に基づいて異なるSQL実行計画を使用できます。これは、データベース内のデータ値に範囲の偏りや値の偏りがある状況で最も有効です。その結果、一部のSQL文は複数の子カーソルと複数のSQL実行計画を持つことになります。SPMは、各SQL文が複数の受入れ済み計画を持てるため、この状況に対応できます。

SQL文が解析されるとき、オプティマイザは、SQL計画ベースラインによって特定の計画が強制されることなく、予測コストが最も低い計画を選択します。オプティマイザが選択した計画に一致するSQL計画ベースラインがある場合、その計画が使用されます。選択された計画がSQL計画ベースラインに一致しない場合（かつ複数の計画ベースラインが使用可能な場合）、SQL計画ベースラインの計画の中から、予測コストが最も低い計画が選択されます（ただし、固定SQL計画ベースラインが優先されます）。このように、SQL計画ベースラインは、使用可能な計画を制約する仕組みとして機能します。

SQL文が複数回実行される過程で、オプティマイザのACS機構はバインド変数の値を使用してコスト計算に影響を与えることがあります。その結果、それぞれ異なる計画を持つ新しい子カーソルが作成される場合があります。また、統計フィードバックという別の機能も、複数回の実行を通じてオプティマイザに影響を与えることがあります。これらの仕組みは連携して透過的に動作します。全体としては、個々のSQL文が最終的な状態に至るまでに、複数の異なる計画を経ることがあります。さらに、異なるバインド変数値に対応するために、時間の経過とともに複数の異なる計画が作成される場合もあります。それでも、これらの処理中も、SQL計画ベースラインは使用可能な計画を制約し続けます。

要約すると、ACSと統計フィードバックは時間の経過とともに計画を調整しますが、それらの計画を使用できるのは、一致するSQL計画ベースラインが存在する場合に限られます。したがって、複数の計画を持つSQL文は、ACSと統計フィードバックが柔軟に動作する余地を与えます。個々のSQL文が時間の経過とともに計画を変更し、異なるバインド値を考慮した子カーソルを持つことが可能になります。

その帰結として、計画が落ち着くまでの間に性能に多少のばらつきが生じることがありますが、これは正常であり、SPMが正しく動作していないことを示すものではありません。

とはいえ、まれに、複数の計画を経由する遷移が、アプリケーション性能に明確で目に見える悪影響を与えることがあります。このような状況では、通常、使用可能な計画の選択肢を最小限に抑え、最適でない性能を示すことがわかっているSQL計画ベースラインを無効にするのが最善です。SQL文が使用可能なSQL計画ベースラインの中から最適でない計画を選択している場合は、次のように追跡できます。

SQL> -- explain planのNoteセクションを確認します
SQL> select *
     from   table(dbms_xplan.display_cursor(sql_id=>'0tzpp5x6zd4cr'));
…
…
Note
-----
   - SQL plan baseline SQL_PLAN_1usb1uwtxd1nta077eb5c used for this statement
SQL> -- SQL計画ベースラインを特定します
SQL> select *
     from  dba_sql_plan_baselines
     where plan_name = 'SQL_PLAN_1usb1uwtxd1nta077eb5c';
SQL> -- 複数の候補計画があるかどうかを確認します
SQL> select count(*)
     from dba_sql_plan_baselines
     where accepted = 'YES'
     and   enabled  = 'YES'
     and   signature = (select signature
                        from   dba_sql_plan_baselines
                        where  plan_name = 'SQL_PLAN_1usb1uwtxd1nta077eb5c');
SQL> -- 特定のSQL計画ベースラインを無効化できます
SQL> declare
        n  number;
     begin
        n := dbms_spm.alter_sql_plan_baseline(
                 plan_name      =>'SQL_PLAN_1usb1uwtxd1nta077eb5c',
                 attribute_name =>'enabled',attribute_value=>'no');
     end;
     /

一部のバインド変数値に対して良好に機能する計画を無効にしてしまう可能性があるのは事実です。しかし、単一または少数の「十分に良い」SQL計画ベースラインを使用することで、ほとんどの場合は良好な結果が期待できます。この方法では改善しにくいSQL文については、最適でない計画を回避する別の方法を特定するために、従来型のSQLチューニング手法が必要になる可能性があります。

複数の計画を持つSQL文 – ハード解析時間

一部のSQL文には、多数の潜在的な実行計画があります。特に、表結合の数が多く、データセットに偏りがあるケースでよく見られると言えるでしょう。バインド値によっては、異なる結合順序やアクセス方法が有効になることがあります。前のセクションで述べたように、適応カーソル共有はこの状況に対応します。

あるSQL文に多数の受入れ済みSQL計画ベースラインがある一方で、オプティマイザが定期的に未受入れの計画を選択するシナリオを想像してください。この場合、受入れ済みの各計画が解析され、使用された特定のバインド変数値に対してどの計画のコストが最も低いかが判断されます。最もコストの低い計画が選択されます（オプティマイザが最初に選択した計画は受け入れられていないためです）。その結果、ハード解析フェーズは通常より長くなります。

これはまれなシナリオですが、場合によっては一部の計画を無効化し、個々のSQL文に対する受入れ済み計画ベースラインの数を「十分に良い」中核的な計画セットに抑えることが有益です。