津島博士のパフォーマンス講座　第75回　SQL Plan Managementについて

津島博士のパフォーマンス講座　Indexページ ▶▶

皆さんこんにちは、疲れたときの気分転換や息抜きなどに読んでみてください。

今回は、実行計画の変化で苦労しているような方に、正しく理解して効果的に管理して欲しいと思い、SQL Plan Management（SPM：SQL計画管理）を取り上げることにしました。後半に、Oracle Database 19c（Oracle19c）からのSPMの拡張機能についても説明していますので、参考にしてください。

1. SQL Plan Managementの基礎

SPMは、実行計画の予測不可能な変化（新しいオプティマイザ統計、初期化パラメータの変化、アップグレードなど）によって、パフォーマンスが低下するのを防止する（遅い実行計画を使用しない）便利な機能ですが、大変そうに感じて使用するのをためらっている方も多いように思います。そのような方のために、SPMの基本的なこと（SPMのコンポーネント、計画の取得、選択、展開）を説明しながら、使用方法や問題点などを紹介していきます。

（１）SPMのコンポーネント
まずは、SPMがどのように管理されているかについて説明します。
SPMは、それぞれのSQLの情報を、SYSAUX表領域上のSQL Management Base（SMB）に作成して、実行計画の管理を行います。そのため、SMBの領域は、使用可能なSYSAUX領域の割合として、SPACE_BUDGET_PERCENTパラメータ（デフォルトは10%）で制限されています（このようなSPMパラメータは、DBMS_SPM.CONFIGUREプロシージャで変更できます）。そのSMBには、以下のものが格納されています。

• SQLステートメント・ログ
  初期計画の自動取得で、繰返し実行されたSQL文かを確認するために、実行されたSQL文のSQLシグネチャ（大/小文字および空白が正規化されているSQLテキストで計算されたハッシュ値）をログに追加します。

• SQL計画履歴
  SQL文に対して生成された一連の実行計画で、SQL計画ベースライン（SQL文に対して使用が許可された計画）と未承認の計画が含まれます。計画ベースラインには、選択のときに優先させる固定計画（FIXEDパラメータがYES）も指定できます。未使用の計画は、保存期間（PLAN_RETENTION_WEEKSパラメータ、デフォルトは53週）が過ぎると自動的に削除されるので、基本は手動で削除する必要がありません。

SQL文は、SQLシグネチャを使用して特定するので、大/小文字や空白数が異なっても同じSQLとして扱うことが可能になっています。また、ユーザーAPI（DBMS_SPMパッケージなど）で使用するために、検索キーのSQLハンドル（SQLシグネチャから導出された文字列）と複数の実行計画から特定する計画名（plan_name）があります。

（２）計画の取得（Plan Capture）
次に、計画ベースラインに実行計画を取得する方法として、自動取得と手動取得を使用しますが、それぞれの特徴について説明します（SPMは、計画ベースラインを設定すると、その後の処理がすべて自動的に行えるようになっているので、計画ベースラインをどのように設定するかが重要になります）。

(a)自動取得
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES初期化パラメータをTRUE（デフォルトはFALSE）にすることで、複数回実行されたSQLの最初の実行計画が自動的に計画ベースラインとして取得されます（初期計画の自動取得とも呼びます）。SPMを簡単に使用することができますが、複数回実行されたすべてのSQLが対象になり、多くなってしまうのが問題のため、第63回で説明したフィルター機能が追加されましたが、条件を決めるのが簡単ではなく、あまり改善されていません（これも使用するのを難しくしているように思います）。

(b)手動取得
対象のSQLを特定できるような場合に、DBMS_SPMパッケージを使用して実行計画のロードを行います（管理者がパフォーマンスを認識して行うものとして、すべて承認済みとして計画ベースラインに追加します）。これは、以下のソースから手動でロードできるので、いろいろな用途で使用することができます。

• SQLチーニング・セット（アップグレードによるフォーマンス低下の防止など）
• ストアド・アウトライン（プラン・スタビリティからの移行）
• カーソル・キャッシュとAWR（継続的なシステムやデータの変化によるパフォーマンス低下の防止など）
  カーソル・キャッシュは、ロード先のSQLハンドルを指定できるので、異なるSQL（ヒントを指定したSQL）を登録することで、第38回で説明した「SQLを変更できないときのSQLチューニング」にも使用できます。

• ステージング表からのアンパック（テスト環境で作成された実行計画の移行）

手動取得されたSQLでも、計画ベースラインにない実行計画は自動的に取得されるので、対象のSQL数が多くなければ、手動取得で使用するだけでも効果的です（「（３）計画の選択」で動作を説明しています）。これを知らない方が多いようなので、簡単な例を載せておきます（以下は、手動でカーソル・キャッシュから実行計画をロードした後に、異なる実行計画がAUTO-CAPTUREとして登録されています）。

