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

はじめに

この投稿では、SQL plan management の基本を取り上げます。automatic SQL plan management についてさらに知りたい場合は、こちらの投稿で説明しています。すでに基本をご存じであれば、チートシートも役立つでしょう。

実行計画が悪い方向に変わってしまい、性能劣化を経験したことはないでしょうか。そうした場合に有効な洗練された解決策が、SQL Plan Management（SPM）です。これから続く 4 本のブログ記事では、SPM を詳しく解説していきます。まずは、実行計画が変わる主な原因を確認しましょう。

実行計画の変更は、さまざまなシステム変更によって発生します。たとえば、optimizer 関連パラメータを変更したり、データベースをアップグレードしたりした場合です。こうした変更により、多くの SQL 文に対して新しい実行計画が生成される可能性があります。新しい計画の大半は、新しいシステム環境に適応した改善版になるはずですが、一部は悪化し、性能劣化を引き起こすことがあります。

DBA には、こうした性能劣化に対処するための選択肢がいくつかあります。しかし、ほとんどの DBA が望むことは単純です。性能向上につながる場合にだけ計画が変わってほしい、ということです。言い換えれば、optimizer は悪い計画を選ぶべきではありません。

この連載の第 1 回では、SQL Plan Management の概念と SQL plan baseline の作成方法を説明します。第 2 回では、これらの SQL plan baseline がどのように、またどのタイミングで使われるのかを説明します。第 3 回では、新しく改善された計画を SQL plan baseline に追加する evolution について扱います。最後の第 4 回では、ユーザー・インターフェースと、stored outline など他の Oracle オブジェクトとの連携について説明します。

SQL Plan Management

SQL Plan Management（SPM）を使うと、データベース・ユーザーは一連の SQL 文に対して、安定しつつ最適な性能を維持できます。SPM は、計画適応性と計画安定性の長所を取り込みながら、それぞれの短所を同時に回避します。主な目的は次の 2 つです。

  • データベース・システムの変更があっても性能劣化を防ぐこと
  • データベース・システムの変更にスムーズに適応し、性能向上をもたらすこと

managed SQL statement とは、SPM が有効化されている SQL 文のことです。SPM は自動で動作するように構成することもできますし、完全または部分的に手動で制御することもできます。SPM は、managed SQL statement に対して計画変更を検出できるようにすることで、性能劣化の防止を支援します。そのために、SPM は各 managed SQL statement に対して生成された複数の実行計画からなる plan history をディスク上に保持します。

Oracle Optimizer は SPM を認識しており、この情報にアクセスし、利用し、管理します。この情報は SQL Management Base（SMB）と呼ばれるリポジトリに格納されます。

plan history により、optimizer はコストベース方式で生成した最良コスト計画が、まったく新しい計画かどうかを判定できます。新しい計画は、性能劣化を引き起こす可能性のある計画変更を意味します。そのため、optimizer は新しい最良コスト計画を選びません。代わりに、accepted plan の集合から選択します。accepted plan とは、性能劣化を引き起こさないことが確認されている、または良好な性能を持つと判断された計画です。

accepted plan の集合は SQL plan baseline と呼ばれ、plan history の部分集合を表します。

まったく新しい計画は、non-accepted plan として plan history に追加されます。その後、SPM ユーティリティがその性能を検証し、性能劣化を引き起こす場合は non-accepted plan のまま保持し、性能向上をもたらす場合は accepted plan に変更します。この計画性能の検証プロセスによって、計画の安定性と適応性の両方が実現されます。

SQL plan baseline は、いくつかの方法で作成できます。SQL Tuning Set（STS）を使う方法、cursor cache から作成する方法、あるデータベースからエクスポートして別のデータベースへインポートする方法、そしてすべての文に対して自動的に作成する方法です。順に見ていきましょう。このブログの例では Oracle Database Sample Schemas を使用しているため、皆さん自身でも試すことができます。

STS から SQL plan baseline を作成する

アップグレード時には、すでに一部またはすべての SQL 文を含む STS を持っているかもしれません。この STS には、十分に満足できる性能を持つ計画が含まれている可能性があります。この STS を MY_STS と呼ぶことにします。この STS から SQL plan baseline を作成するには、次のようにします。

