SQL計画管理の概要

28 SQL計画管理の概要

SQL計画管理は、データベースで既知の計画または確認済の計画のみが使用されるようにオプティマイザで実行計画を自動的に管理する予防的なメカニズムです。

SQL計画管理では、SQL計画ベースラインと呼ばれるメカニズムを使用します。計画ベースラインは、SQL文に対してオプティマイザで使用できる一連の承認済の計画です。このコンテキストで、計画には、オプティマイザで実行計画を再現するために必要な計画に関連するすべての情報(たとえば、SQL計画識別子、一連のヒント、バインド値、オプティマイザ環境など)が含まれます。通常のユースケースの場合、データベースで計画の正常な実行が検証された後のみ、計画が計画ベースラインに承認されます。

SQL計画管理の主要コンポーネントは次のとおりです。

計画の取得

このコンポーネントは、一連のSQL文に対する計画に関連する情報を保存します。
計画の選択

このコンポーネントは、保存された計画履歴に基づいたオプティマイザによる計画変更の検出、およびパフォーマンスが低下する可能性を回避するための適切な計画の選択でのSQL計画ベースラインの使用です。
計画の展開

このコンポーネントは、手動または自動での、既存のSQL計画ベースラインへの新規計画の追加プロセスです。

この章のトピックは、次のとおりです:

28.1 SQL計画管理の目的

SQL計画管理では、計画の変更によって発生するパフォーマンスの低下を防止します。

2つ目の目的は、計画変更を検証し、パフォーマンスを改善させる計画変更のみを承認することにより、新しいオプティマイザ統計や索引などの変更にスムーズに適応することです。

ノート:

SQL計画ベースラインでは、索引の削除などによる元に戻せない実行計画の変更を防止することはできません。

この項では、次の項目について説明します。

28.1.1 SQL計画管理の利点

SQL計画の管理により、データベース・アップグレードおよびシステムとデータの変更でSQLパフォーマンスを向上または維持できます。

特に、次の利点が得られます。

新しいバージョンのオプティマイザをインストールするデータベースのアップグレードでは、通常、ほんのわずかなSQL文に対して計画の変更が発生します。

ほとんどの計画変更において、パフォーマンスの向上が見られるか、または変化がありません。ただし、一部の計画の変更は、パフォーマンスの低下の原因となることがあります。SQL計画ベースラインにより、アップグレードがもたらす低下の可能性を最小限に抑えられます。

アップグレード時には、データベースにより計画ベースラインからの計画のみが使用されます。データベースは現在のベースラインに含まれない新しい計画を保持領域に入れ、後でそれらを評価し、ベースラインに含まれる現在の計画よりも少ないリソースを使用するかどうかを判断します。それらの計画のパフォーマンスがより優れている場合は、それらの計画をベースラインに昇格させます。そうでない場合は昇格させません。
継続的なシステムおよびデータの変更は、一部のSQL文の計画に影響を及ぼし、パフォーマンスの低下を招く可能性があります。

SQL計画ベースラインにより、パフォーマンスの低下を最小限に抑えて安定したSQLパフォーマンスを維持できます。
新しいアプリケーション・モジュールのデプロイメントは、新しいSQL文をデータベースに導入します。

アプリケーション・ソフトウェアは、新しい文に対して標準のテスト構成で作成された適切なSQL実行計画を使用できます。システム構成がテスト構成と大幅に異なる場合、データベースはSQL計画ベースラインを時間の経過とともに進化させてパフォーマンスを改善します。

関連項目:

Oracleデータベースのアップグレード方法を学習するには、『Oracle Databaseアップグレード・ガイド』を参照してください。

28.1.2 SQL計画ベースラインとSQLプロファイルの違い

SQLプロファイルとSQL計画ベースラインのどちらも、オプティマイザで最適な計画のみが使用されるようにすることによりSQL文のパフォーマンスを向上させます。

プロファイルとベースラインはどちらも、ヒントを使用して内部的に実装されます。ただし、これらのメカニズムには次を含めて大きな違いがあります。