（３）計画の選択（Plan Selection）
次に、使用する実行計画をどのように選択するかについて説明します。
オプティマイザは、SQL文のハード解析で最適なコストとして実行計画を生成しますが、OPTIMIZER_USE_SQL_PLAN_BASELINES初期化パラメータをTRUE（デフォルト）にすると計画ベースラインが使用可能になり、計画ベースラインを使用した最適な実行計画の選択を行います（最もコストの低い承認済みの計画を選択します）。このとき、計画ベースラインの状態や内容によって、以下のように実行計画が決定されます。

• 計画ベースラインが存在しない（取得されていない）
  通常のハード解析として、新たに生成した実行計画を最適なコストとして使用します。
• 新しい計画が計画ベースラインに含まれる（最適なコストとして生成した計画が存在する）
  その実行計画を使用します。
• 新しい計画が計画ベースラインに含まれない（一致するものがない）
  新しい計画を計画履歴（未承認計画）に追加して、計画ベースラインの中から、一緒に格納されているアウトライン（ヒントの集まり）で再現でき、最もコストが低い実行計画（固定計画があれば優先）が使用されます。そのため、索引が削除された場合などでも問題ないようになっています。
• そして、計画ベースラインのどの計画も再現できない（すべての計画が削除された索引を使用しているなど）
  新たに生成された実行計画を使用します。

つまり、新しい計画が計画ベースラインに含まれないと、再現するためのハード解析が少し増えてしまうのが欠点ですが、SQL実行時間の低下よりは問題にはならないかと思います。

（４）計画の展開（Plan Evolution）
最後に、実行計画を展開する方法（未承認計画の検証と承認）について説明します。
展開処理は、計画履歴の未承認計画を計画ベースライン（最もコストが低い計画）と検証して、承認された（計画ベースラインよりパフォーマンスが優れている）ものだけを計画ベースラインに入れる処理になります（このパフォーマンス条件は、ハード解析のときのコスト比較ではなく、SQLチューニング・アドバイザなどと同じ条件を使用します）。これによりパフォーマンスの低下を防止しています。Oracle Database 12cからは、第35回で説明したSPM展開アドバイザの自動タスクと手動タスクで行うことができるので、より簡単になっています。
SPM展開アドバイザの自動タスクは、未承認計画の検証をメンテナンス・ウィンドウで自動的に行います（デフォルトで動作します）が、計画ベースラインや未承認計画が存在しないと何もしないので、無効化しなくても問題ありません。そのため、処理させるには、自動取得または手動取得で計画ベースラインを設定して、計画ベースラインにない計画を計画履歴に設定される必要があります。これにより、定期的に展開アドバイザを実行して、効果的な実行計画かを判断するような使い方が可能になります（第35回で説明したように、自動的に承認するか、レポートを見て判断するかも指定できます）。
ただし、SPMは、遅い実行計画を使用しないようにする機能なので、存在した実行計画にすることまでしかできません。そのため、より最適な実行計画にしたければ、オプティマイザ統計をもっと正確にするか、ヒントで補正して実行計画を手動取得することになります。

2. Oracle Database 19cの拡張機能

ここでは、Oracle19cからのSPMの拡張機能について説明します。
以下の二つの機能が、Oracle19cからSPMに拡張され、SPM展開アドバイザがより使いやすくなっています。ただし、どちらもExadataだけで使用できる機能になります。

• 自動SQL計画管理（AUTOMATIC SQL PLAN MANAGEMENT）
• 高頻度自動SPM展開アドバイザ・タスク（High-Frequency Automatic SPM Evolve Advisor Task）

（１）自動SQL計画管理
これまでは、いくつかの制限（検証の対象は計画履歴の未承認計画だけ、問題のSQLを簡単に自動取得できないなど）により、必要なSQLを的確に改善することができませんでしたが、Oracle19cからは、SQL展開アドバイザが完全に自動化されました。以下のように、二つのパラメータ（ALTERNATE_PLAN_BASELINE、ALTERNATE_PLAN_SOURCE）をAUTOにすることで、パフォーマンスが低下したSQLを自動的に判断して、改善することができるようになります（このパラメータは、Oracle Database 12cR2からですが、AUTOの指定はありませんでした）。

BEGIN
   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER (task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
                                       parameter => 'ALTERNATE_PLAN_BASELINE',
                                       value => 'AUTO');
   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER (task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
                                       parameter => 'ALTERNATE_PLAN_SOURCE',
                                       value => 'AUTO');  /* The Default */
END;
/

• ALTERNATE_PLAN_BASELINE（対象のSQL計画ベースライン）
  SQL計画ベースラインがないSQLでも、パフォーマンスが低下した上位SQLを対象にします（デフォルトはEXISTINGで、既存の計画ベースラインを持つSQL文に対して代替計画をロードします）。
