| Oracle® Databaseパフォーマンス・チューニング・ガイド 11gリリース2 (11.2) B56312-06 |
|
 前 |
 次 |
この章では、プラン・スタビリティを使用してパフォーマンス特性を保持する方法について説明します。プラン・スタビリティにより、新しいOracle Databaseリリースにアップグレードするとき、ルールベース・オプティマイザから問合せオプティマイザへの移行も容易になります。
この章には次の項があります。
|
注意: ストアド・アウトラインは、SQL計画の管理を優先するために将来のリリースではサポートされなくなります。Oracle Database 11gでは、ストアド・アウトラインは以前のリリースと同様に引き続き機能します。ただし、新規アプリケーションではSQL計画の管理を使用することを強くお薦めします。SQL計画の管理により作成されるSQL計画ベースラインでは、ストアド・アウトラインより優れたSQLパフォーマンスおよび安定性が提供されます。既存のストアド・アウトラインがある場合、「ストアド・アウトラインのSQL計画ベースラインへの移行」の手順に従って、SQL計画ベースラインへの移行を検討してください。移行が完了したら、ストアド・アウトラインを無効化または削除してください。 |
|
関連項目:
|
プラン・スタビリティは、データベース環境の一定の変更がアプリケーションのパフォーマンス特性に影響を与えないようにします。このような変更には、オプティマイザ統計の変更、オプティマイザ・モード設定の変更、およびメモリー構造のサイズに影響を与えるパラメータ(SORT_AREA_SIZEやBITMAP_MERGE_AREA_SIZEなど)の変更が含まれます。プラン・スタビリティは、アプリケーションのパフォーマンスの変化を望まない場合に非常に便利です。
プラン・スタビリティでは、ストアド・アウトラインに実行計画を保持します。アウトラインは、SQL文に関連するオプティマイザ・ヒントのセットとして実装されます。Oracle Databaseでは、文にアウトラインが使用可能な場合、格納されたヒントを自動的に考慮し、ヒントに基づいて実行計画の生成を試みます。
Oracle Databaseでは、1つまたはすべてのSQL文のパブリックまたはプライベートのストアド・アウトラインを作成できます。ストアド・アウトラインの使用を有効化すると、オプティマイザによりアウトラインから同等の実行計画が生成されます。アウトラインをカテゴリに分類して、どのカテゴリのアウトラインを使用するかを制御することで、アウトラインの管理とデプロイメントが容易になります。
Oracle Databaseによりストアド・アウトラインで管理される計画は、システムの構成または統計に変更が生じた場合でも一貫性が維持されます。また、今後のOracleリリースでオプティマイザが変更されても、ストアド・アウトラインを使用することで、生成された実行計画が安定したものになります。
|
注意: 大量配布用のアプリケーションを開発する場合、ストアド・アウトラインを使用すると、すべての顧客が同じ実行計画に確実にアクセスできます。 |
Oracle Databaseでは、ヒントを使用して格納計画が記録されるため、プラン・スタビリティが実行計画を制御する度合は、Oracle Databaseのヒント・メカニズムがどれだけ実行計画を制御するかによります。
SQLテキストとそのストアド・アウトラインの間には、1対1の対応関係があります。述語内で異なるリテラルを指定すると、異なるアウトラインが適用されます。この状況を回避するには、アプリケーションのリテラルをバインド変数に置換します。
|
関連項目: 類似した文では、リテラルをシステム生成のバインド変数に置換することで、SQLを共有できます。アウトラインがCREATE OUTLINE文ではなくCREATE_STORED_OUTLINESパラメータを使用して生成されている場合、この操作はプラン・スタビリティでも機能します。また、アウトラインは、CURSOR_SHARINGパラメータがFORCEに設定された状態で作成されている必要があり、アウトラインの使用を試みる場合もこのパラメータがFORCEに設定されている必要があります。詳細は、第7章「メモリーの構成および使用方法」を参照してください。 |
プラン・スタビリティは、パフォーマンスが望ましい状態にある時点での実行計画を保持する機能です。ただし、多くの環境で、datesやorder numbersなどのデータ型の属性は急速に変化します。この場合、実行計画を永続的に使用すると、データ特性の変化につれて時間経過とともにパフォーマンスが低下する可能性があります。
つまり、動的環境で計画を保持する技術は、問合せ最適化の使用目的に対してはやや逆効果です。問合せ最適化では、データ状態を正確に反映する統計に基づいて実行計画を生成しようとします。そのため、プラン・スタビリティを制御する必要性と、データ特性の変化に対応できるオプティマイザの機能から得られる利点をバランスさせる必要があります。
アウトラインは、特定のSQL文の実行計画生成に対するオプティマイザの結果と同等の一連のヒントで主に構成されます。アウトラインを作成するとき、プラン・スタビリティが最適化の結果を検査するために使用するデータは、実行計画の生成に使用されたデータと同じです。つまり、Oracle Databaseでは、実行計画への入力を使用してアウトラインを生成し、実行計画自体は使用しません。
|
注意: Oracle Databaseでは、OL$表とOL$HINTS表のデータに基づいて、USER_OUTLINESビューとUSER_OUTLINE_HINTSビューがSYS表領域に作成されます。OL$、OL$HINTSおよびOL$NODES表を直接操作することは禁止されています。
SQL文にヒントを埋め込んだ場合でも、Oracle Databaseでのアウトラインの使用方法には影響しません。Oracle Databaseでは、ヒントを指定して修正されたSQL文を、アウトラインに格納された元のSQL文とは別のものとして認識します。 |
Oracle Databaseでは、アウトライン・データをOL$、OL$HINTSおよびOL$NODES表に格納します。これらの表を削除しないかぎり、アウトラインは永続的に保存されます。
アウトラインによる実行計画のキャッシュでの影響は、キャッシュに計画があるかどうかを確認する際に、SQLテキストに加えてアウトラインのカテゴリ名が使用される点のみです。これにより、あるカテゴリでコンパイルされた実行計画が、別のカテゴリでコンパイルする必要のある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テキストのシグネチャを生成します。
|
関連項目: DBMS_OUTLNパッケージ・プロシージャの使用方法の詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 |
Oracle Databaseでは、すべてのSQL文のアウトラインの作成を自動的に行うか、特定のSQL文についてアウトラインを作成することができます。いずれの場合も、アウトラインはオプティマイザからその入力データを導出します。
初期化パラメータCREATE_STORED_OUTLINESをtrueに設定すると、ストアド・アウトラインが自動的に作成されます。この機能をアクティブにすると、すべてのコンパイル済の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というカテゴリ名がアウトラインに割り当てられます。
ストアド・アウトラインの使用がアクティブ化されている場合は、常に問合せオプティマイザを使用します。アウトラインは、ヒントに依存します。ほとんどのヒントは、その効果を得るにはオプティマイザが必要です。
SQL文をコンパイルする際にストアド・アウトラインを使用するには、システム・パラメータUSE_STORED_OUTLINESをtrueに設定するか、カテゴリ名を指定します。USE_STORED_OUTLINESがtrueの場合は、defaultカテゴリのアウトラインが使用されます。USE_STORED_OUTLINESパラメータでカテゴリを指定すると、このパラメータを別のカテゴリ名に再設定するまで、またはUSE_STORED_OUTLINESをfalseに設定してアウトラインの使用を一時停止するまで、指定したカテゴリのアウトラインが使用されます。カテゴリ名が指定され、SQL文に一致するそのカテゴリのアウトラインが見つからなかった場合、データベースではdefaultカテゴリのアウトラインを検索します。
あるカテゴリのすべてのアウトラインではなく、特定のアウトラインを使用するには、ALTER OUTLINE文を実行して特定のアウトラインを有効にします。カテゴリの特定のアウトラインを除き、他のすべてのアウトラインを使用するには、ALTER OUTLINE文を使用して、使用するカテゴリで特定のアウトラインを無効にします。ALTER OUTLINE文では、ストアド・アウトラインの名前変更、異なるカテゴリへの再割当て、または再生成も可能です。
指定したアウトラインにより制御できるのは、アウトラインを保持する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%';
|
関連項目: ALTER OUTLINE文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 |
Oracle Databaseでデータ・ディクショナリに格納されているアウトラインの情報および関連するヒントのデータには、次のビューからアクセスできます。
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 Databaseでは、OL$表とOL$HINTS表のデータに基づいて、USER_OUTLINESビューとUSER_OUTLINE_HINTSビューを作成します。これらの表およびOL$NODES表は、outlnスキーマにあります。
outlnスキーマは、SYSTEM表領域にデータを格納します。アウトラインがSYSTEM表領域で使用する領域が多すぎる場合、それらのデータを移動できます。この目的を達成するには、別の表領域を作成して、その表領域にアウトライン表を移動します。
|
注意: CREATE_STORED_OUTLINESパラメータを有効化した状態で、実行中のアプリケーションにリテラルSQL文が多く含まれると、デフォルトのシステム表領域が使い果される可能性があります。この場合、DBMS_OUTLN.DROP_UNUSEDプロシージャを使用して、リテラルSQLアウトラインを削除してください。 |
アウトライン表を新しい表領域に移動するには、次の手順を実行します。
Oracle Data Pumpのエクスポート・ユーティリティを使用して、OL$、OL$HINTSおよびOL$NODES表をエクスポートします。
次の例では、outln_dirオブジェクトにマップするディレクトリ内のexp.dmpファイルにこれらの表をエクスポートします。
% expdp outln DIRECTORY=outln_dir DUMPFILE=exp.dmp TABLES=OL$,OL$HINTS,OL$NODES
Password: password
SQL*Plusを起動し、outlnユーザーとしてデータベースに接続します。次に例を示します。
SQL> CONNECT outln
Enter password: password
以前のOL$表、OL$HINTS表およびOL$NODES表を削除します。次に例を示します。
SQL> DROP TABLE OL$; SQL> DROP TABLE OL$HINTS; SQL> DROP TABLE OL$NODES;
表の新しい表領域を作成します。
次の例では、SYSTEMとして接続し、outln_tsという表領域を作成します。
SQL> CONNECT SYSTEM
Enter password: password
SQL> CREATE TABLESPACE outln_ts DATAFILE 'tspace.dat' SIZE 2M
2 DEFAULT STORAGE ( INITIAL 10K NEXT 20K MINEXTENTS 1 MAXEXTENTS 999
3 PCTINCREASE 10 ) ONLINE;
outlnスキーマのデフォルト表領域を変更します。
次の文は、デフォルトの表領域をoutln_tsに変更します。
SQL> ALTER USER OUTLN DEFAULT TABLESPACE outln_ts;
outln_ts表領域へのインポートを実行するには、次のタスクを実行します。
outlnユーザーに対するSYSTEM表領域の割当て制限を0Kに設定します。
表領域に対する無制限の権限または割当てを持つRESOURCEロールなど、UNLIMITED TABLESPACE権限およびすべてのロールを取り消します。
outln表領域の割当て制限を設定します。
データ・ポンプのインポート・ユーティリティを使用して、OL$表、OL$HINTS表およびOL$NODES表をインポートします。次に例を示します。
% impdp outln DIRECTORY=outln_dir DUMPFILE=exp.dmp TABLES=OL$,OL$HINTS,OL$NODES
Password: password
インポートが完了すると、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に変更します。
RBOCATカテゴリのアウトラインを使用するには、次の構文を入力します。
ALTER SESSION SET USE_STORED_OUTLINES = rbocat;
アプリケーションを実行します。
プラン・スタビリティの制限により、このアプリケーションのSQL文のアクセス・パスは変更できません。
|
注意: 手順2で問合せを実行しなかった場合、問合せオプティマイザへの移行後も問合せの古い動作を取得できます。この目標を達成するには、オプティマイザ・モードをRULEに変更し、問合せのアウトラインを作成してから、オプティマイザ・モードをCHOOSEに戻します。 |
問合せオプティマイザを使用する新しいOracle Databaseリリースにアップグレードする場合、オプティマイザに変更が生じるため、一部の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;
problemcatカテゴリのアウトラインを使用するには、次の構文を入力します。
ALTER SESSION SET USE_STORED_OUTLINES = problemcat;
本番データベースとは別個のテスト用データベースは、アップグレード後のオプティマイザの動作を検証するのに役立ちます。インポートおよびエクスポート機能を使用して、本番システムからテスト・システムに統計を移行できます。この方法を使用すると、テスト用データベースの表にデータを簡単に挿入できます。
アウトラインは、カテゴリ別にシステム間を移動できます。たとえば、problemcatカテゴリにアウトラインを作成した後に、問合せベースのエクスポート・オプションを使用してカテゴリ別にアウトラインをエクスポートできます。これは、ソース・データベースにあるすべてのアウトラインをエクスポートせずに、あるデータベースから別のデータベースに選択したアウトラインのみをエクスポートするための簡単で効率的な方法です。次の例のように、QUERYパラメータを指定してデータ・ポンプ・エクスポート・ユーティリティを使用します(行の継続文字が使用されている点に注意してください)。
% expdp outln DIRECTORY=outln_dir DUMPFILE=exp_file.dmp \
? TABLES=OL$,OL$HINTS,OL$NODES QUERY='WHERE CATEGORY="problemcat"'
Password: password
|
関連項目: データ・ポンプ・エクスポートおよびインポート・ユーティリティの使用方法の詳細は、『Oracle Databaseユーティリティ』を参照してください。 |