一般的に、SQL計画ベースラインは事前対応型であるのに対し、SQLプロファイルは事後対応型です。

通常、SQL計画ベースラインは重大なパフォーマンスの問題が発生する前に作成します。SQL計画ベースラインは、オプティマイザが最適ではない計画を将来的に使用することを防ぎます。

SQLプロファイルは、SQLチューニング・アドバイザを起動するとデータベースにより作成されます。通常、チューニング・アドバイザは、SQL文により高負荷の兆候が示された後にのみ起動します。SQLプロファイルは主に、最適ではない計画につながったオプティマイザのミスを継続的に解決するために使用できます。SQLプロファイル・メカニズムは事後対応であるため、データベースに重大な変更が生じた場合は安定したパフォーマンスを保証できません。

図28-1 SQL計画ベースラインとSQLプロファイル

「図28-1 SQL計画ベースラインとSQLプロファイル」の説明
SQL計画ベースラインは特定の計画を再現するのに対し、SQLプロファイルはオプティマイザのコスト見積りを修正します。

SQL計画ベースラインは一連の承認済計画から構成されます。各計画は、特定の計画を完全に指定するアウトライン・ヒントのセットを使用して実装されます。SQLプロファイルもヒントを使用して実装されますが、これらのヒントでは特定の計画は指定されません。これらのヒントは、最適ではない計画につながったオプティマイザの見積りの計算ミスを修正します。たとえば、ヒントにより表のカーディナリティ予測が修正されます。

プロファイルによりオプティマイザは特定の1つの計画に制限されないため、SQLプロファイルはSQL計画ベースラインよりも柔軟です。たとえば、初期化パラメータやオプティマイザ統計の変更により、オプティマイザはより最適な計画を選択できます。

SQLチューニング・アドバイザを使用することをお薦めします。これにより、SQLプロファイルと計画ベースラインのどちらが各SQL文に最適なメカニズムであるのかを自分で判断せずに、アドバイザの推奨事項に従うことができます。

関連項目:

28.2 計画の取得

SQL計画の取得とは、一連のSQL文に対して、計画に関連する情報を取得し、SQL管理ベースに保存するための方法を指します。

計画の取得とは、SQL計画管理にこの計画を認識させることを意味します。初期化パラメータを設定することにより初期計画の取得が自動的に発生するように構成するか、DBMS_SPMパッケージを使用して計画を手動で取得できます。

この項では、次の項目について説明します。

28.2.1 初期計画の自動取得

有効時には、データベースは実行されたSQL文の自動取得が可能であるかどうかを確認します。

初期計画の自動取得は、OPTIMIZER_CAPTURE_SQL_PLAN_BASELINESをtrueに設定にすることで有効化できます(デフォルトはfalse)。初期化パラメータのOPTIMIZER_USE_SQL_PLAN_BASELINESは無関係であることに注意してください。たとえば、OPTIMIZER_CAPTURE_SQL_PLAN_BASELINESがtrueの場合、OPTIMIZER_USE_SQL_PLAN_BASELINESの設定がtrueとfalseのいずれでも、初期計画ベースラインが作成されます。

この項では、次の項目について説明します。

関連項目:

"計画の選択"
OPTIMIZER_USE_SQL_PLAN_BASELINES初期化パラメータについて学習するには、『Oracle Databaseリファレンス』を参照してください

28.2.1.1 初期計画の自動取得の対象

計画の自動取得の対象とするには、文が繰返し可能である必要があり、構成された取得フィルタを通過する必要があります。

デフォルトでは、データベースは、すべての繰返し可能なSQL文を取得の対象とみなします。ただし、次の例外があります。

AS SELECT句が指定されていない場合のCREATE TABLE
DROP TABLE
INSERT INTO ... VALUES

対象の最初のチェックは繰返しです。文が1回のみ実行される場合、データベースはSQL計画ベースラインの対象として考慮されません。ただし、文が2回以上実行される場合、定義上繰返し可能であるため、データベースは詳細チェックの対象として考慮します。