SQL> variable pls number;
SQL> exec :pls := dbms_spm.load_plans_from_sqlset(sqlset_name => 'MY_STS', -
>                 basic_filter => 'sql_text like ''select%p.prod_name%''');

これにより、指定したフィルタに一致するすべての文に対して SQL plan baseline が作成されます。

cursor cache から SQL plan baseline を作成する

現在キャッシュにある任意の cursor に対して、SQL plan baseline を自動作成することもできます。方法は次のとおりです。

SQL> exec :pls := dbms_spm.load_plans_from_cursor_cache( -
>                   attribute_name => 'SQL_TEXT', -
>                   attribute_value => 'select%p.prod_name%');

これにより、テキストが指定した文字列に一致するすべての文に対して SQL plan baseline が作成されます。この関数には複数のオーバーロード版があり、他の cursor 属性でフィルタすることも可能です。

staging table を使用して SQL plan baseline を作成する

すでに SQL plan baseline を持っている場合は、それを別のシステムへエクスポートできます。

まず、テスト・システム側で staging table を作成し、エクスポートしたい SQL plan baseline をその表へ格納します。

SQL> exec dbms_spm.create_stgtab_baseline(table_name => 'MY_STGTAB', -
>           table_owner => 'SH');

PL/SQL procedure successfully completed.
SQL> exec :pls := dbms_spm.pack_stgtab_baseline( -
>                   table_name => 'MY_STGTAB', -
>                   table_owner => 'SH', -
>                   sql_text => 'select%p.prod_name%');

これにより、指定したフィルタに一致する文に対するすべての SQL plan baseline がパックされます。staging table である MY_STGTAB は通常の表であり、Datapump Export を使って本番システムへエクスポートします。

本番システム側では、staging table をアンパックして SQL plan baseline を作成できます。

SQL> exec :pls := dbms_spm.unpack_stgtab_baseline( -
>                   table_name => 'MY_STGTAB', -
>                   table_owner => 'SH', -
>                   sql_text => 'select%p.prod_name%');

これにより、staging table がアンパックされ、SQL plan baseline が作成されます。なお、staging table をアンパックするときのフィルタは任意であり、パック時に使用したものと異なっていても構いません。つまり、複数の SQL plan baseline を 1 つの staging table にパックし、ターゲット・システムではその一部だけを選択的にアンパックすることができます。

SQL plan baseline を自動作成する

optimizer_capture_sql_plan_baselines パラメータを TRUE に設定すると、すべての repeatable statement に対して SQL plan baseline を自動的に作成できます（デフォルトは FALSE です）。任意の文に対して最初にキャプチャされた計画は自動的に accepted となり、SQL plan baseline の一部になります。そのため、このパラメータを有効にするのは、デフォルトの計画が十分に良好な性能を示していると確信できる場合に限ってください。

以前のデータベース・バージョンからアップグレードした場合には、この自動計画キャプチャ・モードを利用できます。optimizer_features_enable を以前のバージョンに設定し、ワークロードを実行してください。すべての repeatable statement の計画がキャプチャされ、その結果 SQL plan baseline が作成されます。ワークロード内のすべての文が実行される機会を十分に得たことを確認した後で、optimizer_features_enable をデフォルト値に戻すことができます。

この自動計画キャプチャは repeatable statement、つまり少なくとも 2 回実行される文に対してのみ行われる点に注意してください。1 回しか実行されない文は、accepted plan が次回以降の hard parse でのみ使用されるため、SQL plan baseline の恩恵を受けません。

次の例は、同じ文を 2 回実行したときに、計画が自動的にキャプチャされる様子を示しています。

SQL> alter session set optimizer_capture_sql_plan_baselines = true;

Session altered.
SQL> var pid number
SQL> exec :pid := 100;

PL/SQL procedure successfully completed.

SQL> select 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 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> alter session set optimizer_capture_sql_plan_baselines = false;

Session altered

この記事では、SQL plan baseline を作成する方法を見てきました。次回は、SPM-aware optimizer と、それが SQL plan baseline をどのように利用するかを説明します。

参考情報: