| Oracle Databaseパフォーマンス・チューニング・ガイド 11gリリース1（11.1） E05743-02 |
|
 戻る |
 次へ |
この章では、プラン・スタビリティを使用してパフォーマンス特性を保持する方法について説明します。プラン・スタビリティは、Oracleの新規リリースにアップグレードする際に、ルールベース・オプティマイザから問合せオプティマイザに移行する場合にも役立ちます。
この章には次の項があります。
|
注意: ストアド・アウトラインは、SQL計画の管理を優先するために将来のリリースではサポートされなくなります。Oracle Database 11gリリース1（11.1）では、ストアド・アウトラインは以前のリリースと同様に引き続き機能します。ただし、新規アプリケーションではSQL計画の管理を使用することを強くお薦めします。SQL計画の管理により作成されるSQL計画ベースラインでは、ストアド・アウトラインより優れたSQLパフォーマンスおよび安定性が提供されます。既存のストアド・アウトラインがある場合は、それらを |
|
関連項目:
|
プラン・スタビリティは、データベース環境の一定の変更がアプリケーションのパフォーマンス特性に影響を与えないようにします。このような変更には、オプティマイザ統計の変更、オプティマイザ・モード設定の変更、およびメモリー構造のサイズに影響を与えるパラメータ（SORT_AREA_SIZEやBITMAP_MERGE_AREA_SIZEなど）の変更が含まれます。プラン・スタビリティは、アプリケーションのパフォーマンスの変化を望まない場合に非常に便利です。
プラン・スタビリティでは、ストアド・アウトラインに実行計画を保持します。アウトラインは、SQL文に関連するオプティマイザ・ヒントのセットとして実装されます。文に対してアウトラインの使用が有効化されている場合、Oracleでは格納されたヒントが自動的に考慮され、それらのヒントに準拠して実行計画が生成されます。
Oracleでは、1つまたはすべてのSQL文に対してパブリックまたはプライベートのストアド・アウトラインを作成できます。ストアド・アウトラインの使用を有効化すると、オプティマイザによりアウトラインから同等の実行計画が生成されます。アウトラインはカテゴリ別にグループ化できるため、Oracleで使用するアウトラインのカテゴリを制御してアウトラインの管理とデプロイメントを簡略化できます。
Oracleがストアド・アウトラインに保持する計画は、システムの構成または統計が変更されても一貫性が維持されます。ストアド・アウトラインを使用すると、将来のOracleリリースでオプティマイザが変更されても、生成された実行計画の安定性は確保されます。
|
注意: 大量配布用のアプリケーションを開発する場合、ストアド・アウトラインを使用すると、すべての顧客が同じ実行計画に確実にアクセスできます。 |
Oracleではヒントを使用して格納計画を記録するため、プラン・スタビリティでどの程度まで実行計画が制御されるかは、Oracleのヒント・メカニズムでどの程度実行計画が制御されるかによります。
SQLテキストとそのストアド・アウトラインの間には、1対1の対応関係があります。述語内で異なるリテラルを指定すると、異なるアウトラインが適用されます。これを回避するには、アプリケーションのリテラルをバインド変数に置換します。
|
関連項目: Oracleでは、リテラルをシステム生成のバインド変数に置換して、同様の文でSQLを共有できます。アウトラインがCREATE OUTLINE文ではなくCREATE_STORED_OUTLINESパラメータを使用して生成されている場合、この操作はプラン・スタビリティでも機能します。また、アウトラインは、CURSOR_SHARINGパラメータがSIMILARに設定された状態で作成されている必要があり、アウトラインの使用を試みる場合もこのパラメータがSIMILARに設定されている必要があります。詳細は、第7章「メモリーの構成と使用方法」を参照してください。 |
プラン・スタビリティは、パフォーマンスが望ましい状態にある時点での実行計画を保持する機能です。ただし、多くの環境で、datesやorder numbersなどのデータ型の属性はすぐに変化します。この場合、実行計画を永続的に使用すると、データ特性の変化につれて時間経過とともにパフォーマンスが低下する可能性があります。
つまり、動的環境で計画を保持する技術は、問合せ最適化の使用目的に対してはやや逆効果です。問合せ最適化では、データ状態を正確に反映する統計に基づいて実行計画を生成しようとします。そのため、プラン・スタビリティを制御する必要性と、データ特性の変化に対応できるオプティマイザの機能から得られる利点をバランスさせる必要があります。
アウトラインは、特定のSQL文の実行計画生成に対するオプティマイザの結果と同等の一連のヒントで主に構成されます。Oracleでアウトラインが作成される場合、プラン・スタビリティでは、実行計画の生成時と同じデータを使用して最適化の結果を調査します。つまり、Oracleでは、アウトラインを生成するために実行計画に対する入力データを使用し、実行計画それ自体は使用しません。
|
注意: Oracleでは、OL$表とOL$HINTS表のデータに基づいて、SYS表領域にそれぞれUSER_OUTLINESビューとUSER_OUTLINE_HINTSビューを作成します。OL$、OL$HINTSおよびOL$NODES表を直接操作することは禁止されています。
ヒントをSQL文に埋め込むこともできますが、この操作はOracleによるアウトラインの使用方法に影響を与えません。Oracleでは、ヒントにより変更されたSQL文は、アウトラインに格納されている元のSQL文とは異なるものとみなされます。 |
Oracleでは、アウトライン・データをOL$、OL$HINTSおよびOL$NODES表に格納します。これらの表を削除しなければ、アウトラインは無期限に保持されます。
キャッシュされている実行計画に対するアウトラインの唯一の効果は、計画がキャッシュ内にあるかどうかを識別するため、SQLテキストに加えてアウトラインのカテゴリ名が使用されることです。これにより、Oracleでは、あるカテゴリでコンパイルする必要のあるSQL文を実行するのに、別のカテゴリでコンパイルされた実行計画を使用することがなくなります。
アウトラインを適切に機能させるには、いくつかのパラメータ（特に_ENABLEDという接尾辞で終わるパラメータ）の設定を実行環境全体で統一する必要があります。これらのパラメータは次のとおりです。
QUERY_REWRITE_ENABLED
STAR_TRANSFORMATION_ENABLED
OPTIMIZER_FEATURES_ENABLE
DBMS_OUTLNおよびDBMS_OUTLN_EDITパッケージには、ストアド・アウトラインとそのアウトライン・カテゴリの管理に使用できるプロシージャが含まれます。
DBMS_OUTLNを実行するにはEXECUTE_CATALOG_ROLEロールが必要ですが、パブリックはDBMS_OUTLN_EDITの実行権限を保持しています。DBMS_OUTLN_EDITパッケージは、実行者権限パッケージです。
DBMS_OUTLNおよびDBMS_OUTLN_EDITの有益なプロシージャの一部は次のとおりです。
CLEAR_USED: 指定したアウトラインを消去します。
DROP_BY_CAT: 指定したカテゴリに属するアウトラインを削除します。
UPDATE_BY_CAT: アウトラインのカテゴリを、指定したカテゴリから新しく指定したカテゴリに変更します。
EXACT_TEXT_SIGNATURES: テキストの完全一致スキームに従ってアウトラインのシグネチャを計算します。
GENERATE_SIGNATURE: 指定したSQLテキストのシグネチャを生成します。
|
関連項目:
|
Oracleでは、すべてのSQL文のアウトラインを自動的に作成できます。ユーザーは、特定のSQL文のアウトラインを作成できます。いずれの場合も、アウトラインはオプティマイザからその入力データを導出します。
初期化パラメータCREATE_STORED_OUTLINESがtrueに設定されている場合、Oracleではストアド・アウトラインが自動的に作成されます。アクティブ化されると、Oracleではすべてのコンパイル済SQL文のアウトラインが作成されます。ユーザーは、CREATE OUTLINE文を使用して特定の文のストアド・アウトラインを作成できます。
プライベート・アウトラインを作成または編集すると、そのアウトライン・データはSYSTEMスキーマのグローバル一時表に書き込まれます。これらの表には、OL$、OL$HINTSおよびOL$NODESシノニムを使用してアクセスできます。
|
注意: アウトラインが作成されるスキーマには、CREATE ANY OUTLINE権限が付与されていることを確認してください。付与されていない場合、CREATE_STORED_OUTLINE初期化パラメータを有効にしても、アプリケーションの実行後にデータベースでアウトラインを検出できません。
また、 |
|
関連項目:
|
アウトラインは、カテゴリに分類して管理タスクを簡略化できます。CREATE OUTLINE文では、カテゴリを指定できます。指定しない場合、DEFAULTカテゴリが選択されます。同様に、CREATE_STORED_OUTLINES初期化パラメータでもカテゴリ名を指定できます。trueを指定すると、DEFAULTカテゴリにアウトラインが生成されます。
CREATE_STORED_OUTLINES初期化パラメータを使用してカテゴリ名を指定すると、その後作成されるすべてのアウトラインは、カテゴリ名が再設定されるまでそのカテゴリに割り当てられます。パラメータをfalseに設定すると、アウトライン生成は一時停止されます。
CREATE_STORED_OUTLINESをtrueに設定した場合、またはカテゴリ名なしでCREATE OUTLINE文を使用した場合、アウトラインはDEFAULTのカテゴリ名に割り当てられます。
ストアド・アウトラインの使用を有効化すると、Oracleでは常に問合せオプティマイザが使用されます。これは、アウトラインはヒントに依存しており、それを有効化するにはほとんどのヒントで問合せオプティマイザを必要とするためです。
OracleによるSQL文のコンパイル時にストアド・アウトラインを使用するには、システム・パラメータUSE_STORED_OUTLINESをtrueまたはカテゴリ名に設定します。USE_STORED_OUTLINESをtrueに設定すると、defaultカテゴリのアウトラインが使用されます。USE_STORED_OUTLINESパラメータでカテゴリを指定すると、このパラメータを別のカテゴリ名に再設定するまで、またはUSE_STORED_OUTLINESをfalseに設定してアウトラインの使用を一時停止するまで、同じカテゴリのアウトラインが使用されます。ユーザーが指定したカテゴリ名について、SQL文に一致するそのカテゴリ内でOracleがアウトラインを検出できない場合、defaultカテゴリのアウトラインが検索されます。
カテゴリ内のすべてのアウトラインではなく、特定のアウトラインを使用する場合は、ALTER OUTLINE文を使用してその特定のアウトラインを有効化します。カテゴリ内の特定のアウトライン以外のアウトラインを使用する場合、ALTER OUTLINE文を使用して、使用中のカテゴリ内の特定のアウトラインを無効化します。ALTER OUTLINE文では、ストアド・アウトラインの名前変更、異なるカテゴリへの再割当て、または再生成も可能です。
|
関連項目: ALTER OUTLINE文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 |
指定したアウトラインにより制御できるのは、アウトラインを保持するSQL文のコンパイルのみです。USE_STORED_OUTLINESをfalseに設定すると、アウトラインは使用されません。USE_STORED_OUTLINESをfalseに設定し、CREATE_STORED_OUTLINESをtrueに設定すると、アウトラインは作成されますが、使用されません。
USE_PRIVATE_OUTLINESパラメータでは、プライベート・アウトラインの使用を制御できます。プライベート・アウトラインは、現在のセッションでのみ表示されるアウトラインで、そのデータは現在の解析スキーマに存在します。このアウトラインに対する変更は、システムの他のセッションからは認識されません。プライベート・アウトラインを文のコンパイルに適用する操作は、USE_PRIVATE_OUTLINESパラメータを使用して現在のセッションでのみ実行できます。編集内容をパブリック領域に保存するよう明示的に選択した場合にのみ、それらの変更は他のユーザーにも表示されます。
オプティマイザでは、通常、問合せに対する最適な計画が選択されますが、オプティマイザが準拠する経験則とは一致しない実行環境に関する知識をユーザーが保持していることもあります。アウトラインを直接編集することで、アプリケーションを変更することなくSQL問合せをチューニングできます。
USE_PRIVATE_OUTLINESパラメータが有効化され、アウトライン化されたSQL文が発行される場合、USE_STORED_OUTLINESの有効時には、オプティマイザでは使用中のパブリック領域ではなくセッションのプライベート領域からアウトラインを取得します。セッションのプライベート領域にアウトラインが存在しない場合、オプティマイザでは文のコンパイルにアウトラインを使用しません。
CREATE OUTLINE文の実行には、CREATE ANY OUTLINE権限が必要です。FROM句を指定する場合、SELECT権限も必要です。この権限は、アウトライン文に関連するSQLテキストとヒント・テキストを表示する資格を持つユーザーにのみ付与する必要があります。CREATE OUTLINE FROMコマンドの発行者がアウトラインの所有者でない場合、このロールはこのコマンドに対して必須です。
|
注意: USE_STORED_OUTLINESおよびUSE_PRIVATE_OUTLINESパラメータは、システムまたはセッションに固有です。初期化パラメータではありません。これらのパラメータの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 |
アウトラインが使用されているかどうかは、V$SQLビューで調査できます。SQL文と組み合せてOUTLINE_CATEGORY列を問い合せます。アウトラインが適用されている場合、この列には、アウトラインが属するカテゴリが含まれます。それ以外の場合、この列はNULLです。OUTLINE_SID列では、特定のカーソルでパブリック・アウトラインを使用しているか（値が0の場合）、プライベート・アウトラインを使用しているか（アウトラインを使用している対応セッションのセッションSIDの場合）を判別できます。
たとえば、次のようにします。
SELECT OUTLINE_CATEGORY, OUTLINE_SID FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT COUNT(*) FROM emp%';
Oracleのデータ・ディクショナリに格納されているアウトラインおよび関連ヒントのデータに関する情報は、次のビューで参照できます。
USER_OUTLINES
USER_OUTLINE_HINTS
ALL_OUTLINES
ALL_OUTLINE_HINTS
DBA_OUTLINES
DBA_OUTLINE_HINTS
アウトライン・カテゴリがmycatのアウトライン情報をUSER_OUTLINESビューから取得するには、次の構文を使用します。
SELECT NAME, SQL_TEXT FROM USER_OUTLINES WHERE CATEGORY='mycat';
これにより、カテゴリmycatに属するすべてのアウトラインの名前とテキストが表示されます。
アウトラインname1に対して生成されたすべてのヒントを参照するには、次の構文を使用します。
SELECT HINT FROM USER_OUTLINE_HINTS WHERE NAME='name1';
互換性、形式、およびアウトラインの有効と無効に関する情報は、_OUTLINESビューのフラグをチェックします。たとえば、アウトラインが有効かどうかを判別するには、USER_OUTLINESビューのENABLEDフィールドをチェックします。
SELECT NAME, CATEGORY, ENABLED FROM USER_OUTLINES;
|
関連項目: アウトラインに関連するビューの詳細は、『Oracle Databaseリファレンス』を参照してください。 |
Oracleでは、OL$表とOL$HINTS表のデータに基づいて、それぞれUSER_OUTLINESビューとUSER_OUTLINE_HINTSビューを作成します。これらの表に加え、OUTLNというスキーマを使用してSYSTEM表領域にOL$NODES表も作成されます。アウトラインがSYSTEM表領域で使用する領域が多すぎる場合、それらのデータを移動できます。これには、次の手順を使用して、個別の表領域を作成し、その表領域にアウトライン表を移動します。
CREATE_STORED_OUTLINESパラメータを有効化した状態で、実行中のアプリケーションにリテラルSQL文が多く含まれると、デフォルトのシステム表領域が使い果される可能性があります。この問題が発生した場合、DBMS_OUTLN.DROP_UNUSEDプロシージャを使用してこれらのリテラルSQLアウトラインを削除してください。
Oracleのエクスポート・ユーティリティを使用して、OL$、OL$HINTSおよびOL$NODES表をエクスポートします。
EXP OUTLN/outln_password
FILE = exp_file TABLES = 'OL$' 'OL$HINTS' 'OL$NODES'
SQL*Plusを起動し、データベースに接続します。
CONNECT OUTLN/outln_password;
以前のOL$、OL$HINTSおよびOL$NODES表を削除します。
DROP TABLE OL$; DROP TABLE OL$HINTS; DROP TABLE OL$NODES;
各表に対応する新規表領域を作成します。
CONNECT SYSTEM/system_password;
CREATE TABLESPACE outln_ts
DATAFILE 'tspace.dat' SIZE 2M
DEFAULT STORAGE (INITIAL 10K NEXT 20K MINEXTENTS 1 MAXEXTENTS 999
PCTINCREASE 10)
ONLINE;
次の文を入力し、デフォルト表領域を変更します。
ALTER USER OUTLN DEFAULT TABLESPACE outln_ts;
OUTLN_TS表領域に強制的にインポートするため、OUTLNユーザーに関してSYSTEM表領域の割当て制限を0Kに設定します。また、UNLIMITED TABLESPACE権限や、無制限の表領域権限または割当て制限を持つすべてのロール（RESOURCEロールなど）を取り消す必要があります。OUTLN表領域の割当て制限を設定します。
OL$、OL$HINTSおよびOL$NODES表をインポートします。
IMP OUTLN/outln_password
FILE = exp_file TABLES = (OL$, OL$HINTS, OL$NODES)
インポート・プロセスが完了すると、OL$、OL$HINTSおよびOL$NODES表がOUTLNスキーマで再作成され、OUTLN_TSという新規表領域に配置されます。
プロセスの完了時に、前の手順で削除した権限およびロールを追加して、OUTLNユーザーの表領域割当て制限を適切に調整できます。
|
関連項目:
|
この項では、問合せオプティマイザの機能を利用してパフォーマンスを大幅に向上する際に使用できる手順について説明します。プラン・スタビリティでは、システムの目標とする実行計画を望ましいパフォーマンスで維持する方法が提供されますが、残りのSQL文に関しては新しい問合せオプティマイザの機能も利用できます。
元の実行計画の正確な再現が保証されないSQL文および機能のクラスも存在しますが、それでもプラン・スタビリティは移行プロセスにおいて非常に役立つ機能です。移行前に、アプリケーションのSQL文の大部分または全部がカバーされるまで、実行計画のアウトライン取得を有効化する必要があります。移行後に、特定のSQL文でパフォーマンス上の問題が発生した場合、以前の動作を回復する方法としてその文に対応するストアド・アウトラインの使用を有効化できます。ストアド・アウトラインの使用は、それにより計画がデータ特性の変更に適応できなくなるため、移行に関連するパフォーマンス問題を解決する方法としては、最善ではないこともあります。ただし、この方法が、パフォーマンス問題の対処に使用できるテクニックの1つとなることも確かです。
この項には、次の項目があります。
アプリケーションがルールベース・オプティマイザを使用して開発されている場合、作業の大部分はSQL文を手動でチューニングしてパフォーマンスを最適化することに費やされています。プラン・スタビリティを使用すると、ルールベース・オプティマイザから問合せオプティマイザへのアップグレード時にアプリケーションの動作を維持することで、パフォーマンス・チューニングに投入してきた作業内容を活用できます。
問合せオプティマイザへの移行前にアプリケーションのアウトラインを作成することで、ルールベース・オプティマイザにより生成された計画を使用できます。一方で、移行後に新規に開発されたアプリケーションにより生成された文は、問合せ計画を使用します。アプリケーションのアウトラインを作成および使用するには、次の手順を実行します。
|
注意: この手順を慎重に確認し、その意味をよく検討してから実行してください。 |
アウトラインが作成されるスキーマにCREATE ANY OUTLINE権限が付与されていることを確認します。たとえば、SYSからの場合は次のようにします。
GRANT CREATE ANY OUTLINE TO user-name
次のような構文を実行します。RBOCATアウトライン・カテゴリを指定する場合、次のようにします。
ALTER SESSION SET CREATE_STORED_OUTLINES = rbocat;
重要なすべてのSQL文のストアド・アウトラインを取得できる程度に長い期間、アプリケーションを実行します。
アウトライン生成を一時停止します。
ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE;
DBMS_STATSパッケージで統計を収集します。
パラメータOPTIMIZER_MODEをCHOOSEに変更します。
次の構文を入力して、OracleでカテゴリRBOCATのアウトラインを使用できるようにします。
ALTER SESSION SET USE_STORED_OUTLINES = rbocat;
アプリケーションを実行します。
プラン・スタビリティの制限により、このアプリケーションのSQL文のアクセス・パスは変更できません。
|
注意: 手順2で問合せを実行しなかった場合、問合せオプティマイザへの移行後も問合せの古い動作を取得できます。これを行うには、オプティマイザ・モードをRULEに変更し、問合せのアウトラインを作成してから、オプティマイザ・モードをCHOOSEに戻します。 |
問合せオプティマイザを使用する新規Oracleリリースにアップグレードする場合、オプティマイザの変更により、一部のSQL文の実行計画が変更される可能性が常に存在します。このような変更はパフォーマンスの向上に役立ちますが、その一方で、優れたパフォーマンスを発揮しているために動作の変更が不必要なリスクであると考えられるアプリケーションが存在することもあります。このようなアプリケーションについては、アップグレード前に次の手順を使用してアウトラインを作成できます。
|
注意: この手順を慎重に確認し、その意味をよく検討してから実行してください。 |
次の構文を入力し、アウトライン作成を有効化します。
ALTER SESSION SET CREATE_STORED_OUTLINES = ALL_QUERIES;
重要なすべてのSQL文のストアド・アウトラインを取得できる程度に長い期間、アプリケーションを実行します。
次の構文を入力し、アウトライン生成を一時停止します。
ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE;
本番システムを新規リリースのRDBMSにアップグレードします。
アプリケーションを実行します。
アップグレード後に、ストアド・アウトラインの使用を有効化できます。または、アップグレード後にパフォーマンスの低下を示した文があった場合、バックアップとして保存してあるアウトラインを使用できます。
後者のアプローチでは、このような問題のある文に対して、次のように選択的にストアド・アウトラインを使用できます。
問題のあるSQL文ごとに、関連するストアド・アウトラインのCATEGORYを次のようなカテゴリ名に変更します。
ALTER OUTLINE outline_name CHANGE CATEGORY TO problemcat;
次の構文を入力して、Oracleでカテゴリproblemcatのアウトラインを使用できるようにします。
ALTER SESSION SET USE_STORED_OUTLINES = problemcat;
本番システムとは別個のテスト・システムは、アップグレードに伴うオプティマイザの動作を検証するのに便利です。インポートおよびエクスポート機能を使用して、本番システムからテスト・システムに統計を移行できます。これにより、テスト・システムの表にデータを移入する作業が容易になります。
アウトラインは、カテゴリ別にシステム間を移動できます。たとえば、problemcatカテゴリにアウトラインを作成した後に、問合せベースのエクスポート・オプションを使用してカテゴリ別にアウトラインをエクスポートできます。これは、ソース・データベースにあるすべてのアウトラインをエクスポートせずに、あるデータベースから別のデータベースに選択したアウトラインのみをエクスポートするための簡単で効率的な方法です。これを行うには、次の文を発行します。
EXP OUTLN/outln_password FILE=exp-file TABLES= 'OL$' 'OL$HINTS' 'OL$NODES' QUERY='WHERE CATEGORY="problemcat"'