繰返し可能な文に対して、DBMS_SPM.CONFIGUREプロシージャにより自動取得フィルタを作成できます。これにより、必要な文のみを取得して重要でない文を除外できるため、SYSAUX表領域の領域が削減されます。多くの場合、重要でない問合せには、次の特性があります。

重要でないと頻繁に実行されません
リソース集中型ではありません
SQL計画管理から利点を得るのは、それほど複雑ではありません

指定されたパラメータに対して、フィルタでは、指定した値の文の計画が含まれるか(enable=>TRUE)、除外されます(enable=>FALSE)。取得の対象とするには、繰返し可能な文をフィルタで除外しないでください。DBMS_SPM.CONFIGUREプロシージャは、SQLテキストのフィルタをサポートし、スキーマ名、モジュールおよびアクションを解析します。

パラメータのnull値により、フィルタが削除されます。allow=FALSEと組み合せてvalue=>'%'を使用することで、パラメータのすべての値をフィルタ処理で除外し、個別のフィルタを作成して指定された値のみを含めることができます。DBA_SQL_MANAGEMENT_CONFIGビューには、現在のフィルタが示されます。

関連項目:

"計画の自動取得のフィルタの構成"
DBMS_SPM.CONFIGUREプロシージャについてさらに学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
DBA_SQL_MANAGEMENT_CONFIGビューについてさらに学習するには、『Oracle Databaseリファレンス』を参照してください

28.2.1.2 初期計画の自動取得の計画照合

データベースが繰返し可能なSQL文を実行し、この文がDBMS_SPM.CONFIGUREフィルタを通過する場合、データベースはSQL計画ベースラインの計画との照合を試行します。

初期計画の自動取得の場合、計画照合アルゴリズムは、次のとおりです。

SQL計画ベースラインが存在しない場合、オプティマイザによって文に対する計画履歴およびSQL計画ベースラインが作成され、その文に対する最初の計画が承認済としてマークされ、SQL計画ベースラインに追加されます。
SQL計画ベースラインが存在する場合、オプティマイザの動作は、解析時に導出されたコストベースの計画によって決まります。
- この計画がSQL計画ベースラインと一致しない場合、オプティマイザは新しい計画を未承認とマークし、SQL計画ベースラインに追加します。
- この計画がSQL計画ベースラインと一致する場合、SQL計画ベースラインには何も追加されません。

28.2.2 計画の手動取得

SQL計画管理では、計画の手動取得とは、ユーザーによるSQL計画ベースラインへの既存計画の一括ロードを指します。

SQL文に対する実行計画をAWR、SQLチューニング・セット(STS)、共有SQL領域、ステージング表またはストアド・アウトラインからロードするには、Cloud ControlまたはPL/SQLを使用します。

図28-2 SQL計画ベースラインへの計画のロード

「図28-2 SQL計画ベースラインへの計画のロード」の説明

ロード動作は、一括ロードに含まれる各文に対してSQL計画ベースラインが存在するかどうかによって異なります。

文に対するベースラインが存在しない場合、データベースは次の処理を行います。
1. 文に対する計画履歴と計画ベースラインを作成します。
2. 文に対する最初の計画を承認済とマークします。
3. 計画を新しいベースラインに追加します。
文に対するベースラインが存在する場合、データベースは次の処理を行います。
1. ロードした計画を承認済とマークします。
2. 計画のパフォーマンスを検証せずに、計画を文に対する計画ベースラインに追加します。

オプティマイザは管理者により手動でロードされた計画のパフォーマンスは許容範囲にあると見なすため、手動でロードされた計画は常に承認済とマークされます。DBMS_SPM.LOAD_PLANS_FROM_%ファンクションでenabledパラメータをNOに設定することで、計画を有効化せずにロードできます。

関連項目:

DBMS_SPM.LOAD_PLANS_FROM_%ファンクションについてさらに学習するには、Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンスを参照してください

28.3 計画の選択

