| Oracle® Database SQLチューニング・ガイド 12c リリース1 (12.1) B71277-09 |
|
 前 |
 次へ |
ストアド・アウトラインの移行は、保存されたアウトラインをSQL計画ベースラインに変換するためにユーザーが開始するプロセスです。SQL計画ベースラインは、最適なパフォーマンスを提供することが立証された計画のセットです。
この章では、ストアド・アウトラインの移行の概要およびタスクについて説明します。この章の内容は次のとおりです。
注意:
Oracle Database 12cで開始する場合は、ストアド・アウトラインは非推奨となっています。代替方法は、「ストアド・アウトラインのSQL計画ベースラインへの移行」を参照してください。
ストアド・アウトラインは、SQL文のヒントのセットです。ヒントは、文に対する特定の計画を選択するようオプティマイザに指示します。ストアド・アウトラインは、計画の安定性を提供するためのレガシー・テクニックです。
この項の内容は次のとおりです。
計画の安定性を維持し、パフォーマンスの低下を防止するためにストアド・アウトラインに依存している場合は、ストアド・アウトラインからSQL計画ベースラインに安全に移行できます。移行した後、同じ計画の安定性を維持し、SQL計画管理フレームワークで提供された機能を利用できます。
ストアド・アウトラインには、次のデメリットがあります。
ストアド・アウトラインは、時間の経過とともに自動的に進化しません。したがって、ストアド・アウトラインは、作成した時点では最適であっても、データベースの変更後に最適ではない計画となってパフォーマンスの低下をもたらす場合があります。
ストアド・アウトラインのヒントは、削除された索引に対する索引ヒントと同様に無効になる場合があります。この場合、データベースでは、無効なヒントを除外してアウトラインを使用し続け、多くの場合、元の計画またはオプティマイザによって生成される現在の最適なコスト計画より劣悪な計画が生じます。
SQL文では、現在指定されているカテゴリでストアド・アウトラインに定義された計画のみを選択できます。パフォーマンスが向上する場合でも、異なるカテゴリのストアド・アウトラインからの選択、または現在のコストベースの計画は選択できません。
ストアド・アウトラインは、事後対応型のチューニング・テクニックです。したがって、パフォーマンス問題が発生した後にのみストアド・アウトラインを使用して問題に対処します。たとえば、高負荷になったSQL文の計画を修正するストアド・アウトラインを実装できます。この場合、高負荷になる前に予防的に文をチューニングせずに、ストアド・アウトラインを使用します。
ストアド・アウトライン移行PL/SQL APIは、次のように前述の問題を解決するのに役立ちます。
SQL計画ベースラインにより、オプティマイザが同じ最適な計画を使用し、時間の経過とともに計画を進化させることができます。
指定したSQL文の新しい計画は、パフォーマンスの低下が生じないことを検証した後にSQL計画ベースラインとして追加できます。
SQL計画ベースラインは、無効なヒントのために計画が再生できなくなることを防止します。
計画ベースラインに格納されたヒントが無効になった場合、その計画は再生可能な計画ではありません。この場合は、代替の再生可能な計画ベースラインまたはオプティマイザによって生成された現在の最適なコスト計画が選択されます。
特定のSQL文に対する複数の計画ベースラインを管理できます。
オプティマイザは、特定のSQL文の計画を最適な計画のセットから選択できるため、ストアド・アウトラインでのカテゴリごとに1つの計画といった要件に制限されることはありません。
この項では、ストアド・アウトラインをSQL計画ベースラインに移行する方法について説明します。この情報は、ストアド・アウトラインの移行タスクの実行に関する重要な情報です。
ストアド・アウトラインを移行するには、ストアド・アウトラインを指定します。SQL計画ベースラインが作成され、更新されます。
移行プロセスには、次のステージがあります。
ユーザーが、移行するアウトラインを指定するファンクションを起動します。
データベースでアウトラインが次のように処理されます。
データベースが計画ベースラインに必要なアウトラインの情報をコピーします。
データベースでは、情報を直接コピーするか、アウトラインの情報に基づいて計算します。たとえば、SQL文のテキストは両方のスキーマに存在するので、テキストをアウトラインからベースラインにコピーできます。
データベースでは、ヒントを再解析してアウトラインにない情報を取得します。
計画のハッシュ値および計画のコストは、アウトラインの既存情報から得られないため、ヒントの再解析が必要です。
データベースがベースラインを作成します。
データベースでは、初めてSQL計画ベースラインを選択してSQL文を実行する際に、不足している情報を取得します。
コンパイル環境および実行統計は、実行する際に、計画ベースラインを解析およびコンパイルするときにのみ取得できます。
これらのフェーズが完了した後、移行が完了します。
アウトラインはヒントのセットであり、SQL計画ベースラインは計画のセットです。
これらは異なる技術であるため、アウトラインの一部の機能は、ベースラインの機能に正確にマップされません。たとえば、1つのSQL文に、カテゴリの異なる複数のアウトラインが存在する可能性がありますが、現在ベースラインに存在するカテゴリはDEFAULTのみです。
アウトラインのカテゴリは、SQL計画ベースラインのモジュールに相当します。表24-1に、アウトラインのカテゴリがどのようにモジュールにマップするかを説明します。
表24-1 アウトラインのカテゴリ
| 概念 | 説明 | デフォルト値 |
|---|---|---|
アウトライン・カテゴリ |
ユーザーにより定義されたストアド・アウトライン・セットの分類。 カテゴリを使用して、ストアド・アウトラインの異なるSQL文を管理できます。たとえば、1つの文が、 各SQL文には、1つ以上のストアド・アウトラインがあります。各ストアド・アウトラインは、1つのカテゴリにのみ分類されます。1つの文にはカテゴリの異なる複数のストアド・アウトラインがありますが、各文には1つのカテゴリにつき1つのストアド・アウトラインのみ存在します。 移行の際に、各アウトライン・カテゴリは、SQL計画ベースライン・モジュールにマップされます。 |
|
ベースライン・モジュール |
実行されている高度な機能。 SQL計画ベースラインは、1つのモジュールにのみ所属できます。 |
アウトラインのSQL計画ベースラインへの移行後、デフォルトのモジュール名はアウトライン・カテゴリ名です。 |
ベースライン・カテゴリ |
SQL計画ベースラインには、カテゴリが1つのみ存在します。それは 1つの文は、 |
|
Oracle Databaseでは、ストアド・アウトラインをSQL計画ベースラインに移行する際に、すべてのアウトライン・カテゴリを同名のSQL計画ベースライン・モジュールにマップします。次の図に示すように、アウトラインのOLTPカテゴリは、ベースラインのOLTPモジュールにマップされます。移行後、DEFAULTは、すべてのSQL計画ベースラインを含むスーパー・カテゴリです。
DBMS_SPMパッケージを使用してストアド・アウトラインを移行できます。
表24-2 ストアド・アウトラインの移行に関連するDBMS_SPMファンクション
| DBMS_SPMファンクション | 説明 |
|---|---|
|
既存のストアド・アウトラインを計画ベースラインに移行します。 次のいずれかの形式を使用します。
|
|
SQL文に関連付けられた1つの計画またはすべての計画の属性を変更します。 |
|
SQL計画ベースラインに移行されたストアド・アウトラインを削除します。
|
ストアド・アウトラインおよび計画ベースラインの動作は、初期化パラメータおよびセッション・パラメータで制御できます。表24-3に、関連パラメータを示します。これらのパラメータ設定の相互作用の説明は、表24-5および表24-6を参照してください。
表24-3 ストアド・アウトラインの移行に関連するパラメータ
| 初期化またはセッション・パラメータ | 説明 | パラメータ・タイプ |
|---|---|---|
|
セッション中に発行された各問合せのアウトラインを自動的に作成して保存するかどうかを決定します。 |
初期化パラメータ |
|
繰返し可能なSQL文の自動認識およびそれらの文のSQL計画ベースラインの生成を有効または無効にします。 |
初期化パラメータ |
|
SQL管理ベースに格納されたSQL計画ベースラインの使用を有効または無効にします。 |
初期化パラメータ |
|
オプティマイザが実行計画の生成にストアド・アウトラインを使用するかどうかを決定します。 注意: これは初期化パラメータではなく、セッション・パラメータです。 |
セッション |
データベース・ビューを使用して、ストアド・アウトラインの移行に関連する情報にアクセスできます。表24-4に、主なビューを示します。
表24-4 ストアド・アウトラインの移行に関連するビュー
| ビュー | 説明 |
|---|---|
|
データベースのすべてのストアド・アウトラインについて説明します。
|
|
特定のSQL文に関して現在作成されているSQL計画ベースラインの情報を表示します。
|
関連項目:
DBMS_SPMパッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
CREATE_STORED_OUTLINES初期化パラメータの詳細は、Oracle Databaseリファレンスを参照してください
この項では、PL/SQL APIを使用してストアド・アウトラインを移行する場合の基本ステップを示します。基本手順は次のとおりです。
ストアド・アウトラインの移行の準備をします。
移行の前提条件を確認し、移行後の計画ベースラインの動作を決定します。
「ストアド・アウトラインの移行の準備」を参照してください。
次のいずれかを実行します。
ベースラインに移行して、SQL計画管理の機能を使用します。
「アウトラインの移行によるSQL計画管理機能の使用」を参照してください。
ストアド・アウトラインの動作を正確に維持して、ベースラインに移行します。
「アウトラインの移行でのストアド・アウトライン動作の保存」を参照してください。
移行後の確認およびクリーンアップを実行します。
「ストアド・アウトラインの移行後のフォーローアップ・タスクの実行」を参照してください。
この項では、ストアド・アウトラインの移行の準備について説明します。
ストアド・アウトラインの移行の準備を行うには、次の手順を実行します。
SYSDBA権限またはDBMS_SPMパッケージのEXECUTE権限でSQL*Plusをデータベースに接続します。
たとえば、オペレーティング・システム認証を使用してデータベースにSYSとしてログオンするには、次のコマンドを実行します。
% sqlplus /nolog SQL> CONNECT / AS SYSDBA
データベースのストアド・アウトラインの問合せを実行します。
次の例では、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リファレンス』を参照してください。
このタスクの目的は、次のとおりです。
移行後、SQL文に対して同じ固定計画を適用するのではなく、SQL計画管理で計画ベースラインのすべての計画から選択できるようにする。
ベースラインに新しい計画を追加して、データベースの変更があった場合にSQL計画ベースラインが進化できるようにする。
前提条件
このチュートリアルでは、次のことが前提となっています。
すべてのアウトラインを移行する。
特定のアウトラインを移行する場合、DBMS_SPM.MIGRATE_STORED_OUTLINEファンクションの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
ベースラインのモジュール名と移行するアウトラインのカテゴリ名を同じ名前にする。
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言語リファレンス』を参照してください。
ストアド・アウトラインを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言語リファレンス』を参照してください
アウトラインをSQL計画ベースラインに移行した後、フォローアップ作業を実行する必要があります。
このタスクの目的は、次のとおりです。
SQL計画ベースラインに移行されたストアド・アウトラインで、ストアド・アウトラインではなく、計画ベースラインを使用するようにデータベースを構成する。
ストアド・アウトラインではなく、SQL計画ベースラインを作成して、今後のSQL文で使用する。
SQL計画ベースラインに移行されたストアド・アウトラインを削除する。
この項では、ストアド・アウトラインおよび計画ベースラインに関連する初期化パラメータの設定について説明します。OPTIMIZER_CAPTURE_SQL_PLAN_BASELINESおよびCREATE_STORED_OUTLINES初期化パラメータは、ストアド・アウトラインおよびSQL計画ベースラインを作成する方法および時期を指定します。表24-5に、これらのパラメータ間の相互作用を示します。
表 24-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初期化パラメータは、ストアド・アウトラインおよび計画ベースラインの使用方法を指定します。表24-6に、これらのパラメータの相互作用を示します。
表24-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リファレンスを参照してください
