※本記事は、Nigel Bayliss による”SQL Plan Management 4 of 4 – User Interfaces and Other Features” (2024/12/8)を翻訳したものです。

この連載の最初の 3 回では、SQL plan baseline がどのように作成され、利用され、進化していくかを見てきました。最終回となる今回は、いくつかのユーザー・インターフェースを紹介し、SPM がほかの機能とどのように連携するかを説明するとともに、皆さんから寄せられた質問にお答えします。

DBMS_SPM パッケージ

新しいパッケージ DBMS_SPM を使うと、プラン履歴を管理できます。これまでの例でも見てきたように、このパッケージを使って SQL plan baseline を作成したり進化させたりできます。ほかにも、プランの属性（有効化状態やプラン名など）を変更したり、プランを削除したりする管理機能が用意されています。このパッケージを実行するには、ADMINISTER SQL MANAGEMENT OBJECT 権限が必要です。

プラン履歴の表示
プラン履歴がどのように作成されたかに関係なく、各プランの詳細はビュー DBA_SQL_PLAN_BASELINES で確認できます。この連載の Part 3 の最後では、SQL 文に 2 つの accepted plan があることを確認しました。

SQL> select sql_text, sql_handle, plan_name, enabled, accepted
  2  from dba_sql_plan_baselines;
SQL_TEXT                  SQL_HANDLE               PLAN_NAME                     ENA ACC
------------------------  ------------------------ ----------------------------- --- ---
select p.prod_name, s.am  SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b08cbcb825 YES YES
ount_sold, t.calendar_year
from sales s, products p
, times t where s.prod_id
= p.prod_id
  and s.time_id =
t.time_id
  and p.prod_id < :pid
select p.prod_name, s.am  SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES
ount_sold, t.calendar_year
from sales s, products p
, times t where s.prod_id
= p.prod_id
  and s.time_id =
t.time_id
  and p.prod_id < :pid

SQL handle は各 SQL 文を一意に識別するための識別子で、DBMS_SPM パッケージを使ってプラン履歴を管理するときに利用します。

SQL テキストを変更して accepted plan を作成する
ヒントを追加するなどして SQL 文を手動でチューニングし、SQL テキスト自体を変更している方もいるでしょう。自動 SQL プラン捕捉を有効にしてその文を実行すると、その変更後の SQL 文に対する SQL plan baseline が作成されます。

しかし多くの場合、必要なのはそのプランを元の SQL 文のプラン履歴へ追加することです。先ほどの SQL 文を例に、その手順を見てみましょう。まず SQL 文を変更して実行し、そのプランを確認します。

SQL> var pid number
SQL> exec :pid := 100;

PL/SQL procedure successfully completed.

SQL> select /*+ leading(t) */ p.prod_name, s.amount_sold, t.calendar_year
2    from sales s, products p, times t
3    where s.prod_id = p.prod_id
4      and s.time_id = t.time_id
5      and p.prod_id < :pid;

