30 ストアド・アウトラインのSQL計画ベースラインへの移行
ストアド・アウトラインの移行は、保存されたアウトラインをSQL計画ベースラインに変換するためにユーザーが開始するプロセスです。SQL計画ベースラインは、最適なパフォーマンスを提供することが立証された計画のセットです。
ノート:
Oracle Database 12c以降では、ストアド・アウトラインは非推奨となっています。代替方法は、「ストアド・アウトラインのSQL計画ベースラインへの移行」を参照してください。
この章では、ストアド・アウトラインの移行の概要およびタスクについて説明します。この章のトピックは、次のとおりです:
30.1 ストアド・アウトラインの移行について
ストアド・アウトラインは、SQL文のヒントのセットです。
ヒントは、文に対する特定の計画を選択するようオプティマイザに指示します。ストアド・アウトラインは、計画の安定性を提供するためのレガシー・テクニックです。
この項では、次の項目について説明します。
30.1.1 ストアド・アウトラインの移行の目的
計画の安定性を維持し、パフォーマンスの低下を防止するためにストアド・アウトラインに依存している場合は、ストアド・アウトラインからSQL計画ベースラインへ安全に移行できます。移行後は、同じ計画の安定性を維持し、さらにSQL計画管理フレームワークで提供された機能を有効に利用できます。
ストアド・アウトラインには、次のデメリットがあります。
-
ストアド・アウトラインは、時間の経過とともに自動的に進化しません。したがって、ストアド・アウトラインは、作成した時点では最適であっても、データベースの変更後に最適ではない計画となってパフォーマンスの低下をもたらす場合があります。
-
ストアド・アウトラインのヒントは、削除された索引に対する索引ヒントと同様に無効になる場合があります。この場合、データベースでは、無効なヒントを除外してアウトラインを使用し続け、多くの場合、元の計画またはオプティマイザによって生成される現在の最適なコスト計画より劣悪な計画が生じます。
-
SQL文では、現在指定されているカテゴリでストアド・アウトラインに定義された計画のみを選択できます。パフォーマンスが向上する場合でも、異なるカテゴリのストアド・アウトラインからの選択、または現在のコストベースの計画は選択できません。
-
ストアド・アウトラインは、事後対応型のチューニング・テクニックです。したがって、パフォーマンス問題が発生した後にのみストアド・アウトラインを使用して問題に対処します。たとえば、高負荷になったSQL文の計画を修正するストアド・アウトラインを実装できます。この場合、高負荷になる前に予防的に文をチューニングせずに、ストアド・アウトラインを使用します。
ストアド・アウトライン移行PL/SQL APIは、次のように前述の問題を解決するのに役立ちます。
-
SQL計画ベースラインにより、オプティマイザが同じ最適な計画を使用し、時間の経過とともに計画を進化させることができます。
指定したSQL文の新しい計画は、パフォーマンスの低下が生じないことを検証した後にSQL計画ベースラインとして追加できます。
-
SQL計画ベースラインは、無効なヒントのために計画が再生できなくなることを防止します。
計画ベースラインに格納されたヒントが無効になった場合、その計画は再生可能な計画ではありません。この場合は、代替の再生可能な計画ベースラインまたはオプティマイザによって生成された現在の最適なコスト計画が選択されます。
-
特定のSQL文に対する複数の計画ベースラインを管理できます。
オプティマイザは、特定のSQL文の計画を最適な計画のセットから選択できるため、ストアド・アウトラインでのカテゴリごとに1つの計画といった要件に制限されることはありません。
30.1.2 ストアド・アウトラインの移行方法
ストアド・アウトラインの移行は、ユーザーが開始して、複数のステージを通過するプロセスです。
この項では、ストアド・アウトラインをSQL計画ベースラインに移行する方法について説明します。この情報は、ストアド・アウトラインの移行タスクの実行に関する重要な情報です。
この項では、次の項目について説明します。
30.1.2.1 ストアド・アウトラインの移行ステージ
ストアド・アウトラインを移行するには、ストアド・アウトラインを指定します。データベースは次にSQL計画ベースラインを作成および更新します。
移行プロセスには、次のステージがあります。
-
ユーザーが、移行するアウトラインを指定するファンクションを起動します。
-
データベースでアウトラインが次のように処理されます。
-
データベースが計画ベースラインに必要なアウトラインの情報をコピーします。
データベースでは、情報を直接コピーするか、アウトラインの情報に基づいて計算します。たとえば、SQL文のテキストは両方のスキーマに存在するので、テキストをアウトラインからベースラインにコピーできます。
-
データベースでは、ヒントを再解析してアウトラインにない情報を取得します。
計画のハッシュ値および計画のコストは、アウトラインの既存情報から得られないため、ヒントの再解析が必要です。
-
データベースがベースラインを作成します。
-
-
データベースでは、初めてSQL計画ベースラインを選択してSQL文を実行する際に、不足している情報を取得します。
コンパイル環境および実行統計は、実行する際に、計画ベースラインを解析およびコンパイルするときにのみ取得できます。
これらのフェーズが完了した後、移行が完了します。
30.1.2.2 アウトラインのカテゴリとベースライン・モジュール
アウトラインはヒントのセットであり、SQL計画ベースラインは計画のセットです。
これらは異なる技術であるため、アウトラインの一部の機能は、ベースラインの機能に正確にマップされません。たとえば、1つのSQL文に、カテゴリの異なる複数のアウトラインが存在する可能性がありますが、現在ベースラインに存在するカテゴリはDEFAULT
のみです。
アウトラインのカテゴリは、SQL計画ベースラインのモジュールに相当します。表30-1 に、アウトラインのカテゴリがどのようにモジュールにマップするかを説明します。
表30-1 アウトラインのカテゴリ
概念 | 説明 | デフォルト値 |
---|---|---|
アウトライン・カテゴリ |
ユーザーにより定義されたストアド・アウトライン・セットの分類。 カテゴリを使用して、ストアド・アウトラインの異なるSQL文を管理できます。たとえば、1つの文が、 各SQL文には、1つ以上のストアド・アウトラインがあります。各ストアド・アウトラインは、1つのカテゴリにのみ分類されます。1つの文にはカテゴリの異なる複数のストアド・アウトラインがありますが、各文には1つのカテゴリにつき1つのストアド・アウトラインのみ存在します。 移行の際に、各アウトライン・カテゴリは、SQL計画ベースライン・モジュールにマップされます。 |
|
ベースライン・モジュール |
実行されている高度な機能。 SQL計画ベースラインは、1つのモジュールにのみ所属できます。 |
アウトラインのSQL計画ベースラインへの移行後、デフォルトのモジュール名はアウトライン・カテゴリ名です。 |
ベースライン・カテゴリ |
SQL計画ベースラインには、カテゴリが1つのみ存在します。それは 1つの文は、 |
|
Oracle Databaseでは、ストアド・アウトラインをSQL計画ベースラインに移行する際に、すべてのアウトライン・カテゴリを同名のSQL計画ベースライン・モジュールにマップします。次の図に示すように、アウトラインのOLTP
カテゴリは、ベースラインのOLTP
モジュールにマップされます。移行後、DEFAULT
は、すべてのSQL計画ベースラインを含むスーパー・カテゴリです。
30.1.3 ストアド・アウトラインの移行のためのユーザー・インタフェース
DBMS_SPM
パッケージを使用してストアド・アウトラインを移行できます。
表30-2 ストアド・アウトラインの移行に関連するDBMS_SPMファンクション
DBMS_SPMファンクション | 説明 |
---|---|
|
既存のストアド・アウトラインを計画ベースラインに移行します。 次のいずれかの形式を使用します。
|
|
SQL文に関連付けられた1つの計画またはすべての計画の属性を変更します。 |
|
SQL計画ベースラインに移行されたストアド・アウトラインを削除します。
|
ストアド・アウトラインおよび計画ベースラインの動作は、初期化パラメータおよびセッション・パラメータで制御できます。表30-3に、関連パラメータを示します。これらのパラメータ設定の相互作用の説明は、表30-5 および表30-6 を参照してください。
表30-3 ストアド・アウトラインの移行に関連するパラメータ
初期化またはセッション・パラメータ | 説明 | パラメータ・タイプ |
---|---|---|
|
セッション中に発行された各問合せのアウトラインを自動的に作成して保存するかどうかを決定します。 |
初期化パラメータ |
|
繰返し可能なSQL文の自動認識およびそれらの文のSQL計画ベースラインの生成を有効または無効にします。 |
初期化パラメータ |
|
SQL管理ベースに格納されたSQL計画ベースラインの使用を有効または無効にします。 |
初期化パラメータ |
|
オプティマイザが実行計画の生成にストアド・アウトラインを使用するかどうかを決定します。 ノート: これは初期化パラメータではなく、セッション・パラメータです。 |
セッション |
データベース・ビューを使用して、ストアド・アウトラインの移行に関連する情報にアクセスできます。表30-4に、主なビューを示します。
表30-4 ストアド・アウトラインの移行に関連するビュー
ビュー | 説明 |
---|---|
|
データベースのすべてのストアド・アウトラインについて説明します。
|
|
特定のSQL文に関して現在作成されているSQL計画ベースラインの情報を表示します。
|
関連項目:
-
DBMS_SPM
パッケージについて学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください -
CREATE_STORED_OUTLINES
初期化パラメータについて学習するには、Oracle Databaseリファレンスを参照してください
30.1.4 ストアド・アウトラインの移行の基本ステップ
基本的なプロセスは、準備、移行およびクリーン・アップです。
基本ステップは次のとおりです。
-
ストアド・アウトラインの移行の準備をします。
移行の前提条件を確認し、移行後の計画ベースラインの動作を決定します。
「ストアド・アウトラインの移行の準備」を参照してください。
-
次のいずれかのタスクを定義します。
-
ベースラインに移行して、SQL計画管理の機能を使用します。
「アウトラインの移行によるSQL計画管理機能の使用」を参照してください。
-
ストアド・アウトラインの動作を正確に維持して、ベースラインに移行します。
「アウトラインの移行でのストアド・アウトライン動作の保存」を参照してください。
-
-
移行後の確認およびクリーンアップを実行します。
「ストアド・アウトラインの移行後のフォーローアップ・タスクの実行」を参照してください。
30.2 ストアド・アウトラインの移行の準備
目標は、どのストアド・アウトラインが移行に適格かを判断することです。
ストアド・アウトラインの移行の準備を行うには:
-
SYSDBA
権限またはDBMS_SPM
パッケージのEXECUTE
権限でSQL*Plusをデータベースに接続します。 -
データベースのストアド・アウトラインの問合せを実行します。
次の例では、SQL計画ベースラインに移行されていないすべてのストアド・アウトラインを問い合せます。
SELECT NAME, CATEGORY, SQL_TEXT FROM DBA_OUTLINES WHERE MIGRATED = 'NOT-MIGRATED';
-
どのストアド・アウトラインが次の前提条件を満たして移行の対象となるかを判断します。
-
文は、ランタイムの
INSERT AS SELECT
文ではない。 -
文は、リモート・オブジェクトを参照していない。
-
この文は、プライベート・ストアド・アウトラインではない。
-
-
すべてのアウトラインを移行するか、指定したストアド・アウトラインか、指定したアウトライン・カテゴリに属するアウトラインかを決定します。
一部のアウトラインを移行する場合は、移行するアウトラインまたはカテゴリを指定します。
-
SQL計画ベースラインに移行するストアド・アウトラインで固定計画を使用するか、非固定計画を使用するかを決定します。
-
固定計画
固定計画は、固定されています。固定計画が、計画ベースラインに格納されたヒントを使用して再生可能な計画である場合、オプティマイザは、固定でない計画ベースラインよりも最小コストの固定計画ベースラインを常に選択します。本質的に、固定計画ベースラインは、有効なヒントを含むストアド・アウトラインとして機能します。
計画ベースラインに格納されたヒントに基づいて文を解析し、計画ベースラインと同じ計画ハッシュ値を使用して計画を作成できる場合、固定計画は再生可能です。1つ以上のヒントが無効になった場合、同じ計画ハッシュ値を使用して計画を作成することはできません。この場合、計画は非再生可能です。
ヒントを使用して解析したとき、固定計画を再生できなかった場合、オプティマイザは別の計画を選択します。次のいずれかを選択できます。
-
SQL計画ベースラインの別の計画
-
オプティマイザによって作成された現在のコストベースの計画
場合によっては、計画が異なるためにパフォーマンスが低下し、SQLチューニングが必要になります。
-
-
非固定計画
計画ベースラインに固定計画が含まれていない場合、SQL計画管理は、SQL文の計画を選択する際に、計画を同等に検討します。
-
-
実際の移行を開始する前に、Oracleデータベースが次の前提条件を満たしていることを確認します。
-
データベースはEnterprise Editionである。
-
データベースは、オープンし、一時停止状態ではない必要がある。
-
データベースは、制限付きアクセス(DBA)、読取り専用または移行モードであってはならない。
-
Oracle Call Interface (OCI)が使用可能である。
-
関連項目:
-
管理者権限について学習するには、Oracle Database管理者ガイドを参照してください
-
DBA_OUTLINES
ビューについて学習するには、Oracle Databaseリファレンスを参照してください
30.3 アウトラインの移行によるSQL計画管理機能の使用
トアド・アウトラインをSQL計画ベースラインに移行できます。
このタスクの目的は、次のとおりです。
-
移行後、SQL文に対して同じ固定計画を適用するのではなく、SQL計画管理で計画ベースラインのすべての計画から選択できるようにする。
-
ベースラインに新しい計画を追加して、データベースの変更があった場合にSQL計画ベースラインが進化できるようにする。
前提条件
このチュートリアルでは、次のことが前提となっています。
-
すべてのアウトラインを移行する。
特定のアウトラインを移行する場合、
DBMS_SPM.MIGRATE_STORED_OUTLINE
ファンクションを使用します。 -
ベースラインのモジュール名と移行するアウトラインのカテゴリ名を同じ名前にする。
-
SQL計画を固定しない。
デフォルトでは、生成される計画は固定ではなく、SQL計画管理では、SQL文の計画を選択する際にすべての計画を同等に検討します。この状況により、計画を改良する拡張機能では、SQL文の新しい計画を取得し、パフォーマンスを検証して新しい計画を計画ベースラインに受け入れることが可能になります。
ストアド・アウトラインをSQL計画ベースラインに移行するには:
-
適切な権限でSQL*Plusをデータベースに接続します。
-
PL/SQLファンクション
MIGRATE_STORED_OUTLINE
をコールします。次のPL/SQLブロック・サンプルは、すべてのストアド・アウトラインを固定ベースラインに移行します。
DECLARE my_report CLOB; BEGIN my_outlines := DBMS_SPM.MIGRATE_STORED_OUTLINE( attribute_name => 'all' ); END; /
関連項目:
-
DBMS_SPM
パッケージについて学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください -
ALTER SYSTEM
文について学習するには、『Oracle Database SQL言語リファレンス』を参照してください
30.4 アウトラインの移行でのストアド・アウトライン動作の保存
ストアド・アウトラインをSQL計画ベースラインに移行し、固定計画ベースラインを作成して元のストアド・アウトラインの動作を保持できます。
固定計画は、同じSQL文に対する他の計画よりも優先順位が高くなります。計画が固定されている場合、計画ベースラインは進化できません。計画ベースラインに固定計画が含まれていると、新しい計画は追加されません。
前提条件
このチュートリアルでは、次のことが前提となっています。
-
firstrow
というカテゴリのストアド・アウトラインのみを移行する。 -
ベースラインのモジュール名と移行するアウトラインのカテゴリ名を同じ名前にする。
ストアド・アウトラインを計画ベースラインに移行するには:
-
適切な権限でSQL*Plusをデータベースに接続します。
-
PL/SQLファンクション
MIGRATE_STORED_OUTLINE
をコールします。次のPL/SQLブロック・サンプルは、
firstrow
カテゴリのストアド・アウトラインを固定ベースラインに移行します。DECLARE my_report CLOB; BEGIN my_outlines := DBMS_SPM.MIGRATE_STORED_OUTLINE( attribute_name => 'category', attribute_value => 'firstrow', fixed => 'YES' ); END; /
移行後、SQL計画ベースラインのモジュールは
firstrow
、カテゴリはDEFAULT
です。
関連項目:
-
DBMS_SPM.MIGRATE_STORED_OUTLINE
ファンクションの構文およびセマンティクスは、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください -
ALTER SYSTEM
文について学習するには、『Oracle Database SQL言語リファレンス』を参照してください
30.5 ストアド・アウトラインの移行後のフォーローアップ・タスクの実行
アウトラインをSQL計画に移行した後、なんらかのフォーローアップ作業を実行する必要があります。
このタスクの目的は、次のとおりです。
-
SQL計画ベースラインに移行されたストアド・アウトラインで、ストアド・アウトラインではなく、計画ベースラインを使用するようにデータベースを構成する。
-
ストアド・アウトラインではなく、SQL計画ベースラインを作成して、今後のSQL文で使用する。
-
SQL計画ベースラインに移行されたストアド・アウトラインを削除する。
この項では、ストアド・アウトラインおよび計画ベースラインに関連する初期化パラメータの設定について説明します。OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
およびCREATE_STORED_OUTLINES
初期化パラメータは、ストアド・アウトラインおよびSQL計画ベースラインを作成する方法および時期を指定します。表30-5に、これらのパラメータ間の相互作用を示します。
表 30-5 アウトラインおよびベースラインの作成
CREATE_STORED_OUTLINES初期化パラメータ | OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES初期化パラメータ | データベースの動作 |
---|---|---|
|
|
SQL文を実行する際に、ストアド・アウトラインもSQL計画ベースラインも作成されません。 |
|
|
繰返し可能なSQL文の自動認識およびこれらの文のSQL計画ベースラインの生成は有効です。SQL文を実行する際に、その文に対して |
|
|
セッション中に発行された各問合せのアウトラインが自動的に作成され、保存されます。SQL文を実行する際に、その文に対して |
|
|
SQL文を実行する際に、その文に対して指定されたカテゴリ名の新規ストアド・アウトラインのみが作成されます(存在しない場合)。 |
|
|
セッション中に発行された各問合せのアウトラインが自動的に作成され、保存されます。繰返し可能なSQL文の自動認識およびこれらの文のSQL計画ベースラインの生成も有効です。 SQL文を実行する際に、 |
|
|
セッション中に発行された各問合せのアウトラインが自動的に作成され、保存されます。繰返し可能なSQL文の自動認識およびこれらの文のSQL計画ベースラインの生成も有効です。 SQL文を実行する際に、指定されたカテゴリ名のストアド・アウトラインが作成され、 |
USE_STORED_OUTLINES
セッション・パラメータ(これは初期化パラメータではない)およびOPTIMIZER_USE_SQL_PLAN_BASELINES
初期化パラメータは、ストアド・アウトラインおよび計画ベースラインの使用方法を指定します。表30-6に、これらのパラメータの相互作用を示します。
表30-6 ストアド・アウトラインおよびSQL計画ベースラインの使用方法
USE_STORED_OUTLINESセッション・パラメータ | OPTIMIZER_USE_SQL_PLAN_BASELINES初期化パラメータ | データベースの動作 |
---|---|---|
|
|
SQL文の計画を選択する際に、ストアド・アウトラインも計画ベースラインも使用しません。 |
|
|
SQL文の計画を選択する際に、SQL計画ベースラインのみを使用します。 |
|
|
SQL文の計画を選択する際に、 |
|
|
SQL文の計画を選択する際に、指定されたカテゴリ名のストアド・アウトラインを使用します。 指定されたカテゴリ名のストアド・アウトラインが存在しない場合は、 |
|
|
SQL文の計画を選択する際に、計画ベースラインよりもストアド・アウトラインが優先されます。 その文に |
|
|
SQL文の計画を選択する際に、計画ベースラインよりもストアド・アウトラインが優先されます。 その文に、指定されたカテゴリ名または |
前提条件
このチュートリアルでは、次のことが前提となっています。
-
ストアド・アウトラインの移行の基本ステップが完了している。
-
一部のストアド・アウトラインは、Oracle Database 10gより前のリリースで作成されている可能性がある。
Oracle Database 10gより前のリリースでは、ローカル・ヒント形式のヒントを使用しています。移行後、計画ベースラインに格納されたヒントは、Oracle Database 10gで導入されたグローバル・ヒント形式を使用します。
移行後に、データベースを適切な状態に設定するには:
-
適切な権限でSQL*Plusをデータベースに接続して、移行によってSQL計画ベースラインが作成されていることを確認します。
計画が使用可能であり、承認されていることを確認します。たとえば、次の問合せを入力します(サンプル出力の一部が含まれます)。
SELECT SQL_HANDLE, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, MODULE FROM DBA_SQL_PLAN_BASELINES; SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX MODULE ------------------------- ---------- -------------- --- --- --- ------ SYS_SQL_f44779f7089c8fab STMT01 STORED-OUTLINE YES YES NO DEFAULT . . .
-
オプションとして、SQL計画ベースラインの属性を変更します。
たとえば、次の文を使用して、指定したSQL文のベースラインのステータスを
fixed
に変更します。DECLARE v_cnt PLS_INTEGER; BEGIN v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE( sql_handle=>'SYS_SQL_f44779f7089c8fab', attribute_name=>'FIXED', attribute_value=>'NO'); DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt); END; /
-
元のストアド・アウトラインのステータスを確認します。
たとえば、次の問合せを入力します(サンプル出力の一部が含まれます)。
SELECT NAME, OWNER, CATEGORY, USED, MIGRATED FROM DBA_OUTLINES ORDER BY NAME; NAME OWNER CATEGORY USED MIGRATED ---------- ---------- ---------- ------ ------------ STMT01 SYS DEFAULT USED MIGRATED STMT02 SYS DEFAULT USED MIGRATED . . .
-
SQL計画ベースラインに移行されたすべてのストアド・アウトラインを削除します。
たとえば、次の文を使用して、
DBA_OUTLINES
のMIGRATED
ステータスのすべてのストアド・アウトラインを削除します。DECLARE v_cnt PLS_INTEGER; BEGIN v_cnt := DBMS_SPM.DROP_MIGRATED_STORED_OUTLINE(); DBMS_OUTPUT.PUT_LINE('Migrated stored outlines dropped: ' || v_cnt); END; /
-
初期化パラメータを次のように設定します。
-
SQL文を実行する際に、計画ベースラインを作成し、ストアド・アウトラインを作成しないように設定します。
-
同等のSQL計画ベースラインが存在しない場合、ストアド・アウトラインのみを使用するように設定します。
たとえば、次のSQL文は、SQL文を実行する際に、ストアド・アウトラインではなくSQL計画ベースラインを作成するようデータベースに指示します。また、
allrows
またはDEFAULT
カテゴリのストアド・アウトラインは、それが存在し、SQL計画ベースラインに移行されていない場合にのみ適用するようデータベースに指示しています。それ以外の場合は、かわりにSQL計画ベースラインを適用します。ALTER SYSTEM SET CREATE_STORED_OUTLINE = false SCOPE = BOTH; ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = true SCOPE = BOTH; ALTER SYSTEM SET OPTIMIZER_USE_SQL_PLAN_BASELINES = true SCOPE = BOTH; ALTER SESSION SET USE_STORED_OUTLINES = allrows SCOPE = BOTH;
-
関連項目:
-
DBMS_SPM
パッケージについて学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください -
CREATE_STORED_OUTLINES
初期化パラメータについて学習するには、Oracle Databaseリファレンスを参照してください