• ALTERNATE_PLAN_SOURCE（代替計画を検索するソース）
  計画履歴にない計画についても、すべての使用可能なソース（カーソル・キャッシュ、AWR、SQLチューニング・セット）から代替計画（未承認の計画）を検索して、計画履歴に追加します（デフォルトはAUTOです）。

この自動SPM展開アドバイザは、以下のような動作になります。Oracle19cで①～③が追加になり、初期計画の自動取得がFALSEでも（計画ベースラインや未承認計画が登録されていなくても）動作することができます。

1. ①.SQL文に計画ベースラインがないときに、AWRまたはASTS（Automatic SQL Tuning Set）からパフォーマンス低下の上位SQLをチェックする（AWRやASTSから上位SQLの実行計画を計画ベースラインに登録する）
2. ②.代替計画を検索する（ALTERNATE_PLAN_SOURCEパラメータで指定されたリポジトリから検索する）
3. ③.未承認の計画を計画履歴に追加する
4. ④.未承認の計画を検証する
5. ⑤.パフォーマンスが高くなる計画はSQL計画ベースラインに入れる（高くならない計画は未承認計画に残る）

関連するパラメータ値は、以下のように確認することができます。

SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE" FROM DBA_ADVISOR_PARAMETERS
  2   WHERE ( (TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK') AND
  3           (PARAMETER_NAME IN ('ACCEPT_PLANS','TIME_LIMIT') OR PARAMETER_NAME LIKE '%ALT%') );

PARAMETER_NAME            VALUE
------------------------- ------------------------------------------
TIME_LIMIT                3600
ALTERNATE_PLAN_LIMIT      UNLIMITED
ALTERNATE_PLAN_SOURCE     AUTO
ALTERNATE_PLAN_BASELINE   AUTO
ACCEPT_PLANS              TRUE

（２）高頻度自動SPM展開アドバイザ・タスク
Oracle19cから自動SPM展開アドバイザが、メンテナンス・ウィンドウ以外でも実行できるようになりました。 以下のように、Oracle19cからのAUTO_SPM_EVOLVE_TASKパラメータをONにすることで、メンテナンス・ウィンドウ以外でも自動的に実行できるようになります（デフォルトはOFFで、Oracle19cではAUTOはOFFと同じです）。

exec DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK', 'ON');

以下のSQLで、AUTO_SPM_EVOLVE_TASKの現在の設定を確認することができます。このパラメータを有効にすると、以下のように1時間ごとに30分以内で実行されるようになります。

SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG WHERE PARAMETER_NAME LIKE '%SPM%';

PARAMETER_NAME                   PARAMETER_VALUE
-------------------------------- --------------------------------
AUTO_SPM_EVOLVE_TASK             ON
AUTO_SPM_EVOLVE_TASK_INTERVAL    3600
AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800

タスクの名前は、SPM展開アドバイザの標準自動タスクと高頻度タスクのどちらもSYS_AUTO_SPM_EVOLVE_TASKになるので、実行名によって識別します（標準タスクの実行名がEXEC_<number>という形式に対し、高頻度の実行名はSYS_SPM_<timestamp>という形式になります）。以下のように、DBA_ADVISOR_EXECUTIONSビューでタスク実行のステータスを確認することができます（この出力では、EXEC_6が標準自動タスクの実行、それ以外は高頻度タスクの実行です）。

SQL> SELECT TASK_NAME, EXECUTION_NAME, STATUS FROM DBA_ADVISOR_EXECUTIONS
2   WHERE TASK_NAME LIKE '%SPM%' AND (EXECUTION_NAME LIKE 'SYS_SPM%' OR EXECUTION_NAME LIKE 'EXEC_%')
3   ORDER BY EXECUTION_END;

TASK_NAME                      EXECUTION_NAME                 STATUS
------------------------------ ------------------------------ ---------
SYS_AUTO_SPM_EVOLVE_TASK       SYS_SPM_2020-04-03/13:15:26    COMPLETED
SYS_AUTO_SPM_EVOLVE_TASK       SYS_SPM_2020-04-03/14:16:04    COMPLETED
SYS_AUTO_SPM_EVOLVE_TASK       EXEC_6                         COMPLETED
SYS_AUTO_SPM_EVOLVE_TASK       SYS_SPM_2020-04-03/15:16:32    COMPLETED

このように、いろいろ機能拡張されて使いやすくなっているので、実行計画を固定化運用している方、実行計画が問題になっている方などは使用を検討してみてください。

3. おわりに

今回は、SQL Plan Managementについて説明しましたが、少しは参考になりましたでしょうか。これからも頑張りますのでよろしくお願いします
それでは、次回まで、ごきげんよう。

ページトップへ戻る▲ 

津島博士のパフォーマンス講座　Indexページ ▶▶