PROD_NAME AMOUNT_SOLD CALENDAR_YEAR
--------- ----------- -------------
...
9 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('b17wnz4y8bqv1', 0, 'basic note'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+ leading(t) */ p.prod_name, s.amount_sold, t.calendar_year
from sales s, products p, times t where s.prod_id = p.prod_id   and
s.time_id = t.time_id   and p.prod_id < :pid

Plan hash value: 2290436051
---------------------------------------------------------------
| Id  | Operation                            | Name           |
---------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |
|   1 |  HASH JOIN                           |                |
|   2 |   HASH JOIN                          |                |
|   3 |    TABLE ACCESS FULL                 | TIMES          |
|   4 |    PARTITION RANGE ALL               |                |
|   5 |     TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |
|   6 |      BITMAP CONVERSION TO ROWIDS     |                |
|   7 |       BITMAP INDEX RANGE SCAN        | SALES_PROD_BIX |
|   8 |   TABLE ACCESS BY INDEX ROWID        | PRODUCTS       |
|   9 |    INDEX RANGE SCAN                  | PRODUCTS_PK    |
---------------------------------------------------------------

これで、変更後の SQL 文のプランを元の SQL 文の sql_handle（DBA_SQL_PLAN_BASELINES から取得）に関連付けることで、元の SQL 文に対する新しい accepted plan を作成できます。

SQL> var pls number
SQL> exec :pls := dbms_spm.load_plans_from_cursor_cache( -
>                    sql_id => 'b17wnz4y8bqv1', -
>                    plan_hash_value => 2290436051, -
>                    sql_handle => 'SYS_SQL_4bf04d85fcc170b0');

元の SQL 文にまだプラン履歴がなく（したがって SQL handle もない）場合は、load_plans_from_cursor_cache の別バージョンを使って、元の文のテキストを指定できます。では、この SQL 文に accepted plan が 3 つになったことを DBA_SQL_PLAN_BASELINES で確認してみましょう。

SQL> select sql_text, sql_handle, plan_name, enabled, accepted
  2  from dba_sql_plan_baselines;
SQL_TEXT                  SQL_HANDLE               PLAN_NAME                     ENA ACC
------------------------  ------------------------ ----------------------------- --- ---
select p.prod_name, s.am  SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b0888547d3 YES YES
ount_sold, t.calendar_year
from sales s, products p
, times t
where s.prod_id =
p.prod_id
  and s.time_id =
t.time_id
  and p.prod_id < :pid
select p.prod_name, s.am  SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b08cbcb825 YES YES
ount_sold, t.calendar_year
from sales s, products p
, times t
where s.prod_id =
p.prod_id
  and s.time_id =
t.time_id
  and p.prod_id < :pid
select p.prod_name, s.am  SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES
ount_sold, t.calendar_year
from sales s, products p
, times t
where s.prod_id =
p.prod_id
  and s.time_id =
t.time_id
  and p.prod_id < :pid

プランの表示

オプティマイザが SQL 文に対して accepted plan を使用した場合、その内容はプラン表（EXPLAIN 用）または V$SQL_PLAN（共有カーソル用）で確認できます。では、先ほどの SQL 文を EXPLAIN して、そのプランを表示してみます。

SQL> explain plan for
  2  select p.prod_name, s.amount_sold, t.calendar_year
  3  from sales s, products p, times t
  4  where s.prod_id = p.prod_id
  5    and s.time_id = t.time_id
  6    and p.prod_id < :pid;

Explained.
SQL> select * from table(dbms_xplan.display('plan_table', null, 'basic note'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 2787970893
----------------------------------------------------------------
| Id  | Operation                             | Name           |
----------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |
|   1 |  NESTED LOOPS                         |                |
|   2 |   NESTED LOOPS                        |                |
|   3 |    HASH JOIN                          |                |
|   4 |     TABLE ACCESS BY INDEX ROWID       | PRODUCTS       |
|   5 |      INDEX RANGE SCAN                 | PRODUCTS_PK    |
|   6 |     PARTITION RANGE ALL               |                |
|   7 |      TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |
|   8 |       BITMAP CONVERSION TO ROWIDS     |                |
|   9 |        BITMAP INDEX RANGE SCAN        | SALES_PROD_BIX |
|  10 |    INDEX UNIQUE SCAN                  | TIME_PK        |
|  11 |   TABLE ACCESS BY INDEX ROWID         | TIMES          |
----------------------------------------------------------------
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_fcc170b0a62d0f4d" used for this statement

末尾の Note から、オプティマイザが accepted plan を使用したことが分かります。プラン履歴には複数のプランが含まれることがあります。オプティマイザが実行用に選択した accepted plan の 1 つは確認できますが、そのほかのプランの一部または全部を表示したい場合はどうすればよいでしょうか。これには、DBMS_XPLAN パッケージの display_sql_plan_baseline 関数を使用できます。先ほどの例を使って、プラン履歴内のすべてのプランを表示する方法を見てみましょう。

SQL> select *
  2  from table(dbms_xplan.display_sql_plan_baseline(
  3               sql_handle => 'SYS_SQL_4bf04d85fcc170b0', format => 'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_4bf04d85fcc170b0
SQL text: select p.prod_name, s.amount_sold, t.calendar_year from sales s,
          products p, times t where s.prod_id = p.prod_id   and s.time_id =
          t.time_id   and p.prod_id < :pid
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_fcc170b0888547d3
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 2290436051
---------------------------------------------------------------
| Id  | Operation                            | Name           |
---------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |
|   1 |  HASH JOIN                           |                |
|   2 |   HASH JOIN                          |                |
|   3 |    TABLE ACCESS FULL                 | TIMES          |
|   4 |    PARTITION RANGE ALL               |                |
|   5 |     TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |
|   6 |      BITMAP CONVERSION TO ROWIDS     |                |
|   7 |       BITMAP INDEX RANGE SCAN        | SALES_PROD_BIX |
|   8 |   TABLE ACCESS BY INDEX ROWID        | PRODUCTS       |
|   9 |    INDEX RANGE SCAN                  | PRODUCTS_PK    |
---------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_fcc170b08cbcb825
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 2361178149
------------------------------------------
| Id  | Operation             | Name     |
------------------------------------------
|   0 | SELECT STATEMENT      |          |
|   1 |  HASH JOIN            |          |
|   2 |   HASH JOIN           |          |
|   3 |    PARTITION RANGE ALL|          |
|   4 |     TABLE ACCESS FULL | SALES    |
|   5 |    TABLE ACCESS FULL  | TIMES    |
|   6 |   TABLE ACCESS FULL   | PRODUCTS |
------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_fcc170b0a62d0f4d
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 2787970893
----------------------------------------------------------------
| Id  | Operation                             | Name           |
----------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |
|   1 |  NESTED LOOPS                         |                |
|   2 |   NESTED LOOPS                        |                |
|   3 |    HASH JOIN                          |                |
|   4 |     TABLE ACCESS BY INDEX ROWID       | PRODUCTS       |
|   5 |      INDEX RANGE SCAN                 | PRODUCTS_PK    |
|   6 |     PARTITION RANGE ALL               |                |
|   7 |      TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |
|   8 |       BITMAP CONVERSION TO ROWIDS     |                |
|   9 |        BITMAP INDEX RANGE SCAN        | SALES_PROD_BIX |
|  10 |    INDEX UNIQUE SCAN                  | TIME_PK        |
|  11 |   TABLE ACCESS BY INDEX ROWID         | TIMES          |
----------------------------------------------------------------

パラメータ

SPM を制御するためのパラメータは 2 つあります。1 つ目は optimizer_capture_sql_plan_baselines で、デフォルト値は FALSE です。このパラメータを使うと、プランを自動的に捕捉できます。SPM は、実行された繰り返し SQL 文をすべて管理対象にし、それぞれのプラン履歴を作成します。最初に捕捉されたプランは自動的に accepted になります。これに対し、その後に見つかったプランは、evolve されるまで accepted にはなりません。2 つ目のパラメータは optimizer_use_sql_plan_baselines で、デフォルト値は TRUE です。

このパラメータにより、SPM 対応オプティマイザは SQL 文のコンパイル時に SQL plan baseline が利用可能であればそれを使えます。このパラメータを FALSE に設定すると、SPM 対応オプティマイザは無効になり、通常のコストベース・オプティマイザが使用されます。この場合、推定コストに基づいて最適なプランが選択されます。

SPM と SQL プロファイル

1 つの SQL 文が SQL プロファイルと SQL plan baseline の両方を持つことがあります。Part 3 では、SQL プロファイルを受け入れることで SQL plan baseline を evolve させる例を紹介しました。この場合、SPM 対応オプティマイザは SQL プロファイルと SQL plan baseline の両方を使用します。SQL プロファイルには、accepted plan のそれぞれをより正確にコスト評価し、その中から最適なものを選ぶのに役立つ追加情報が含まれています。

SQL プロファイルが存在する場合、SPM 対応オプティマイザは、SQL プロファイルが存在しない場合とは異なる accepted plan を選択することがあります。

SPM と Stored Outline

1 つの SQL 文が stored outline と SQL plan baseline の両方を持つこともあります。ある SQL 文に stored outline が存在し、それが有効化されている場合、オプティマイザはそれを使用します。stored outline を使っている場合は、SQL plan baseline を作成して stored outline を無効化することで SPM をテストできます。SPM に満足できれば、stored outline を削除してもよいですし、無効のまま残しても構いません。もし SPM が期待どおりに動作しない場合（その理由はぜひお知らせいただきたいところですが）、stored outline を再度有効化できます。

stored outline を使っている場合は、その制約も理解しておく必要があります。まず、ある SQL 文に対して同時に持てる stored outline は 1 つだけです。状況によってはそれで十分かもしれませんが、文がさまざまな条件（たとえば bind 値）で実行される場合、単一のプランが常に最適とは限りません。2 つ目の制約はこれに関連しています。stored outline では進化が行えません。

つまり、より良いプランが存在しても、stored outline は使われ続けるため、システム性能を低下させる可能性があります。より良いプランを得るには、現在の stored outline を手動で削除し、新しいものを生成しなければなりません。また、stored outline で使用されていたアクセス・パス（たとえば索引）が削除されるか、何らかの理由で使用不能になった場合でも、不完全な stored outline が使われ続け、結果として大幅に悪いプランになることがあります。

読者からよく寄せられる質問の 1 つに、Oracle が stored outline 機能を今後どうする予定なのか、というものがあります。stored outline は、将来のリリースで SQL plan management を優先する形で desupported になる予定です。Oracle Database 12c Release 2 では、stored outline は従来のリリースと同様に引き続き機能します。ただし Oracle は、新規アプリケーションでは SQL plan management を使用することを強く推奨しています。SQL plan management は SQL plan baseline を作成し、stored outline と比べてより優れた SQL パフォーマンスと安定性を提供します。

既存の stored outline がある場合は、DBMS_SPM パッケージの LOAD_PLANS_FROM_CURSOR_CACHE または LOAD_PLANS_FROM_SQLSET プロシージャを使って SQL plan baseline へ移行することを検討してください。移行が完了したら、stored outline は無効化または削除すべきです。

SPM と Adaptive Cursor Sharing

Adaptive cursor sharing（ACS）は、bind-sensitive な SQL 文に対して、単一のプランがすべての条件で最適ではないと判断されると、複数のカーソルを生成することがあります。各カーソルは、その文に対して hard parse を強制することで生成されます。通常、オプティマイザは hard parse のたびに最もコストの低いプランを選択します。ある文に SQL plan baseline がある場合、SPM 対応オプティマイザは accepted plan の中から最適なプランを選択します。

これは bind-sensitive な文の hard parse にも当てはまります。accepted plan が複数あり、それぞれが異なる bind set に対して最適である場合があります。SPM と ACS の両方が有効になっていれば、SPM 対応オプティマイザは現在の bind set に最適なプランを選択します。したがって hard parse が発生した場合でも、その文が bind sensitive かどうかに関係なく、通常の SPM プラン選択アルゴリズムが使用されます。

参考情報