SQL 計画の選択とは、保存された計画履歴に基づいて計画変更を検出するオプティマイザの機能、およびパフォーマンスが低下する可能性を回避するために適切な計画を選択するSQL計画ベースラインの使用を指します。

データベースがSQL文のハード解析を実行すると、オプティマイザは最適なコスト計画を生成します。デフォルトでは、オプティマイザは次に、文に対するSQL計画ベースライン内で一致する計画を探します。計画ベースラインが存在しない場合は、データベースは最適なコスト計画を使用して文を実行します。

計画ベースラインが存在する場合は、オプティマイザの動作は、新たに生成された計画が計画ベースラインに含まれるかどうかによって決まります。

新しい計画がベースラインに含まれる場合、データベースは検出した計画を使用して文を実行します。
新しい計画がベースラインに含まれない場合、オプティマイザは新たに生成された計画を未承認とマークし、計画履歴に追加します。オプティマイザの動作は、計画ベースラインの内容によって決まります。
- 計画ベースラインに固定計画が含まれる場合、オプティマイザはコストが最も低い固定計画を使用します。
- 計画ベースラインに固定計画が含まれない場合、オプティマイザはコストが最も低いベースライン計画を使用します。
- 計画ベースライン内に再現可能な計画が含まれない場合(これはベースライン内のすべての計画が削除された索引を参照している場合に発生します)、オプティマイザは新たに生成されたコストベースの計画を使用します。

図28-3 SQL計画選択のディシジョン・ツリー

「図28-3 SQL計画選択のディシジョン・ツリー」の説明

関連項目:

固定計画

28.4 計画の展開

一般的に、SQL計画の展開は、オプティマイザが新しい計画を検証して既存のSQL計画ベースラインに追加するためのプロセスです。

具体的には、計画の展開は次の個別のステップで構成されます。

承認されていない計画がSQL計画ベースラインに含まれる承認済の計画と少なくとも同じパフォーマンスであることを検証します(計画の検証と呼ばれます)。
データベースにより承認済の計画と同じパフォーマンスであることが証明された場合、承認されていない計画を承認済の計画として計画ベースラインに追加します。

計画の展開の標準的なケースでは、オプティマイザは前述のステップを順番に実行します。したがって、オプティマイザがSQL計画ベースラインと比較して計画のパフォーマンスを検証するまで、SQL計画管理で新しい計画は使用できません。ただし、一方のステップを実行せずにもう一方のステップを実行するようにSQL計画管理を構成できます。次の図は、計画の展開で使用可能なパスを示しています。

図28-4 計画の展開

「図28-4 計画の展開」の説明

この項では、次の項目について説明します。

28.4.1 計画の展開の目的

通常、文のSQL計画ベースラインは、1つの承認済の計画から開始します。

ただし、様々な条件の異なる計画とともに実行されると、SQL文の実行が良くなる場合があります。たとえば、値が異なる選択性になるバインド変数を使用したSQL文は、いくつかの最適な計画を持つ場合があります。マテリアライズド・ビューまたは索引の作成あるいは表の再パーティション化により、現在の計画が他の計画よりコストが高くなる場合があります。

新しい計画がSQL計画ベースラインに一度も追加されなかった場合、一部のSQL文のパフォーマンスが低下する可能性があります。したがって、新しい承認済の計画をSQL計画ベースラインに展開する必要がある場合があります。計画の展開は、SQL計画ベースラインに含める前に新しい計画のパフォーマンスを検証して、パフォーマンスの低下を防止します。

28.4.2 計画の展開用のPL/SQLサブプログラム

DBMS_SPMパッケージは、計画の展開で使用できるプロシージャとファンクションを提供します。

これらのサブプログラムはタスク・インフラストラクチャを使用します。たとえば、CREATE_EVOLVE_TASKは展開タスクを作成し、EXECUTE_EVOLVE_TASKはそれを実行します。すべてのタスク展開サブプログラムの名前には、文字列EVOLVE_TASKが含まれます。

展開プロシージャは必要に応じて使用するか、自動的に実行されるようにサブプログラムを構成します。自動メンテナンス・タスクSYS_AUTO_SPM_EVOLVE_TASKは、スケジュールされているメンテナンス・ウィンドウで毎日実行されます。このタスクにより次の処理が自動的に実行されます。

承認されていない計画を選択し、検証用にランク付けします。
パフォーマンスしきい値を満たしていれば、各計画を承認します。

関連項目:

「SPM展開アドバイザのタスクの管理」
「SQL計画ベースラインの手動での展開」
DBMS_SPMパッケージについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。

28.5 SQL計画管理の記憶域アーキテクチャ

SQL計画管理インフラストラクチャでは、解析された文の署名と、承認済と未承認の両方の計画を記録します。

この項では、次の項目について説明します。

28.5.1 SQL管理ベース

SQL管理ベース(SMB)はデータ・ディクショナリ内の論理リポジトリです。

SMBの内容は次のとおりです。

SQL文のログ(SQL IDのみを含む)
SQL計画履歴(SQL計画ベースラインを含む)
SQLプロファイル
SQLパッチ

SMBには、SQLパフォーマンスの維持または向上のためにオプティマイザで使用できる情報が保存されます。

SMBはSYSAUX表領域内にあり、自動セグメント領域管理を使用します。SMB全体がSYSAUX表領域内に存在するため、この表領域が使用可能でない場合、データベースはSQL計画管理およびSQLチューニング機能を使用しません。

図28-5 SMBのアーキテクチャ

「図28-5 SMBのアーキテクチャ」の説明

ノート:

SMBをプラガブル・データベースとともに使用すると、データの可視性と権限の要件が一致しない場合があります。コンテナ・データベース(CDB)内での管理機能の動作をまとめた表が記載されている『Oracle Database管理者ガイド』を参照してください。

関連項目:

SYSAUX表領域について学習するには、Oracle Database管理者ガイドを参照してください

28.5.2 SQL文のログ

SQL計画の自動取得が有効になっている場合、SQL文のログには、オプティマイザがこれまでに評価してきた文の署名が含まれます。

SQLシグネチャは、大/小文字および空白が正規化されているSQL文のテキストを使用して計算されたハッシュ値です。オプティマイザは文を解析する際、シグネチャを作成します。

自動取得中、データベースはこの署名をSQL文のログ(SQLLOG$)と照合してその署名が以前に確認されたことがあるかどうかを判断します。確認されたことがない場合、データベースはその署名をログに追加します。署名がすでにログにある場合は、データベースによりその文が繰返し可能なSQL文であることが確認されています。

ノート:

フィルタで文が除外される場合、その署名もログから除外されます。

例28-1 SQL文のロギング

この例は、データベースがどのように文のログ内で文を追跡し、繰返し可能な文に対して自動的にベースラインを作成するかを示しています。文のログの最初の問合せでは、追跡されたSQL文は1つもないことが示されます。AD_PRESに対するhr.jobsの問合せ後、ログには文が1つ追跡されていることが示されます。

SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;

System altered.

SQL> SELECT * FROM SQLLOG$;
 
no rows selected
 
SQL> SELECT job_title FROM hr.jobs WHERE job_id = 'AD_PRES';
 
JOB_TITLE
-----------------------------------
President
 
SQL> SELECT * FROM SQLLOG$;
 
 SIGNATURE     BATCH#
---------- ----------
1.8096E+19          1

次に、セッションは別のjobs問合せを実行します。ログには2つの文が追跡されていることが示されます。

SQL> SELECT job_title FROM hr.jobs WHERE job_id='PR_REP';
 
JOB_TITLE
-----------------------------------
Public Relations Representative
 
SQL> SELECT * FROM SQLLOG$;
 
 SIGNATURE     BATCH#
---------- ----------
1.7971E+19          1
1.8096E+19          1

DBA_SQL_PLAN_BASELINESの問合せでは、どちらの文に対してもベースラインが存在しないことが示されます。これは、どちらの文も繰返し可能ではないためです。

SQL> SELECT SQL_HANDLE, SQL_TEXT 
  2  FROM DBA_SQL_PLAN_BASELINES 
  3  WHERE SQL_TEXT LIKE 'SELECT job_title%';
 
no rows selected

セッションがjob_id='PR_REP'に対する問合せを再度実行します。これでこの文は繰返し可能になり、またSQL計画の自動取得が有効になっているため、データベースによりこの文に対する計画ベースラインが作成されます。job_id='AD_PRES'に対する問合せは一度しか実行されていないため、これに対する計画ベースラインは存在しません。

SQL> SELECT job_title FROM hr.jobs WHERE job_id='PR_REP';
 
JOB_TITLE
-----------------------------------
Public Relations Representative
 
SQL> SELECT SQL_HANDLE, SQL_TEXT 
  2  FROM DBA_SQL_PLAN_BASELINES 
  3  WHERE SQL_TEXT LIKE 'SELECT job_title%';
 
SQL_HANDLE           SQL_TEXT
-------------------- --------------------
SQL_f9676a330f972dd5 SELECT job_title FRO
                     M hr.jobs WHERE job_
                     id='PR_REP'

関連項目:

「初期計画の自動取得」
DBA_SQL_PLAN_BASELINESについて学習するには、『Oracle Databaseリファレンス』を参照してください。

28.5.3 SQL計画履歴

SQL計画履歴は、取得されたSQL実行計画のセットです。この履歴には、SQL計画ベースラインおよび未承認の計画が含まれます。

SQL計画管理では、データベースが既存のSQL計画ベースラインに対する新しいSQL実行計画を検出し、新しい計画を展開(検証)できるように履歴に記録します。展開は、データベースにより自動的に、またはDBAにより手動で開始されます。

Oracle Database 12cから、SQL計画履歴のすべてのSQL文に対する実行計画がSMBに保存されるようになりました。DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINEファンクションは、SMBから計画をフェッチして表示します。Oracle Database 12cより前に作成された計画については、SMBに保存されていないため、ファンクションはSQL文をコンパイルして計画を生成する必要があります。

この項では、次の項目について説明します。

関連項目:

「SQL計画ベースラインでの計画の表示」
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINEファンクションについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。

28.5.3.1 有効化された計画

有効化された計画は、オプティマイザの使用対象である計画です。

enabledパラメータをYES (デフォルト)に設定して計画をロードした場合、データベースは結果のSQL計画ベースラインを、承認されていない場合でも、自動的に有効とマークします。有効な計画を手動で無効な計画に変更できます。この場合、計画が承認済でもオプティマイザはその計画を使用できなくなります。

28.5.3.2 承認済の計画

承認済の計画はSQL文のSQL計画ベースラインに存在する計画であり、オプティマイザで使用できます。承認済の計画には、ヒント・セット、計画のハッシュ値、計画に関連するその他の情報が含まれます。

SQL文に対する計画履歴には、すべての計画(承認済と未承認の両方の計画)が含まれます。オプティマイザが計画ベースライン内に最初の承認済の計画を生成すると、それ以降のすべての承認されていない計画は計画履歴に追加され、検証を待ちますが、SQL計画ベースラインには追加されません。

28.5.3.3 固定計画

固定計画は、優先としてマークされた承認済の計画です。これにより、オプティマイザはベースライン内の固定計画のみを考慮するようになります。固定計画は、オプティマイザの計画選択プロセスに影響します。

ある文に対するSQL計画ベースラインに3つの計画が存在するとします。オプティマイザに、その内の2つの計画のみを優先させたいとします。次の図に示すように、この2つの計画を固定とマークすることにより、オプティマイザがこれらの計画からの最適な計画のみを使用し、残りの計画は無視するようにします。

図28-6 固定計画

「図28-6 固定計画」の説明

1つ以上の有効な固定計画を含むベースラインに新しい計画を追加した場合、それらの計画を手動で固定と宣言しないかぎり、オプティマイザはそれらの計画を使用することはできません。