| Oracle® Databaseパフォーマンス・チューニング・ガイド 11gリリース2 (11.2) B56312-06 |
|
 前 |
 次 |
この章では、SQLアクセス・アドバイザの使用方法について説明します。SQLアクセス・アドバイザは、パーティション化、マテリアライズド・ビュー、索引およびマテリアライズド・ビュー・ログによるデータベースのパフォーマンス向上についてアドバイスを提供するチューニング・ツールです。この章には次の項があります。
データ集中型の複雑な問合せの実行時に最適なパフォーマンスを実現できるようにデータベースをチューニングする場合、マテリアライズド・ビュー、パーティションおよび索引が必要不可欠です。SQLアクセス・アドバイザでは、特定のワークロードに関するマテリアライズド・ビュー、マテリアライズド・ビュー・ログ、パーティションおよび索引の適切なセットを推奨して、パフォーマンスの目標達成を支援します。SQLを最適化する際には、これらの構造を理解して使用することが重要です。これにより、データを取り出す際のパフォーマンスが大幅に向上します。ただし、このような利点を利用するにはそれなりの負担が伴います。これらのオブジェクトの作成やメンテナンスには時間がかかり、領域要件も重要になります。特に、パーティション化されていない実表のパーティション化は、慎重な計画を必要とする複雑な操作です。
SQLアクセス・アドバイザの索引の推奨事項には、ビットマップ索引、ファンクション索引およびBツリー索引が含まれます。ビットマップ索引を使用すると、多くのタイプの非定型問合せのレスポンス時間が短縮され、その他の索引付けの方法と比べて記憶域要件が軽減されます。ビットマップ索引は、一意またはほぼ一意のキーに索引を付ける方法で、データ・ウェアハウスで最も一般的に使用されています。SQLアクセス・アドバイザのマテリアライズド・ビューの推奨には、通常のリライトまたはテキストの完全一致リライトでの、高速リフレッシュおよび完全リフレッシュの可能なMVの推奨も含まれます。
SQLアクセス・アドバイザでは、TUNE_MVIEWプロシージャを使用することで、高速リフレッシュが可能で、通常のクエリー・リライトを活用できるマテリアライズド・ビューの最適化の方法も推奨されます。
また、SQLアクセス・アドバイザは、既存のパーティション化されていない実表のパーティション化によるパフォーマンスの改善を推奨する場合もあります。さらに、パーティション化された新しい索引およびマテリアライズド・ビューを推奨することもあります。パーティション化された新しい索引およびマテリアライズド・ビューの作成は、パーティション化されていない場合と同じですが、既存の実表のパーティション化は慎重に実行する必要があります。表に索引、ビュー、制約またはトリガーが定義されている場合は、特に慎重に行う必要があります。このタスクをオンラインで実行する場合、実表のパーティション化における問題の詳細は、「スクリプトにパーティション推奨事項が含まれる場合の特別な考慮事項」を参照してください。
SQLアクセス・アドバイザを実行するには、Oracle Enterprise ManagerからSQLアクセス・アドバイザ・ウィザードを使用するか(「アドバイザ・セントラル」ページからアクセス可能)、DBMS_ADVISORパッケージを起動します。DBMS_ADVISORパッケージは、任意のPL/SQLプログラムからコール可能な分析およびアドバイザ用のファンクションおよびプロシージャの集合です。
図18-1に、SQLアクセス・アドバイザが指定されたワークロードをユーザー定義の表またはSQLキャッシュから取得し、アクセス構造を推奨する方法を示します。ワークロードを指定しない場合、SQLアクセス・アドバイザは、仮想ワークロードを生成し、使用できます(ただし、CREATE DIMENSIONキーワードで定義されたディメンションがユーザー・スキーマに含まれていることが前提となります)。
Enterprise ManagerのSQLアクセス・アドバイザ・ウィザードまたはAPIを使用して、次の操作を実行できます。
収集した情報、ユーザー指定の情報、または仮想ワークロード情報に基づいてマテリアライズド・ビューと索引を推奨します。
表、索引およびマテリアライズド・ビューのパーティション化を推奨します。
推奨事項をマーク、更新および削除します。
また、SQLアクセス・アドバイザのAPIを使用して、次の操作を実行できます。
単一SQL文を使用してクイック・チューニングを実行します。
マテリアライズド・ビューを高速でリフレッシュする方法を示します。
マテリアライズド・ビューを変更して汎用的なクエリー・リライトを可能にする方法を示します。
推奨する際に、SQLアクセス・アドバイザは、ディメンション・レベルの列、JOIN KEY列およびファクト表のキー列における表および索引のカーディナリティに関する構造の統計情報に依存します。DBMS_STATSパッケージを使用して、正確な統計または見積り統計を収集できます。統計の収集は時間のかかる処理であり、完全な統計精度は必要ないため、通常は統計を見積ることをお薦めします。特定の表に関する統計情報を収集しない場合、その表を参照する問合せはワークロードで無効とマークされ、それらの問合せについては推奨事項は生成されません。また、既存のすべての索引とマテリアライズド・ビューを分析しておくこともお薦めします。DBMS_STATSパッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
SQLアクセス・アドバイザを使用するには、Enterprise Managerの「アドバイザ・セントラル」ページからウィザードを起動する方法が簡単です。DBMS_ADVISORパッケージからSQLアクセス・アドバイザを使用する場合は、この項で説明する基本コンポーネントおよびプロシージャのコール順序を参照してください。
この項では、一連の推奨事項を生成するための4つの手順について説明します。
アドバイザ・タスクはデータ・ディクショナリ内のコンテナで、インテリジェントなアドバイザ分析への入力内容や実行結果を格納します。結果を含めて推奨操作に関連する情報はすべて、タスクに格納されています。
SQLアクセス・アドバイザによる推奨を生成するには、次のいずれかを使用して最初にタスクを作成しておく必要があります。
タスクを自動的に作成するOracle Enterprise ManagerのウィザードまたはDBMS_ADVISOR.QUICK_TUNEプロシージャ
DBMS_ADVISOR.CREATE_TASKプロシージャ
タスクの処理内容を制御するには、DBMS_ADVISOR.SET_TASK_PARAMETERプロシージャを使用してタスクのパラメータを定義します。
ワークロードは1つ以上のSQL文と、各文を完全に説明する統計や属性で構成されています。全ワークロードには、対象となるビジネス・アプリケーションのすべてのSQL文が含まれます。部分ワークロードには、SQL文のサブセットが含まれます。これらの違いは、全ワークロードの場合、使用されていないマテリアライズド・ビューと索引を削除するようSQLアクセス・アドバイザが推奨する点にあります。
ワークロードなしでSQLアクセス・アドバイザを使用することはできません。ワークロードには様々な種類の文が含まれることがあります。SQLアクセス・アドバイザは特定の統計、ビジネスの重要性、またはその2つの組合せに応じてエントリにランクを付けます。これにより、アドバイザは最も重要なSQL文を先に処理できるようになります。
SQLアクセス・アドバイザでは、有効なワークロードに特定の属性が存在する必要があります。項目が欠落していてもアドバイザは分析を実行できますが、推奨事項の品質が低下する場合があります。たとえば、SQLアクセス・アドバイザでは、SQL問合せとこの問合せを実行したユーザーがワークロードに含まれている必要があります。その他の属性はオプションです。ただし、ワークロードにI/OおよびCPUデータも含む場合、SQLアクセス・アドバイザは、文の効率をより正確に評価できます。
データベースはワークロードをSQLチューニング・セットとして格納します。ワークロードにはDBMS_SQLTUNEパッケージを使用してアクセスし、多くのアドバイザ・タスク間で共有できます。ワークロードは独立しているため、DBMS_ADVISOR.ADD_STS_REFプロシージャを使用してタスクにリンクする必要があります。このリンクが確立された後は、すべてのアドバイザ・タスクからワークロードに対する依存性が削除されるまで、ワークロードを削除または変更できません。ユーザーが親アドバイザ・タスクを削除するか、DBMS_ADVISOR.DELETE_STS_REFプロシージャを使用してタスクからワークロード参照を手動で削除すると、ワークロード参照は削除されます。
ディメンションと制約を分析することで、スキーマから仮想ワークロードを作成できます。最良の結果を得るには、ワークロードをSQLチューニング・セットとして提供します。DBMS_SQLTUNEパッケージに提供されているヘルパー・ファンクションにより、一般的なワークロード・ソース(SQLキャッシュ、表に格納されたユーザー定義のワークロード、仮想ワークロードなど)からSQLチューニング・セットを作成できます。
推奨事項の生成時に、ワークロードにフィルタを適用して分析対象を制限できます。こうして制限することで、様々なワークロード・シナリオに基づいた推奨事項のセットを生成できます。SQLアクセス・アドバイザ・パラメータは、ワークロードの推奨プロセスとカスタマイズを制御します。これらのパラメータは、必要な推奨事項のタイプや推奨内容のネーミング規則など、プロセスの様々な側面を制御します。
これらのパラメータを設定するには、SET_TASK_PARAMETERプロシージャを使用します。パラメータは、タスクの存続期間は設定されたままであるという点において永続的です。SET_TASK_PARAMETERを使用してパラメータ値を設定した場合、このプロシージャをもう1回コールするまで、その値は変わりません。
タスクにワークロードをリンクし、適切なパラメータを設定したら、DBMS_ADVISOR.EXECUTE_TASKプロシージャを使用して推奨事項を生成できます。これらの推奨事項は、SQLアクセス・アドバイザ・リポジトリに格納されます。
推奨プロセスにより、複数の推奨事項が生成されます。各推奨事項には、1つ以上のアクションが指定されます。たとえば、1つの推奨事項には、複数のマテリアライズド・ビュー・ログの作成、マテリアライズド・ビューの作成、およびその分析による統計の収集が含まれます。
タスクの推奨事項は、簡単な提案から、一連の既存の実表のパーティション化や、索引、マテリアライズド・ビューおよびマテリアライズド・ビュー・ログなどの一連のデータベース・オブジェクトの実装を必要とする複雑なソリューションにまで及びます。アドバイザ・タスクが実行されると、SQLアクセス・アドバイザでは、収集されたデータとユーザー調整のタスク・パラメータが慎重に分析されます。分析の後、ユーザーが表示および実装できる構造化された推奨が形成されます。
推奨事項の生成の詳細は、「推奨事項の生成」を参照してください。
SQLアクセス・アドバイザからの推奨事項は、次のいずれかの方法で表示できます。
カタログ・ビューを使用
DBMS_ADVISOR.GET_TASK_SCRIPTプロシージャを使用してスクリプトを生成
SQLアクセス・アドバイザの処理が完了したら、Enterprise Managerで推奨事項を表示できます。推奨事項を表示するためにカタログ・ビューを使用する方法の詳細は、「推奨事項の表示」を参照してください。スクリプトの作成方法の詳細は、「SQLスクリプトの生成」を参照してください。
すべての推奨事項を受け入れる必要はありません。推奨事項のスクリプトに含める推奨事項をマークできます。ただし、実表のパーティション化が推奨された場合、一部の推奨事項はそれ以外の推奨事項に依存します。たとえば、索引の実表に対するパーティション推奨事項を実装しなければ、ローカル索引も実装できません。
最終手順では、推奨事項を実装し、問合せのパフォーマンスが向上したかどうかを検証します。
SQLアクセス・アドバイザによって生成されたすべての必要情報は、データベース・ディクショナリの一部であるアドバイザ・リポジトリに格納されます。リポジトリを使用する利点は、次のとおりです。
SQLアクセス・アドバイザの完全なワークロードが収集されます。
履歴データがサポートされます。
サーバーによって管理されます。
この項では、SQLアクセス・アドバイザに関する一般情報およびSQLアクセス・アドバイザを使用するために必要な手順について説明します。この項の内容は次のとおりです。
|
関連項目: Oracle Enterprise ManagerでSQLアクセス・アドバイザを使用する方法の詳細は、『Oracle Database 2日でパフォーマンス・チューニング・ガイド』を参照してください。 |
図18-2に、SQLアクセス・アドバイザの使用手順、SQLアクセス・アドバイザのすべてのパラメータの概要およびどの時点でそれらのパラメータを使用するのが適切かを示します。
SQLアクセス・アドバイザを管理または使用するには、ADVISOR権限が必要です。ワークロードを処理する際、SQLアクセス・アドバイザは表と列の参照を識別するために各文の検証を試みます。検証では、本来のユーザーが文を実行する場合と同様に各文が処理されます。
ユーザーに特定の表に対するSELECT権限がない場合、SQLアクセス・アドバイザはその表を参照している文をバイパスします。この動作によって、多くの文が分析から除外される可能性があります。SQLアクセス・アドバイザがワークロードのすべての文を除外した場合、ワークロードは無効になります。SQLアクセス・アドバイザは、次のメッセージを戻します。
QSM-00774, there are no SQL statements to process for task TASK_NAME
重要なワークロード問合せが欠落しないようにするには、現在のデータベース・ユーザーが、マテリアライズド・ビューの分析対象の表に対するSELECT権限を持っている必要があります。これらの表では、ロールを通じてこれらのSELECT権限を取得することはできません。
また、SQLチューニング・セット・オブジェクトのワークロードを作成および管理するには、ADMINISTER SQL TUNING SET権限も必要です。他のユーザーが所有するSQLチューニング・セットでアドバイザを実行するには、ADMINISTER ANY SQL TUNING SET権限が必要です。
この項では、タスクとテンプレートの設定に関する次の側面について説明します。
タスクでは、アドバイザが分析する内容と分析結果の格納場所を定義します。ユーザーは、各タスクが特化した任意の数のタスクを作成できます。これらすべてのタスクは同じアドバイザ・タスク・モデルに基づいており、同じリポジトリを共有します。
タスクを作成するには、CREATE_TASKプロシージャを使用します。この構文は、次のとおりです。
DBMS_ADVISOR.CREATE_TASK ( advisor_name IN VARCHAR2, task_id OUT NUMBER, task_name IN OUT VARCHAR2, task_desc IN VARCHAR2 := NULL, template IN VARCHAR2 := NULL, is_template IN VARCHAR2 := 'FALSE', how_created IN VARCHAR2 := NULL);
次に、このプロシージャの使用例を示します。
VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2(255);
EXECUTE :task_name := 'MYTASK';
EXECUTE DBMS_ADVISOR.CREATE_TASK ('SQL Access Advisor', :task_id, :task_name);
CREATE_TASKプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
タスクまたはワークロードの理想的な構成が判明した場合は、その構成をテンプレートとして保存しておき、今後のタスクおよびワークロードのベースとして使用できます。
タスクまたはワークロードをテンプレートとして設定すると、今後のタスクの作成で、インテリジェントな出発点またはテンプレートとして使用できます。テンプレートを設定することにより、チューニング分析の実行時間を短縮できます。また、このアプローチにより、ビジネス・オペレーションに適合したチューニング分析が可能になります。
テンプレートからタスクを作成するには、新しいタスクの作成時に使用するテンプレートを指定します。このとき、SQLアクセス・アドバイザは、新しく作成されたタスクにテンプレートからデータおよびパラメータ設定をコピーします。また、既存のタスクをテンプレートとして設定するには、タスクの作成時にテンプレート属性を設定するか、後でUPDATE_TASK_ATTRIBUTEプロシージャを使用します。
タスクをテンプレートとして使用するには、新しいタスクの作成時にタスクを使用することをSQLアクセス・アドバイザに通知します。このとき、SQLアクセス・アドバイザは、新しく作成されたタスクにテンプレートのデータおよびパラメータ設定をコピーします。または、コマンドラインまたはEnterprise Managerでテンプレートの属性を設定して、既存のタスクをテンプレートとして設定することもできます。
テンプレートの作成例は、次のとおりです。
MY_TEMPLATEと呼ばれるテンプレートを作成します。
VARIABLE template_id NUMBER;
VARIABLE template_name VARCHAR2(255);
EXECUTE :template_name := 'MY_TEMPLATE';
EXECUTE DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor',:template_id, -
:template_name, is_template => 'TRUE');
テンプレートのパラメータを設定します。たとえば、次の例では、推奨される索引とマテリアライズド・ビューのネーミング規則とデフォルトの表領域を設定します。
-- set naming conventions for recommended indexes/mvs EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - :template_name, 'INDEX_NAME_TEMPLATE', 'SH_IDX$$_<SEQ>'); EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - :template_name, 'MVIEW_NAME_TEMPLATE', 'SH_MV$$_<SEQ>'); -- set default tablespace for recommended indexes/mvs EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - :template_name, 'DEF_INDEX_TABLESPACE', 'SH_INDEXES'); EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - :template_name, 'DEF_MVIEW_TABLESPACE', 'SH_MVIEWS');
これで、このテンプレートは、次のようにタスクを作成するときの開始ポイントとして使用できます。
VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2(255);
EXECUTE :task_name := 'MYTASK';
EXECUTE DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', :task_id, -
:task_name, template=>'MY_TEMPLATE');
次の例では、事前定義済テンプレートSQLACCESS_WAREHOUSEを使用します。詳細は、表18-3を参照してください。
EXECUTE DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', -
:task_id, :task_name, template=>'SQLACCESS_WAREHOUSE');
SQLアクセス・アドバイザでは、異なるタイプのワークロードがサポートされます。この項では、ワークロードの管理に関する次の側面について説明します。
SQLアクセス・アドバイザの入力ワークロード・ソースは、SQLチューニング・セットです。SQLチューニング・セットの使用による重要な利点は、SQLチューニング・セットが個別のエンティティとして格納されるため、多くのアドバイザ・タスク間で簡単に共有できることです。SQLチューニング・セット・オブジェクトがアドバイザ・タスクによって参照された後は、すべてのアドバイザ・タスクからデータに対する依存性が削除されるまで、データを削除または変更できません。ワークロード参照が削除されるのは、親アドバイザ・タスクが削除された場合、またはアドバイザ・タスクからワークロード参照がユーザーによって手動で削除された場合です。
SQLアクセス・アドバイザのパフォーマンスは、実際の使用状況に基づいたワークロードが使用可能な場合に最も高くなります。複数のワークロードをSQLチューニング・セットとして保存することにより、長期およびデータベース・インスタンスの起動から停止までの全ライフサイクルにわたって、実際のデータ・ウェアハウスまたはトランザクション処理環境の様々な使用状況を参照できます。
SQLチューニング・セットのワークロードは、DBMS_SQLTUNEパッケージを使用して実装します。SQLチューニング・セットの作成および管理の詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
DBMS_ADVISORパッケージは、既存のSQLワークロード・オブジェクトをSQLチューニング・セットに移行するために、SQLワークロード・データをユーザーが指定したSQLチューニング・セットにコピーするプロシージャを提供します。このプロシージャを使用するには、SQLチューニング・セットの必要な権限とADVISOR権限が必要です。
この構文は、次のとおりです。
DBMS_ADVISOR.COPY_SQLWKLD_TO_STS ( workload_name IN VARCHAR2, sts_name IN VARCHAR2, import_mode IN VARCHAR2 := 'NEW');
次に使用例を示します。
EXECUTE DBMS_ADVISOR.COPY_SQLWKLD_TO_STS('MYWORKLOAD','MYSTS','NEW');
COPY_SQLWKLD_TO_STSプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
推奨プロセスを開始するには、あらかじめSQLチューニング・セットにタスクをリンクしておく必要があります。そのためには、ADD_STS_REFプロシージャを使用して、タスクとチューニング・セットをそれぞれの名前を使用してリンクします。このプロシージャは、アドバイザ・タスクとチューニング・セット間のリンクを確立します。リンクが定義された後は、削除または更新されないようにSQLチューニング・セットが保護されます。この構文は、次のとおりです。
DBMS_ADVISOR.ADD_STS_REF (task_name IN VARCHAR2, sts_owner IN VARCHAR2, sts_name IN VARCHAR2);
sts_ownerパラメータはNULLでもかまいませんが、その場合、STSの所有者は現在のユーザーであると想定されます。
次の例では、作成したタスクMYTASKと現在のユーザーのSQLチューニング・セットMYWORKLOADをリンクします。
EXECUTE DBMS_ADVISOR.ADD_STS_REF('MYTASK', null, 'MYWORKLOAD');
ADD_STS_REFプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
この項では、推奨事項の処理に関する次の側面について説明します。
SQLアクセス・アドバイザは複数の推奨事項を提供し、各推奨事項には1つ以上の個別のアクションが含まれます。一般に、各推奨事項は、1つ以上の問合せに対して利点をもたらします。利点を最大限に得るには、推奨事項の個々のアクションをすべてまとめて実装する必要があります。複数の推奨事項でアクションを共有できます。
たとえば、CREATE INDEX文は複数の問合せに対して利点をもたらしますが、それらの問合せの一部は、他のCREATE MATERIALIZED VIEW文から利点を得ることがあります。その場合、アドバイザは2つの推奨事項を生成します。最適に実行するのに、索引のみを必要とする問合せセット用と、索引とマテリアライズド・ビューの両方を必要とする問合せセット用です。
パーティションの推奨事項は、特殊なタイプの推奨です。SQLアクセス・アドバイザは、指定された実表のパーティション化によってワークロードのパフォーマンスが向上すると判断した場合、その実表を参照する問合せが含まれるすべての推奨事項にパーティション・アクションを追加します。この方法により、索引およびマテリアライズド・ビューの推奨事項が、適切にパーティション化された表に実装されます。
アドバイザで推奨事項を生成するには、最初にSET_TASK_PARAMETERプロシージャを使用してタスクのパラメータを定義する必要があります。パラメータの定義がない場合、データベースはデフォルトを使用します。
タスクのパラメータを設定するには、SET_TASK_PARAMETERプロシージャを使用します。この構文は、次のとおりです。
DBMS_ADVISOR.SET_TASK_PARAMETER ( task_name IN VARCHAR2, parameter IN VARCHAR2, value IN [VARCHAR2 | NUMBER]);
タスク・パラメータは多数存在するため、関連するパラメータを識別しやすいように、これらのカテゴリ分けした表を表18-1に示します。ワークロード・フィルタのタスク・パラメータはすべて非推奨になっていることに注意してください。
表18-1 アドバイザのタスク・パラメータのタイプとその使用方法
| ワークロード・フィルタ | タスク構成 | スキーマ属性 | 推奨オプション |
|---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
|
|
|
|
|
次の例では、タスクMYTASKの記憶域変更が100MBに設定されています。これは、推奨事項の追加領域が100MBであることを示しています。値が0の場合は、追加領域を割り当てることができません。マイナス値は、指定した量だけアドバイザが現在の領域使用量を削減する必要があることを示しています。
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER('MYTASK','STORAGE_CHANGE', 100000000);
次の例では、SH.SALESおよびSH.CUSTOMERS表を構成していないすべての問合せを、フィルタで除外するようにVALID_TABLE_LISTパラメータを設定します。
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - 'MYTASK', 'VALID_TABLE_LIST', 'SH.SALES, SH.CUSTOMERS');
SET_TASK_PARAMETERプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
SQLアクセス・アドバイザは、問題解決モードと評価モードの2つのモードで動作します。SQLアクセス・アドバイザは、デフォルトでは、アクセス方法の問題を解決するために、索引構造、パーティション、マテリアライズド・ビューおよびマテリアライズド・ビュー・ログの拡張を求めます。たとえば、問題解決モードで実行すると、新しい索引の作成やマテリアライズド・ビュー・ログへの新しい列の追加などが推奨されます。
評価のみで実行すると、SQLアクセス・アドバイザは、指定されたワークロードによって使用されるアクセスの構造についてのみコメントします。評価のみの実行では、索引の保持やマテリアライズド・ビューの保持などの推奨のみとなります。評価モードでは、ワークロードがどの索引およびマテリアライズド・ビューを使用するのか正確に知ることができます。SQLアクセス・アドバイザは、既存の実表のパーティション化によるパフォーマンスへの影響は評価しません。
SQLアクセス・アドバイザでは、分析操作中に中間結果を表示できるようになりました。以前は、分析操作の結果は、処理が完了するか、ユーザーが割り込むまで使用できませんでした。現在は、SQLアクセス・アドバイザ・タスクが実行中であっても、対応する推奨事項およびアクション表の結果にアクセスできます。その利点は、長時間続くタスクで、長時間にわたる実行が完了するのを待たずにタスクに割り込むことによって、最新の結果を受け取ることができるようになったことです。
最新の推奨事項セットを受け取るには、タスクに割り込む必要があります。この割込みによって、SQLアクセス・アドバイザは処理を停止し、タスクはINTERRUPTEDとマークされます。その時点で、推奨事項の属性を更新してスクリプトを生成できます。または、SQLアクセス・アドバイザに推奨プロセスを完了させることもできます。
中間結果は、その時点までのワークロードの内容に対する推奨事項であることに注意してください。推奨事項がワークロード全体の影響を受けやすいことが重要である場合は、タスクの実行を正常に完了させることをお薦めします。また、推奨プロセスの初期段階でアドバイザによって作成される推奨事項には、実表のパーティション化に関する推奨は含まれません。パーティション化分析では、パーティション化が有効かどうかを決定する前に、ワークロードの大部分を処理する必要があります。したがって、SQLアクセス・アドバイザが利点を検出した場合は、より遅い段階での中間結果に実表のパーティション化に関する推奨事項が含まれます。
推奨事項を生成するには、タスク名を指定してEXECUTE_TASKプロシージャを使用します。プロシージャが終了した後、DBA_ADVISOR_LOG表をチェックし、実際の実行ステータスや、生成された推奨事項およびアクションの数を確認できます。{DBA, USER}_ADVISOR_RECOMMENDATIONSでは、タスク名で推奨項目の問合せができます。{DBA, USER}_ADVISOR_ACTIONSでは、これらの推奨事項のアクションをタスクごとに参照できます。
指定したタスクがSQLアクセス・アドバイザによって分析または評価されます。タスクの実行は同期処理であるため、処理が完了するか、ユーザーによる割込みが検出されるまで、制御はユーザーに戻されません。タスクの実行が戻されると、実際の実行ステータスについてDBA_ADVISOR_LOG表をチェックできます。
EXECUTE_TASKを実行すると、推奨事項が生成されます。この場合、推奨事項は、マテリアライズド・ビュー・ログやマテリアライズド・ビューの作成などの1つ以上のアクションから構成されます。この構文は、次のとおりです。
DBMS_ADVISOR.EXECUTE_TASK (task_name IN VARCHAR2);
次に、このプロシージャの使用例を示します。
EXECUTE DBMS_ADVISOR.EXECUTE_TASK('MYTASK');
EXECUTE_TASKプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
SQLアクセス・アドバイザによって生成された各推奨事項は、(DBA, USER)_ADVISOR_RECOMMENDATIONSなどのいくつかのカタログ・ビューを使用して表示できます。ただし、GET_TASK_SCRIPTプロシージャを使用するか、またはEnterprise ManagerでSQLアクセス・アドバイザを使用する方が簡単です。この場合、推奨事項がグラフィック表示され、推奨事項の利点を得るSQL文を簡単に参照するためのハイパーリンクが用意されています。SQLアクセス・アドバイザによって生成された各推奨事項は、その利点を得るSQL文にリンクされています。
次の例は、アドバイザの実行によって生成される推奨事項(rec_id)とそのランクおよび全利点を示します。ランクとは、推奨事項がサポートする問合せの重要性の尺度です。利点とは、推奨事項を使用したすべての問合せの実行コスト(オプティマイザのコストの観点から)における改善結果です。
VARIABLE workload_name VARCHAR2(255);
VARIABLE task_name VARCHAR2(255);
EXECUTE :task_name := 'MYTASK';
EXECUTE :workload_name := 'MYWORKLOAD';
SELECT REC_ID, RANK, BENEFIT
FROM USER_ADVISOR_RECOMMENDATIONS WHERE TASK_NAME = :task_name;
REC_ID RANK BENEFIT
---------- ---------- ----------
1 2 2754
2 3 1222
3 1 5499
4 4 594
推奨事項が役立つ問合せを確認するには、DBA_*およびUSER_ADVISOR_SQLA_WK_STMTSビューを使用します。事前コストと事後コストの数は、それぞれ推奨されたアクセス構造の変更がある場合とない場合とで見積られたオプティマイザのコスト(EXPLAIN PLANを参照)に関するものです。各問合せの推奨事項を表示するには、次の文を発行します。
SELECT sql_id, rec_id, precost, postcost,
(precost-postcost)*100/precost AS percent_benefit
FROM USER_ADVISOR_SQLA_WK_STMTS
WHERE TASK_NAME = :task_name AND workload_name = :workload_name;
SQL_ID REC_ID PRECOST POSTCOST PERCENT_BENEFIT
---------- ---------- ---------- ---------- ---------------
121 1 3003 249 91.7082917
122 2 1404 182 87.037037
123 3 5503 4 99.9273124
124 4 730 136 81.369863
各推奨事項は1つ以上のアクションから構成されます。推奨事項の利点を得るには、これらのアクションをまとめて実装する必要があります。SQLアクセス・アドバイザでは、次のタイプのアクションを生成します。
PARTITION BASE TABLE
CREATE|DROP|RETAIN MATERIALIZED VIEW
CREATE|ALTER|RETAIN MATERIALIZED VIEW LOG
CREATE|DROP|RETAIN INDEX
GATHER STATS
PARTITION BASE TABLEアクションは、既存のパーティション化されていない実表をパーティション化します。CREATEアクションは、新しいアクセス構造に対応します。RETAIN推奨事項は、既存のアクセス構造を維持する必要があることを示します。DROP推奨事項が生成されるのは、WORKLOAD_SCOPEパラメータがFULLに設定されている場合のみです。GATHER STATSアクションは、DBMS_STATSプロシージャへのコールを生成し、新しく生成されたアクセス構造に関する統計を収集します。複数の推奨事項が同じアクションを参照する場合があります。ただし、推奨事項のスクリプトの生成では、各アクションは1回のみ表示されます。
次の例では、この一連の推奨事項について個別アクションの数を確認できます。
SELECT 'Action Count', COUNT(DISTINCT action_id) cnt
FROM USER_ADVISOR_ACTIONS WHERE task_name = :task_name;
'ACTIONCOUNT CNT
------------ --------
Action Count 20
-- see the actions for each recommendations
SELECT rec_id, action_id, SUBSTR(command,1,30) AS command
FROM user_advisor_actions WHERE task_name = :task_name
ORDER BY rec_id, action_id;
REC_ID ACTION_ID COMMAND
---------- ---------- ------------------------------
1 5 CREATE MATERIALIZED VIEW LOG
1 6 ALTER MATERIALIZED VIEW LOG
1 7 CREATE MATERIALIZED VIEW LOG
1 8 ALTER MATERIALIZED VIEW LOG
1 9 CREATE MATERIALIZED VIEW LOG
1 10 ALTER MATERIALIZED VIEW LOG
1 11 CREATE MATERIALIZED VIEW
1 12 GATHER TABLE STATISTICS
1 19 CREATE INDEX
1 20 GATHER INDEX STATISTICS
2 5 CREATE MATERIALIZED VIEW LOG
2 6 ALTER MATERIALIZED VIEW LOG
2 9 CREATE MATERIALIZED VIEW LOG
...
各アクションには、アクセス構造のプロパティに関連する複数の属性があります。各アクセス構造の名前と表領域は、必要に応じてattr1およびattr2にそれぞれ格納されます。新しい各アクセス構造が占有する領域は、num_attr1にあります。その他すべての属性は、アクションごとに異なります。
表18-2に、SQLアクセス・アドバイザのアクション情報とそれに対応するDBA_ADVISOR_ACTIONS内の列を示します。表内の「MV」は、マテリアライズド・ビューを意味します。
表18-2 SQLアクセス・アドバイザのアクション属性
| ATTR1 | ATTR2 | ATTR3 | ATTR4 | ATTR5 | ATTR6 | NUM_ATTR1 | |
|---|---|---|---|---|---|---|---|
|
|
索引名 |
索引表領域 |
ターゲット表 |
|
索引列リスト/式 |
未使用 |
索引の記憶域のサイズ(バイト単位) |
|
|
MV名 |
MV表領域 |
|
|
SQL |
未使用 |
MVの記憶域のサイズ(バイト単位) |
|
|
ターゲット表名 |
MVログ表領域 |
|
|
表列リスト |
パーティション副次句 |
未使用 |
|
|
等価名 |
チェックサム値 |
未使用 |
未使用 |
ソースSQL文 |
等価SQL文 |
未使用 |
|
|
索引名 |
未使用 |
未使用 |
未使用 |
索引列 |
未使用 |
索引の記憶域のサイズ(バイト単位) |
|
|
MV名 |
未使用 |
未使用 |
未使用 |
未使用 |
未使用 |
MVの記憶域のサイズ(バイト単位) |
|
|
ターゲット表名 |
未使用 |
未使用 |
未使用 |
未使用 |
未使用 |
未使用 |
|
|
表名 |
|
パーティション化のパーティション・キー(列名または列名のリスト) |
サブパーティション化のパーティション・キー(列名または列名のリスト) |
SQL |
SQL |
未使用 |
|
|
索引名 |
|
パーティション化のパーティション・キー(列名のリスト) |
未使用 |
SQL |
未使用 |
未使用 |
|
|
MV名 |
|
パーティション化のパーティション・キー(列名または列名のリスト) |
サブパーティション化のパーティション・キー(列名または列名のリスト) |
SQL |
SQL |
未使用 |
|
|
索引名 |
未使用 |
ターゲット表 |
|
索引列 |
未使用 |
索引の記憶域のサイズ(バイト単位) |
|
|
MV名 |
未使用 |
|
未使用 |
SQL |
未使用 |
MVの記憶域のサイズ(バイト単位) |
|
|
ターゲット表名 |
未使用 |
未使用 |
未使用 |
未使用 |
未使用 |
未使用 |
次のPL/SQLプロシージャは、推奨事項の属性の一部を出力できます。
CONNECT SH/SH;
CREATE OR REPLACE PROCEDURE show_recm (in_task_name IN VARCHAR2) IS
CURSOR curs IS
SELECT DISTINCT action_id, command, attr1, attr2, attr3, attr4
FROM user_advisor_actions
WHERE task_name = in_task_name
ORDER BY action_id;
v_action number;
v_command VARCHAR2(32);
v_attr1 VARCHAR2(4000);
v_attr2 VARCHAR2(4000);
v_attr3 VARCHAR2(4000);
v_attr4 VARCHAR2(4000);
v_attr5 VARCHAR2(4000);
BEGIN
OPEN curs;
DBMS_OUTPUT.PUT_LINE('=========================================');
DBMS_OUTPUT.PUT_LINE('Task_name = ' || in_task_name);
LOOP
FETCH curs INTO
v_action, v_command, v_attr1, v_attr2, v_attr3, v_attr4 ;
EXIT when curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Action ID: ' || v_action);
DBMS_OUTPUT.PUT_LINE('Command : ' || v_command);
DBMS_OUTPUT.PUT_LINE('Attr1 (name) : ' || SUBSTR(v_attr1,1,30));
DBMS_OUTPUT.PUT_LINE('Attr2 (tablespace): ' || SUBSTR(v_attr2,1,30));
DBMS_OUTPUT.PUT_LINE('Attr3 : ' || SUBSTR(v_attr3,1,30));
DBMS_OUTPUT.PUT_LINE('Attr4 : ' || v_attr4);
DBMS_OUTPUT.PUT_LINE('Attr5 : ' || v_attr5);
DBMS_OUTPUT.PUT_LINE('----------------------------------------');
END LOOP;
CLOSE curs;
DBMS_OUTPUT.PUT_LINE('=========END RECOMMENDATIONS============');
END show_recm;
/
-- see what the actions are using sample procedure
set serveroutput on size 99999
EXECUTE show_recm(:task_name);
A fragment of a sample output from this procedure is as follows:
Task_name = MYTASK
Action ID: 1
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "SH"."CUSTOMERS"
Attr2 (tablespace):
Attr3 : ROWID, SEQUENCE
Attr4 : INCLUDING NEW VALUES
Attr5 :
----------------------------------------
..
----------------------------------------
Action ID: 15
Command : CREATE MATERIALIZED VIEW
Attr1 (name) : "SH"."SH_MV$$_0004"
Attr2 (tablespace): "SH_MVIEWS"
Attr3 : REFRESH FAST WITH ROWID
Attr4 : ENABLE QUERY REWRITE
Attr5 :
----------------------------------------
..
----------------------------------------
Action ID: 19
Command : CREATE INDEX
Attr1 (name) : "SH"."SH_IDX$$_0013"
Attr2 (tablespace): "SH_INDEXES"
Attr3 : "SH"."SH_MV$$_0002"
Attr4 : BITMAP
Attr5 :
Attr5およびAttr6の詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
EXECUTE_TASKプロシージャを使用してSQLアクセス・アドバイザの推奨事項を生成する際に、処理に時間がかかりすぎる場合は、CANCEL_TASKプロシージャをコールしてその推奨プロセスのtask_nameを渡すと、処理を停止できます。CANCEL_TASKを使用した場合、SQLアクセス・アドバイザの推奨事項はありません。そのため、推奨事項が必要な場合は、INTERRUPT_TASKプロシージャの使用を検討してください。
INTERRUPT_TASKプロシージャを使用すると、アドバイザ操作は正常終了に達したものとして終了されます。これにより、ユーザーには、割込みポイントまでに形成された推奨事項が表示されます。
割り込まれたタスクは再開できません。この構文は、次のとおりです。
DBMS_ADVISOR.INTERRUPT_TASK (task_name IN VARCHAR2);
次に、このプロシージャの使用例を示します。
EXECUTE DBMS_ADVISOR.INTERRUPT_TASK ('MY_TASK');
CANCEL_TASKプロシージャを使用すると、現在実行中の操作が終了します。この場合、アドバイザ操作がこのリクエストに応答するのに数秒かかることがあります。すべてのアドバイザ・タスクのプロシージャは同期操作であるため、操作を取り消すには、別のデータベース・セッションを使用する必要があります。
取消コマンドは、取り消された操作の開始前の状態にタスクを効率的にリストアします。このため、取り消されたタスクまたはデータ・オブジェクトは再開できません(ただし、DBMS_ADVISOR.RESET_TASKを使用してからタスクを再実行することで、タスクをリセットできます)。この構文は、次のとおりです。
DBMS_ADVISOR.CANCEL_TASK (task_name IN VARCHAR2);
次に、このプロシージャの使用例を示します。
EXECUTE DBMS_ADVISOR.CANCEL_TASK('MYTASK');
CANCEL_TASKプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
デフォルトでは、SQLアクセス・アドバイザのすべての推奨事項は実装可能な状態にありますが、MARK_RECOMMENDATIONプロシージャを使用すると、選択した推奨事項をスキップまたは除外できます。MARK_RECOMMENDATIONでは、ユーザーがREJECTまたはIGNORE設定を使用して推奨事項に注釈を付けることができます。実装プロシージャを生成する際に、GET_TASK_SCRIPTは、注釈の付けられた推奨事項をスキップします。この構文は、次のとおりです。
DBMS_ADVISOR.MARK_RECOMMENDATION ( task_name IN VARCHAR2 id IN NUMBER, action IN VARCHAR2);
次の例では、IDが2の推奨事項をREJECTとマークします。この推奨事項およびこれに依存する推奨事項は、スクリプトに表示されません。
EXECUTE DBMS_ADVISOR.MARK_RECOMMENDATION('MYTASK', 2, 'REJECT');
アドバイザにより、パーティション化されていない1つ以上の実表のパーティション化が推奨された場合、その推奨事項をスキップする前に慎重に検討してください。表のパーティション化スキームを変更すると、その表に定義されたすべての問合せ、索引およびマテリアライズド・ビューのコストに影響を及ぼします。このため、パーティション化の推奨事項をスキップした場合は、その表に対してアドバイザが行ったその他の推奨も最適ではありません。パーティション化を含めずにワークロードの推奨事項を確認するには、ANALYSIS_SCOPEパラメータを変更してパーティション化の推奨を除外し、アドバイザ・タスクをリセットして、再度タスクを実行します。
MARK_RECOMMENDATIONSプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
UPDATE_REC_ATTRIBUTESプロシージャを使用して、SQLアクセス・アドバイザでは、分析操作時に索引やマテリアライズド・ビューなどの新しいオブジェクトに名前が付けられ、所有者が割り当てられます。ただし、必ずしも適切な名前が選択されるとはかぎらないため、新しいオブジェクトの所有者、名前および表領域の値を手動で設定できます。既存のデータベース・オブジェクトを参照している推奨事項の場合、所有者と名前の値は変更できません。この構文は、次のとおりです。
DBMS_ADVISOR.UPDATE_REC_ATTRIBUTES ( task_name IN VARCHAR2 rec_id IN NUMBER, action_id IN NUMBER, attribute_name IN VARCHAR2, value IN VARCHAR2);
attribute_nameパラメータには、次の値を使用できます。
OWNER
推奨オブジェクトの所有者名を指定します。
NAME
推奨オブジェクトの名前を指定します。
TABLESPACE
推奨オブジェクトの表領域を指定します。
次の例では、推奨事項ID 1、アクションID 1のTABLESPACE属性をSH_MVIEWSに変更します。
EXECUTE DBMS_ADVISOR.UPDATE_REC_ATTRIBUTES('MYTASK', 1, 1, -
'TABLESPACE', 'SH_MVIEWS');
UPDATE_REC_ATTRIBUTESプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
推奨事項を表示するためにメタデータを問い合せるもう1つの方法は、GET_TASK_SCRIPTプロシージャを使用して推奨事項のSQL文のスクリプトを作成する方法です。この結果生成されるスクリプトは実行可能SQLファイルで、DROP、CREATEおよびALTER文を含めることができます。新しいオブジェクトの場合、マテリアライズド・ビューの名前、マテリアライズド・ビュー・ログおよび索引は、ユーザー指定の名前テンプレートを使用して自動的に生成されます。生成されたSQLスクリプトは、実行する前に見直す必要があります。
ネーミング規則(MVIEW_NAME_TEMPLATEおよびINDEX_NAME_TEMPLATE)、これらの新しいオブジェクトの所有者(DEF_INDEX_OWNERおよびDEF_MVIEW_OWNER)、表領域(DEF_MVIEW_TABLESPACEおよびDEF_INDEX_TABLESPACE)を制御する複数のタスク・パラメータがあります。
次の例は、推奨事項のスクリプトが含まれるCLOBを生成する方法を示します。
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('MYTASK'),
'ADVISOR_RESULTS', 'advscript.sql');
スクリプトをファイルに保存するには、CREATE_FILEプロシージャにスクリプトの格納先を示すディレクトリ・パスを指定する必要があります。また、このディレクトリには読取りおよび書込み権限を付与する必要があります。次の例は、CLOBアドバイザ・スクリプトをファイルに保存する方法を示します。
-- create a directory and grant permissions to read/write to it CONNECT SH/SH; CREATE DIRECTORY ADVISOR_RESULTS AS '/mydir'; GRANT READ ON DIRECTORY ADVISOR_RESULTS TO PUBLIC; GRANT WRITE ON DIRECTORY ADVISOR_RESULTS TO PUBLIC;
次の例は、このスクリプトによって生成されたスクリプトのフラグメントです。また、このスクリプトには、推奨されたアクセス構造に関する統計を収集するPL/SQLコールが含まれ、最後に推奨事項にIMPLEMENTEDとマークします。
Rem Access Advisor V11.1.0.0.0 - Production
Rem
Rem Username: SH
Rem Task: MYTASK
Rem Execution date: 15/08/2006 11:35
Rem
set feedback 1
set linesize 80
set trimspool on
set tab off
set pagesize 60
whenever sqlerror CONTINUE
CREATE MATERIALIZED VIEW LOG ON "SH"."PRODUCTS"
WITH ROWID, SEQUENCE("PROD_ID","PROD_SUBCATEGORY")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."PRODUCTS"
ADD ROWID, SEQUENCE("PROD_ID","PROD_SUBCATEGORY")
INCLUDING NEW VALUES;
..
CREATE MATERIALIZED VIEW "SH"."MV$$_00510002"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH.CUSTOMERS.CUST_STATE_PROVINCE C1, COUNT(*) M1 FROM
SH.CUSTOMERS WHERE (SH.CUSTOMERS.CUST_STATE_PROVINCE = 'CA') GROUP
BY SH.CUSTOMERS.CUST_STATE_PROVINCE;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('"SH"', '"MV$$_00510002"', NULL,
DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/
..
CREATE BITMAP INDEX "SH"."MV$$_00510004_IDX$$_00510013"
ON "SH"."MV$$_00510004" ("C4");
whenever sqlerror EXIT SQL.SQLCODE
BEGIN
DBMS_ADVISOR.MARK_RECOMMENDATION('"MYTASK"',1,'IMPLEMENTED');
DBMS_ADVISOR.MARK_RECOMMENDATION('"MYTASK"',2,'IMPLEMENTED');
DBMS_ADVISOR.MARK_RECOMMENDATION('"MYTASK"',3,'IMPLEMENTED');
DBMS_ADVISOR.MARK_RECOMMENDATION('"MYTASK"',4,'IMPLEMENTED');
END;
/
|
関連項目: CREATE DIRECTORY構文の詳細は『Oracle Database SQL言語リファレンス』を、GET_TASK_SCRIPTプロシージャの詳細は『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 |
アドバイザは、問合せパフォーマンスを向上させるために既存のパーティション化されていない実表のパーティション化を推奨することがあります。アドバイザの実装スクリプトにパーティション推奨事項が含まれる場合は、次の問題に注意してください。
既存の表のパーティション化は複雑で広範な作業であるため、新しい索引やマテリアライズド・ビューを実装するよりも非常に時間がかかる場合があります。この推奨事項の実装には、時間を十分に取っておく必要があります。
索引およびマテリアライズド・ビューの推奨事項は、索引やビューを削除すれば簡単に元に戻せますが、表をパーティション化すると簡単には元の状態に戻せません。このため、パーティション化の推奨が含まれるスクリプトを実行する前に、データベースのバックアップが必要です。
実表を再パーティション化する際に、SQLアクセス・アドバイザのスクリプトにより、元の表の一時コピーが作成されますが、その一時コピーは元の表と同じ容量の領域を使用します。そのため、再パーティション化プロセスには、再パーティション化する最も大きな表のコピーをもう1つ作成できる程度に十分な空きディスク領域が必要です。実装スクリプトを実行する前に、使用可能な領域を確保しておく必要があります。
パーティション実装スクリプトは、索引、マテリアライズド・ビューおよび制約などの依存オブジェクトの移行を試みます。しかし、一部のオブジェクトは、自動的に移行できません。たとえば、再パーティション化された実表に対するPL/SQLストアド・プロシージャの定義は、通常では無効になり、再コンパイルが必要です。
アドバイザのパーティション化の推奨を実装しない場合、同じスクリプトの同じ表に対するその他のすべての推奨事項(CREATE INDEXおよびCREATE MATERIALIZED VIEWなどの推奨)は、パーティション化の推奨に依存していることに注意してください。正確な推奨を得るには、パーティション化の推奨をスクリプトから単純に削除しないでください。かわりに、パーティション化を無効にしてアドバイザを再実行します(たとえば、ANALYSIS_SCOPEパラメータの値にTABLEキーワードを含めないなど)。
|
関連項目: CREATE DIRECTORY構文の詳細は『Oracle Database SQL言語リファレンス』を、GET_TASK_SCRIPTプロシージャの詳細は『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 |
RESET_TASKプロシージャは、タスクを初期の開始ポイントにリセットします。これにより、すべての推奨事項と中間データがタスクから削除されます。実際のタスクのステータスは、INITIALに設定されます。この構文は、次のとおりです。
DBMS_ADVISOR.RESET_TASK (task_name IN VARCHAR2);
次に、このプロシージャの使用例を示します。
EXECUTE DBMS_ADVISOR.RESET_TASK('MYTASK');
RESET_TASKプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
単一のSQL文をチューニングするには、task_nameとSQL文を入力として受け入れるQUICK_TUNEプロシージャを使用します。このプロシージャは、タスクとワークロードを作成し、そのタスクを実行します。QUICK_TUNEを使用しても、結果に違いはありません。結果はEXECUTE_TASKを使用する場合とまったく同じですが、チューニング対象のSQL文が1つのみである場合、この方法を使用した方が簡単です。この構文は、次のとおりです。
DBMS_ADVISOR.QUICK_TUNE ( advisor_name IN VARCHAR2, task_name IN VARCHAR2, attr1 IN CLOB, attr2 IN VARCHAR2 := NULL, attr3 IN NUMBER := NULL, task_or_template IN VARCHAR2 := NULL);
次の例は、単一のSQL文をクイック・チューニングする方法を示しています。
VARIABLE task_name VARCHAR2(255);
VARIABLE sql_stmt VARCHAR2(4000);
EXECUTE :sql_stmt := 'SELECT COUNT(*) FROM customers
WHERE cust_state_province =''CA''';
EXECUTE :task_name := 'MY_QUICKTUNE_TASK';
EXECUTE DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR,
:task_name, :sql_stmt);
QUICK_TUNEプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
推奨事項を生成すると、常にタスクが作成されます。これらのタスクを管理しなければ、やがてタスク数が増加して記憶域が占領されます。また、一部のタスクを保持する場合は、誤って削除されないように保護します。タスクの管理では、次のような操作を実行できます。
UPDATE_TASK_ATTRIBUTESプロシージャを使用すると、次の操作ができます。
タスク名の変更
タスクの説明の付加
タスクの読取り専用(変更不可)設定
他のタスクを定義できるようにタスクをテンプレート化
タスクやタスク・テンプレートの様々な属性の変更
この構文は、次のとおりです。
DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES ( task_name IN VARCHAR2 new_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL, read_only IN VARCHAR2 := NULL, is_template IN VARCHAR2 := NULL, how_created IN VARCHAR2 := NULL);
次の例では、タスクMYTASKの名前がTUNING1に更新されます。
EXECUTE DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES('MYTASK', 'TUNING1');
次の例では、タスクTUNING1が読取り専用に設定されます。
EXECUTE DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES('TUNING1', read_only => 'TRUE');
次の例では、タスクMYTASKがテンプレートとして設定されます。
EXECUTE DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES('TUNING1', is_template=>'TRUE');
UPDATE_TASK_ATTRIBUTESプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
DELETE_TASKプロシージャでは、アドバイザの既存のタスクがリポジトリから削除されます。この構文は、次のとおりです。
DBMS_ADVISOR.DELETE_TASK (task_name IN VARCHAR2);
次に、このプロシージャの使用例を示します。
EXECUTE DBMS_ADVISOR.DELETE_TASK('MYTASK');
DELETE_TASKプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
タスクまたはワークロード・オブジェクトを作成すると、DAYS_TO_EXPIREパラメータが30に設定されます。この値は、タスクまたはオブジェクトが自動的に削除されるまでの日数を示します。タスクまたはワークロードを無期限に保存し続けるには、DAYS_TO_EXPIREパラメータをADVISOR_UNLIMITEDに設定します。
SQLアクセス・アドバイザでは、表18-3に示されている定数を使用できます。
表18-3 SQLアクセス・アドバイザの定数
| 定数 | 説明 |
|---|---|
ADVISOR_ALL |
すべての可能な値を示す値。文字列パラメータでは、この値はワイルドカード文字である |
ADVISOR_CURRENT |
現在の時刻またはアクティブな要素セットを示します。通常、これは時間パラメータで使用します。 |
ADVISOR_DEFAULT |
デフォルト値を示します。通常、タスクまたはワークロード・パラメータの設定時に使用します。 |
ADVISOR_UNLIMITED |
無制限な数値を表す値です。 |
ADVISOR_UNUSED |
未使用のエンティティを表す値。パラメータが |
SQLACCESS_GENERAL |
SQLアクセスの汎用タスク・テンプレートのデフォルト名を指定します。このテンプレートは、 |
SQLACCESS_OLTP |
SQLアクセスのOLTPタスク・テンプレートのデフォルト名を指定します。このテンプレートは、 |
SQLACCESS_WAREHOUSE |
SQLアクセスのウェアハウス・タスク・テンプレートのデフォルト名を指定します。このテンプレートは、 |
SQLACCESS_ADVISOR |
SQLアクセス・アドバイザの正式名称を格納します。プロシージャでアドバイザ名を引数として指定する必要がある場合、この名前を指定できます。 |
この項では、SQLアクセス・アドバイザの一般的な使用例について説明します。Oracle Databaseでは、この章の例を含むaadvdemo.sqlというスクリプトが提供されています。
次の例では、ユーザー定義表SH.USER_WORKLOADからワークロードがインポートされます。次に、MYTASKというタスクが作成され、記憶域割当量が100MBに設定されて、タスクが実行されます。PL/SQLプロシージャによって、推奨事項が表示されます。最後に、推奨事項を実装するためのスクリプトが生成されます。
SQL文を使用してUSER_WORKLOAD表をロードします。
CONNECT SH/SH;
-- aggregation with selection
INSERT INTO user_workload (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2,
'SELECT t.week_ending_day, p.prod_subcategory,
SUM(s.amount_sold) AS dollars, s.channel_id, s.promo_id
FROM sales s, times t, products p WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id AND s.prod_id > 10 AND s.prod_id < 50
GROUP BY t.week_ending_day, p.prod_subcategory,
s.channel_id, s.promo_id')
/
-- aggregation with selection
INSERT INTO user_workload (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2,
'SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dollars
FROM sales s , times t
WHERE s.time_id = t.time_id
AND s.time_id between TO_DATE(''01-JAN-2000'', ''DD-MON-YYYY'')
AND TO_DATE(''01-JUL-2000'', ''DD-MON-YYYY'')
GROUP BY t.calendar_month_desc')
/
--Load all SQL queries.
INSERT INTO user_workload (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2,
'SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount
FROM sales s, times t, customers c, channels ch
WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id AND c.cust_state_province = ''CA''
AND ch.channel_desc IN (''Internet'',''Catalog'')
AND t.calendar_quarter_desc IN (''1999-Q1'',''1999-Q2'')
GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc')
/
-- order by
INSERT INTO user_workload (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2,
'SELECT c.country_id, c.cust_city, c.cust_last_name
FROM customers c WHERE c.country_id IN (52790, 52789)
ORDER BY c.country_id, c.cust_city, c.cust_last_name')
/
COMMIT;
CONNECT SH/SH;
set serveroutput on;
VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2(255);
VARIABLE workload_name VARCHAR2(255);
EXECUTE :workload_name := 'MYWORKLOAD'; EXECUTE DBMS_SQLTUNE.CREATE_SQLSET(:workload_name, 'test purposeV);
DECLARE
sqlset_cur DBMS_SQLTUNE.SQLSET_CURSOR; /*a sqlset cursor variable*/
BEGIN
OPEN sqlset_cur FOR
SELECT
SQLSET_ROW(null, sql_text, null, null, username, null,
null, 0,0,0,0,0,0,0,0,0,null, 0,0,0,0)
AS ROW
FROM USER_WORKLOAD;
DBMS_SQLTUNE.LOAD_SQLSET(:workload_name, sqlset_cur);
END;
EXECUTE :task_name := 'MYTASK';
EXECUTE DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', :task_id, :task_name);
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, 'STORAGE_CHANGE', 100);
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, 'ANALYSIS_SCOPE, INDEX');
EXECUTE DBMS_ADVISOR.ADD_STS_REF(:task_name, :workload_name);
EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);
-- See the number of recommendations and the status of the task.
SELECT rec_id, rank, benefit FROM user_advisor_recommendations WHERE task_name = :task_name;
詳細は、「推奨事項の表示」または「SQLスクリプトの生成」を参照してください。
-- See recommendation for each query.
SELECT sql_id, rec_id, precost, postcost,
(precost-postcost)*100/precost AS percent_benefit
FROM user_advisor_sqla_wk_stmts
WHERE task_name = :task_name AND workload_name = :workload_name;
-- See the actions for each recommendations.
SELECT rec_id, action_id, SUBSTR(command,1,30) AS command
FROM user_advisor_actions
WHERE task_name = :task_name
ORDER BY rec_id, action_id;
-- See what the actions are using sample procedure.
SET SERVEROUTPUT ON SIZE 99999
EXECUTE show_recm(:task_name);
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name),
'ADVISOR_RESULTS', 'Example1_script.sql');
次の例では、テンプレートを作成し、それを使用してタスクを作成します。次にこのタスクを使用して、「ユーザー定義のワークロードの推奨事項」と同様に、ユーザー定義表から推奨事項が生成されます。
CONNECT SH/SH; VARIABLE template_id NUMBER; VARIABLE template_name VARCHAR2(255);
EXECUTE :template_name := 'MY_TEMPLATE';
EXECUTE DBMS_ADVISOR.CREATE_TASK ( - 'SQL Access Advisor',:template_id, :template_name, is_template=>'TRUE');
推奨される索引およびマテリアライズド・ビューのネーミング規則を設定します。
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - :template_name, 'INDEX_NAME_TEMPLATE', 'SH_IDX$$_<SEQ>'); EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - :template_name, 'MVIEW_NAME_TEMPLATE', 'SH_MV$$_<SEQ>'); --Set default owners for recommended indexes/materialized views. EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - :template_name, 'DEF_INDEX_OWNER', 'SH'); EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - :template_name, 'DEF_MVIEW_OWNER', 'SH'); --Set default tablespace for recommended indexes/materialized views. EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - :template_name, 'DEF_INDEX_TABLESPACE', 'SH_INDEXES'); EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - :template_name, 'DEF_MVIEW_TABLESPACE', 'SH_MVIEWS');
VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2(255); EXECUTE :task_name := 'MYTASK'; EXECUTE DBMS_ADVISOR.CREATE_TASK ( - 'SQL Access Advisor', :task_id, :task_name, template => 'MY_TEMPLATE'); --See the parameter settings for task SELECT parameter_name, parameter_value FROM user_advisor_parameters WHERE task_name = :task_name AND (parameter_name LIKE '%MVIEW%' OR parameter_name LIKE '%INDEX%');
EXECUTE :workload_name := 'MYWORKLOAD'; EXECUTE DBMS_SQLTUNE.CREATE_SQLSET(:workload_name, 'test_purpose');
DECLARE
sqlset_cur DBMS_SQLTUNE.SQLSET_CURSOR; /*a sqlset cursor variable*/
BEGIN
OPEN sqlset_cur FOR
SELECT
SQLSET_ROW(null,sql_text,null,null,username, null, null, 0,0,0,0,0,0,0,0,0,
null,0,0,00) AS row
FROM user_workload;
DBMS_SQLTUNE.LOAD_SQLSET(:workload_name, sqlsetcur);
END;
EXECUTE DBMS_ADVISOR.ADD_STS_REF(:task_name, :workload_name);
EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name),-
'ADVISOR_RESULTS', 'Example2_script.sql');
この例では、SQLアクセス・アドバイザを使用して、既存の索引およびマテリアライズド・ビューの使用状況を評価する方法を示します。「ユーザー定義のワークロードの推奨事項」に記載されているように、ワークロードをUSER_WORKLOAD表にロードするとします。特定のワークロードによって使用されている索引およびマテリアライズド・ビューは、SQLアクセス・アドバイザの推奨事項でRETAINアクションとして表示されます。
VARIABLE task_id NUMBER; VARIABLE task_name VARCHAR2(255); VARIABLE workload_name VARCHAR2(255);
EXECUTE :workload_name := 'MYWORKLOAD';
EXECUTE DBMS_SQLTUNE.CREATE_SQLSET(:workload_name, 'test_purpose');
DECLARE
sqlset_cur DBMS_SQLTUNE.SQLSET_CURSOR; /*a sqlset cursor variable*/
BEGIN
OPEN sqlset_cur FOR
SELECT
SQLSET_ROW(null,sql_text,null,null,username, null, null, 0,0,0,0,0,0,0,0,0,
null, 0,0,0,0)
AS ROW
FROM user_workload;
DBMS_SQLTUNE.LOAD_SQLSET(:workload_name, :sqlsetcur);
END;
EXECUTE :task_name := 'MY_EVAL_TASK';
EXECUTE DBMS_ADVISOR.CREATE_TASK ('SQL Access Advisor', :task_id, :task_name);
EXECUTE DBMS_ADVISOR.ADD_STS_REF(:task_name, :workload_name);
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER (:task_name, 'EVALUATION_ONLY', 'TRUE');
EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);
--See the number of recommendations and the status of the task.
SELECT rec_id, rank, benefit FROM user_advisor_recommendations WHERE task_name = :task_name; --See the actions for each recommendation. SELECT rec_id, action_id, SUBSTR(command,1,30) AS command, attr1 AS name FROM user_advisor_actions WHERE task_name = :task_name ORDER BY rec_id, action_id;
高速リフレッシュおよびクエリー・リライトのために最適化されたマテリアライズド・ビューを作成するには、いくつかのDBMS_MVIEW プロシージャが役立ちます。EXPLAIN_MVIEWプロシージャは、マテリアライズド・ビューが高速リフレッシュ可能かどうか、また、通常のクエリー・リライトを実行できるかどうかを示します。EXPLAIN_REWRITEは、クエリー・リライトが行われるかどうかを示します。ただし、これらのプロシージャでは、高速リフレッシュやクエリー・リライトを実行する方法は示されません。
マテリアライズド・ビューをさらに使いやすくするため、TUNE_MVIEWプロシージャでは、CREATE MATERIALIZED VIEW文を最適化する方法と、高速リフレッシュや通常のクエリー・リライトのためのその他の要件(マテリアライズド・ビュー・ログやリライト同値化関係など)を満たす方法が示されます。TUNE_MVIEWにより、CREATE MATERIALIZED VIEW文の分析と処理が行われ、2つの出力結果(マテリアライズド・ビューの実装とCREATE MATERIALIZED VIEW操作の取消し)が生成されます。この2セットの出力結果は、ビューまたはSQLアクセス・アドバイザによって作成される外部スクリプト・ファイルを通じてアクセスできます。これらの外部スクリプト・ファイルを実行すると、マテリアライズド・ビューを実装できます。
TUNE_MVIEWプロシージャを使用すると、マテリアライズド・ビューおよび必要なコンポーネント(マテリアライズド・ビュー・ログなど)が適切に作成されるため、マテリアライズド・ビューの詳細を理解する必要なしに、アプリケーションにマテリアライズド・ビューを作成できます。
|
関連項目: TUNE_MVIEWプロシージャの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 |
この項では、次の項目について説明します。
TUNE_MVIEWの構文は次のとおりです。
DBMS_ADVISOR.TUNE_MVIEW ( task_name IN OUT VARCHAR2, mv_create_stmt IN [CLOB | VARCHAR2])
TUNE_MVIEWプロシージャでは、task_nameとmv_create_stmtという2つの入力パラメータが使用されます。task_nameはユーザー指定のタスク識別子で、出力結果へのアクセスに使用されます。mv_create_stmtは、チューニングが行われる完全なCREATE MATERIALIZED VIEW文です。入力されたCREATE MATERIALIZED VIEW文にREFRESH FAST句またはENABLE QUERY REWRITE句の一方あるいは両方が含まれない場合、TUNE_MVIEWでは、デフォルトであるREFRESH FORCE句およびDISABLE QUERY REWRITE句を使用して文をチューニングし、可能な場合は高速リフレッシュ、それ以外の場合は完全リフレッシュができるようにします。
TUNE_MVIEWプロシージャでは、内部に任意の問合せ定義を持つことができる、様々なCREATE MATERIALIZED VIEW文が処理されます。この問合せ定義は、単純なSELECT文である場合も、集合演算子やインライン・ビューを持つ複雑な問合せである場合もあります。マテリアライズド・ビューの問合せ定義にREFRESH FAST句が含まれる場合、TUNE_MVIEWによってその問合せが分析され、高速リフレッシュが可能かどうか確認されます。高速リフレッシュが可能な場合、「マテリアライズド・ビューはすでに最適であり、これ以上チューニングできません」というメッセージが戻されます。それ以外の場合は、TUNE_MVIEWプロシージャが特定の文のチューニング操作を開始します。
TUNE_MVIEWプロシージャでは、FAST REFRESHが可能なように、必須集計列などの新しい列の追加やマテリアライズド・ビュー・ログの修正によって、問合せ定義を修正する出力文を生成できます。複雑な問合せ定義の場合、TUNE_MVIEWプロシージャは、その問合せを分解して複数の高速リフレッシュ可能なマテリアライズド・ビューを生成するか、高速リフレッシュの要件が最大限に満たされる方法でマテリアライズド・ビューを再記述することがあります。TUNE_MVIEWプロシージャでは、次の複雑な問合せ構造を持つ問合せ定義がサポートされます。
集合演算子(UNION、UNION ALL、MINUSおよびINTERSECT)
COUNT DISTINCT
SELECT DISTINCT
インライン・ビュー
ENABLE QUERY REWRITE句を指定した場合、TUNE_MVIEWは、REFRESH FASTに類似したプロセスを使用して文の修正も行います。このプロシージャは、できるだけ多くの拡張形式のクエリー・リライトが可能となるようにマテリアライズド・ビューを再定義します。
TUNE_MVIEWプロシージャでは、実行可能文として、2つの出力結果が生成されます。1つの出力結果(IMPLEMENTATION)は、マテリアライズド・ビューと高速リフレッシュやクエリー・リライトを可能にするために必要なコンポーネント(マテリアライズド・ビュー・ログやリライト同値化)を最大限実装するためのものです。もう1つの出力結果(UNDO)は、不要と判断した場合にマテリアライズド・ビューとリライト同値化を削除するためのものです。
IMPLEMENTATIONプロセスの出力文には、次のものがあります。
CREATE MATERIALIZED VIEW LOG文: 高速リフレッシュに必要で、欠落しているマテリアライズド・ビュー・ログを作成します。
ALTER MATERIALIZED VIEW LOG FORCE文: マテリアライズド・ビュー・ログに関する要件(高速リフレッシュに必要で、欠落しているフィルタ列や順序など)を修正します。
1つ以上のCREATE MATERIALIZED VIEW文: 出力文が1つの場合、元の問合せ定義が直接記述しなおされ、変換されます。単純な問合せ変換では、必須列の追加が行われるのみです。たとえば、マテリアライズド結合ビューにROWID列が、マテリアライズド集計ビューに集計列が追加されます。分解を行う場合は、複数のCREATE MATERIALIZED VIEW文が生成され、元の文から変更された新しいトップレベルのマテリアライズド・ビューが1つ以上のサブマテリアライズド・ビューを参照する、ネストされたマテリアライズド・ビュー階層が形成されます。これは、高速リフレッシュやクエリー・リライトを最大限可能にするためです。多くの場合、サブマテリアライズド・ビューは高速リフレッシュが可能です。
BUILD_SAFE_REWRITE_EQUIVALENCE文: サブマテリアライズド・ビューを使用して、トップレベルのマテリアライズド・ビューをリライトできるようにします。分解を行う場合、クエリー・リライトを有効にする必要があります。
分解により、サブマテリアライズド・ビューの共有ができなくなることがあります。つまり、分解の場合、TUNE_MVIEWの出力には、常に新しいサブマテリアライズド・ビューが含まれます。既存のマテリアライズド・ビューは参照されません。
UNDOプロセスの出力文には、次のものがあります。
DROP MATERIALIZED VIEW文: IMPLEMENTATIONプロセスでのマテリアライズド・ビュー(サブマテリアライズド・ビューを含む)の作成を取り消します。
DROP_REWRITE_EQUIVALENCE文: 必要に応じて、IMPLEMENTATIONプロセスで構築されたリライト同値化関係を削除します。
UNDOプロセスには、マテリアライズド・ビュー・ログを削除する文は含まれません。様々なマテリアライズド・ビューが、マテリアライズド・ビュー・ログを共有できます。これらのログの一部は、リモートOracleデータベース・インスタンスに存在する場合もあります。
TUNE_MVIEWの出力結果へのアクセス方法は2通りあります。
DBMS_ADVISOR.GET_TASK_SCRIPTファンクションとDBMS_ADVISOR.CREATE_FILEプロシージャによるスクリプト生成
USER_TUNE_MVIEWビューまたはDBA_TUNE_MVIEWビューの使用方法
TUNE_MVIEWを実行すると、結果はSQLアクセス・アドバイザのリポジトリ表に出力され、データ・ディクショナリのビューであるUSER_TUNE_MVIEWとDBA_TUNE_MVIEWからアクセスできます。詳細は、『Oracle Databaseリファレンス』を参照してください。
推奨事項の実行スクリプトを生成する最も簡単な方法は、DBMS_ADVISOR.GET_TASK_SCRIPTプロシージャを使用することです。次に簡単な例を示します。最初に、結果を格納するディレクトリを定義する必要があります。
CREATE DIRECTORY TUNE_RESULTS AS '/tmp/script_dir'; GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC;
次に、実装スクリプトとUNDOスクリプトを生成し、それぞれ/tmp/script_dir/mv_create.sqlと/tmp/script_dir/mv_undo.sqlに格納します。
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name),-
'TUNE_RESULTS', 'mv_create.sql');
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name, -
'UNDO'), 'TUNE_RESULTS', 'mv_undo.sql');
TUNE_MVIEWプロシージャの使用例を次にいくつか示します。
例18-1 高速リフレッシュのための問合せ定義の最適化
この例では、TUNE_MVIEWによって問合せ定義を高速リフレッシュできるように変更する方法を示します。CREATE MATERIALIZED VIEW文は、変数create_mv_ddlで定義されています。これに、FAST REFRESH句が含まれています。問合せ定義に1つの問合せブロックがあり、その集計列SUM(s.amount_sold)には高速リフレッシュをサポートするための必須集計列がありません。TUNE_MVIEW文をこのMATERIALIZED VIEW CREATE文とともに実行すると、その結果のマテリアライズド・ビューの推奨事項は高速リフレッシュ可能です。
VARIABLE task_cust_mv VARCHAR2(30); VARIABLE create_mv_ddl VARCHAR2(4000); EXECUTE :task_cust_mv := 'cust_mv'; EXECUTE :create_mv_ddl := ' CREATE MATERIALIZED VIEW cust_mv REFRESH FAST DISABLE QUERY REWRITE AS SELECT s.prod_id, s.cust_id, SUM(s.amount_sold) sum_amount FROM sales s, customers cs WHERE s.cust_id = cs.cust_id GROUP BY s.prod_id, s.cust_id'; EXECUTE DBMS_ADVISOR.TUNE_MVIEW(:task_cust_mv, :create_mv_ddl);
cust_mvの元の問合せ定義は、高速リフレッシュを可能にするために集計列が追加されて、変更されています。
TUNE_MVIEWからの出力には、次のように、最適化されたマテリアライズド・ビューの問合せ定義が含まれます。
CREATE MATERIALIZED VIEW SH.CUST_MV
REFRESH FAST WITH ROWID
DISABLE QUERY REWRITE AS
SELECT SH.SALES.PROD_ID C1, SH.CUSTOMERS.CUST_ID C2,
SUM("SH"."SALES"."AMOUNT_SOLD") M1,
COUNT("SH"."SALES"."AMOUNT_SOLD") M2,
COUNT(*) M3
FROM SH.SALES, SH.CUSTOMERS
WHERE SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID
GROUP BY SH.SALES.PROD_ID, SH.CUSTOMERS.CUST_ID;
UNDOの出力は次のとおりです。
DROP MATERIALIZED VIEW SH.CUST_MV;
例18-2 USER_TUNE_MVIEWビューからのIMPLEMENTATIONの出力へのアクセス
SELECT STATEMENT FROM USER_TUNE_MVIEW WHERE TASK_NAME= :task_cust_mv AND SCRIPT_TYPE='IMPLEMENTATION';
例18-3 スクリプト・ファイルへのIMPLEMENTATIONの出力の保存
CREATE DIRECTORY TUNE_RESULTS AS '/myscript' GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC; EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_cust_mv), - 'TUNE_RESULTS', 'mv_create.sql');
例18-4 複数のマテリアライズド・ビューの作成によるクエリー・リライトの有効化
この例では、クエリー・リライトでサポートされない集合演算子UNIONを含むマテリアライズド・ビューの問合せ定義を、複数のサブマテリアライズド・ビューに分解し、クエリー・リライトを可能にします。入力ディテール表は、sales、customersおよびcountriesです。これらの表には、マテリアライズド・ビュー・ログがありません。最初に、TUNE_MVIEW文をcreate_mv_ddl変数で定義されているCREATE MATERIALIZED VIEW文とともに実行する必要があります。
EXECUTE :task_cust_mv := 'cust_mv2';
EXECUTE :create_mv_ddl := '
CREATE MATERIALIZED VIEW cust_mv
ENABLE QUERY REWRITE AS
SELECT s.prod_id, s.cust_id, COUNT(*) cnt, SUM(s.amount_sold) sum_amount
FROM sales s, customers cs, countries cn
WHERE s.cust_id = cs.cust_id AND cs.country_id = cn.country_id
AND cn.country_name IN (''USA'',''Canada'')
GROUP BY s.prod_id, s.cust_id
UNION
SELECT s.prod_id, s.cust_id, COUNT(*) cnt, SUM(s.amount_sold) sum_amount
FROM sales s, customers cs
WHERE s.cust_id = cs.cust_id AND s.cust_id IN (1005,1010,1012)
GROUP BY s.prod_id, s.cust_id';
マテリアライズド・ビューの問合せ定義には、通常のクエリー・リライトをサポートしないUNION集合演算子が含まれています。ただし、これを複数のマテリアライズド・ビューに分解すると、クエリー・リライトが可能になります。通常のクエリー・リライトをサポートするには、MATERIALIZED VIEWの問合せ定義を分解します。
EXECUTE DBMS_ADVISOR.TUNE_MVIEW(:task_cust_mv, :create_mv_ddl);
TUNE_MVIEWからの次の推奨事項には、マテリアライズド・ビュー・ログと複数のマテリアライズド・ビューが含まれます。
CREATE MATERIALIZED VIEW LOG ON "SH"."CUSTOMERS"
WITH ROWID, SEQUENCE("CUST_ID")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"SH"."CUSTOMERS"
ADD ROWID, SEQUENCE("CUST_ID")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON
"SH"."SALES"
WITH ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"SH"."SALES"
ADD ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON
"SH"."COUNTRIES"
WITH ROWID, SEQUENCE("COUNTRY_ID","COUNTRY_NAME")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"SH"."COUNTRIES"
ADD ROWID, SEQUENCE("COUNTRY_ID","COUNTRY_NAME")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"SH"."CUSTOMERS"
ADD ROWID, SEQUENCE("CUST_ID","COUNTRY_ID")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"SH"."SALES"
ADD ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW SH.CUST_MV$SUB1
REFRESH FAST WITH ROWID ON COMMIT
ENABLE QUERY REWRITE
AS SELECT SH.SALES.PROD_ID C1, SH.CUSTOMERS.CUST_ID C2,
SUM("SH"."SALES"."AMOUNT_SOLD")
M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.SALES,
SH.CUSTOMERS WHERE SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID AND
(SH.SALES.CUST_ID IN (1012, 1010, 1005))
GROUP BY SH.SALES.PROD_ID, SH.CUSTOMERS.CUST_ID;
CREATE MATERIALIZED VIEW SH.CUST_MV$SUB2
REFRESH FAST WITH ROWID ON COMMIT
ENABLE QUERY REWRITE
AS SELECT SH.SALES.PROD_ID C1, SH.CUSTOMERS.CUST_ID C2,
SH.COUNTRIES.COUNTRY_NAME C3, SUM("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES".
"AMOUNT_SOLD")
M2, COUNT(*) M3 FROM SH.SALES, SH.CUSTOMERS, SH.COUNTRIES WHERE
SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID AND SH.COUNTRIES.COUNTRY_ID = SH.CUSTOMERS.COUNTRY_ID
AND (SH.COUNTRIES.COUNTRY_NAME IN ('USA', 'Canada')) GROUP BY
SH.SALES.PROD_ID,
SH.CUSTOMERS.CUST_ID, SH.COUNTRIES.COUNTRY_NAME;
CREATE MATERIALIZED VIEW SH.CUST_MV
REFRESH FORCE WITH ROWID
ENABLE QUERY REWRITE
AS (SELECT "CUST_MV$SUB2"."C1" "PROD_ID","CUST_MV$SUB2"."C2"
"CUST_ID",SUM("CUST_MV$SUB2"."M3")
"CNT",SUM("CUST_MV$SUB2"."M1") "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB2"
"CUST_MV$SUB2" GROUP BY "CUST_MV$SUB2"."C1","CUST_MV$SUB2"."C2")UNION
(SELECT "CUST_MV$SUB1"."C1" "PROD_ID","CUST_MV$SUB1"."C2"
"CUST_ID",SUM("CUST_MV$SUB1"."M3")
"CNT",SUM("CUST_MV$SUB1"."M1") "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1"
"CUST_MV$SUB1" GROUP BY "CUST_MV$SUB1"."C1","CUST_MV$SUB1"."C2");
BEGIN
DBMS_ADVANCED_REWRITE.BUILD_SAFE_REWRITE_EQUIVALENCE ('SH.CUST_MV$RWEQ',
'SELECT s.prod_id, s.cust_id, COUNT(*) cnt,
SUM(s.amount_sold) sum_amount
FROM sales s, customers cs, countries cn
WHERE s.cust_id = cs.cust_id AND cs.country_id = cn.country_id
AND cn.country_name IN (''USA'',''Canada'')
GROUP BY s.prod_id, s.cust_id
UNION
SELECT s.prod_id, s.cust_id, COUNT(*) cnt,
SUM(s.amount_sold) sum_amount
FROM sales s, customers cs
WHERE s.cust_id = cs.cust_id AND s.cust_id IN (1005,1010,1012)
GROUP BY s.prod_id, s.cust_id',
'(SELECT "CUST_MV$SUB2"."C3" "PROD_ID","CUST_MV$SUB2"."C2" "CUST_ID",
SUM("CUST_MV$SUB2"."M3") "CNT",
SUM("CUST_MV$SUB2"."M1") "SUM_AMOUNT"
FROM "SH"."CUST_MV$SUB2" "CUST_MV$SUB2"
GROUP BY "CUST_MV$SUB2"."C3","CUST_MV$SUB2"."C2")
UNION
(SELECT "CUST_MV$SUB1"."C2" "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID",
"CUST_MV$SUB1"."M3" "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT"
FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1")',-1553577441)
END;
/;
DROPの出力は次のとおりです。
DROP MATERIALIZED VIEW SH.CUST_MV$SUB1
DROP MATERIALIZED VIEW SH.CUST_MV$SUB2
DROP MATERIALIZED VIEW SH.CUST_MV
DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE('SH.CUST_MV$RWEQ')
cust_mvの元の問合せ定義は、cust_mv$SUB1およびcust_mv$SUB2という2つのサブマテリアライズド・ビューに分解されました。COUNT(amount_sold)という新しい列がcust_mv$SUB1に追加され、マテリアライズド・ビューの高速リフレッシュが可能になりました。
cust_mvの元の問合せ定義は、2つのサブマテリアライズド・ビューを問い合せるように変更され、両方のサブマテリアライズド・ビューで高速リフレッシュと通常のクエリー・リライトが可能です。
必要なマテリアライズド・ビュー・ログが追加され、サブマテリアライズド・ビューの高速リフレッシュが可能です。ディテール表ごとに、2つのマテリアライズド・ビュー・ログ文が生成されます。1つはCREATE MATERIALIZED VIEW文、もう1つはALTER MATERIALIZED VIEW FORCE文です。これらの文により、CREATEスクリプトを複数回実行できます。
BUILD_SAFE_REWRITE_EQUIVALENCE文は、古い問合せ定義を、新しいトップレベルのマテリアライズド・ビューの問合せ定義に結合します。これにより、クエリー・リライトでは、問合せへの応答に新しいトップレベルのマテリアライズド・ビューが使用されます。
例18-5 USER_TUNE_MVIEWビューからのIMPLEMENTATIONの出力へのアクセス
SELECT * FROM USER_TUNE_MVIEW WHERE TASK_NAME='cust_mv2' AND SCRIPT_TYPE='IMPLEMENTATION';
例18-6 スクリプト・ファイルへのIMPLEMENTATIONの出力の保存
次の文では、IMPLEMENTATIONの出力が、/myscript/mv_create2.sqlにあるスクリプト・ファイルに保存されます。
CREATE DIRECTORY TUNE_RESULTS AS '/myscript'
GRANT READ, WRITE ON DIRECTRY TUNE_RESULTS TO PUBLIC;
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('cust_mv2'),
'TUNE_RESULTS', 'mv_create2.sql');
この例では、TUNE_MVIEWを使用して、高速リフレッシュを行えるようにマテリアライズド・ビューを最適化する方法を示します。この例では、集合演算子を持つマテリアライズド・ビューの問合せ定義が1つのサブマテリアライズド・ビューと1つのトップレベル・マテリアライズド・ビューに変換されます。元の問合せ定義の副問合せの形式は類似しており、条件式は結合されます。
マテリアライズド・ビュー自体が高速リフレッシュできないよう、マテリアライズド・ビューの問合せ定義にはUNION集合演算子が含まれています。ただし、マテリアライズド・ビューの問合せ定義の2つの副問合せを、1つの問合せとして結合できます。
EXECUTE :create_mv_ddl := ' CREATE MATERIALIZED VIEW cust_mv REFRESH FAST ON DEMAND ENABLE QUERY REWRITE AS SELECT s.prod_id, s.cust_id, COUNT(*) cnt, SUM(s.amount_sold) sum_amount FROM sales s, customers cs WHERE s.cust_id = cs.cust_id AND s.cust_id IN (2005,1020) GROUP BY s.prod_id, s.cust_id UNION SELECT s.prod_id, s.cust_id, COUNT(*) cnt, SUM(s.amount_sold) sum_amount FROM sales s, customers cs - WHERE s.cust_id = cs.cust_id AND s.cust_id IN (1005,1010,1012) GROUP BY s.prod_id, s.cust_id'; EXECUTE DBMS_ADVISOR.TUNE_MVIEW(:task_cust_mv, :create_mv_ddl);
TUNE_MVIEWにより、次の推奨事項が生成されます。2つの副問合せが結合されて、サブマテリアライズド・ビューが最適化されています。サブマテリアライズド・ビューは、次のように新しいトップレベルのマテリアライズド・ビューによって参照されます。
CREATE MATERIALIZED VIEW LOG ON "SH"."SALES"
WITH ROWID, SEQUENCE ("PROD_ID","CUST_ID","AMOUNT_SOLD")
INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."SALES"
ADD ROWID, SEQUENCE ("PROD_ID","CUST_ID","AMOUNT_SOLD")
INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW LOG ON "SH"."CUSTOMERS"
WITH ROWID, SEQUENCE ("CUST_ID") INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."CUSTOMERS"
ADD ROWID, SEQUENCE ("CUST_ID") INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW SH.CUST_MV$SUB1
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE AS
SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2,
SUM("SH"."SALES"."AMOUNT_SOLD") M1,
COUNT("SH"."SALES"."AMOUNT_SOLD")M2, COUNT(*) M3
FROM SH.CUSTOMERS, SH.SALES
WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND
(SH.SALES.CUST_ID IN (2005, 1020, 1012, 1010, 1005))
GROUP BY SH.SALES.CUST_ID, SH.SALES.PROD_ID
CREATE MATERIALIZED VIEW SH.CUST_MV
REFRESH FORCE WITH ROWID ENABLE QUERY REWRITE AS
(SELECT "CUST_MV$SUB1"."C2" "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID",
"CUST_MV$SUB1"."M3" "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT"
FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1"
WHERE "CUST_MV$SUB1"."C1"=2005 OR "CUST_MV$SUB1"."C1"=1020)
UNION
(SELECT "CUST_MV$SUB1"."C2" "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID",
"CUST_MV$SUB1"."M3" "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT"
FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1"
WHERE "CUST_MV$SUB1"."C1"=1012 OR "CUST_MV$SUB1"."C1"=1010 OR
"CUST_MV$SUB1"."C1"=1005)
DBMS_ADVANCED_REWRITE.BUILD_SAFE_REWRITE_EQUIVALENCE ('SH.CUST_MV$RWEQ',
'SELECT s.prod_id, s.cust_id, COUNT(*) cnt,
SUM(s.amount_sold) sum_amount
FROM sales s, customers cs
WHERE s.cust_id = cs.cust_id AND s.cust_id IN (2005,1020)
GROUP BY s.prod_id, s.cust_id UNION
SELECT s.prod_id, s.cust_id, COUNT(*) cnt,
SUM(s.amount_sold) sum_amount
FROM sales s, customers cs
WHERE s.cust_id = cs.cust_id AND s.cust_id IN (1005,1010,1012)
GROUP BY s.prod_id, s.cust_id',
'(SELECT "CUST_MV$SUB1"."C2" "PROD_ID",
"CUST_MV$SUB1"."C1" "CUST_ID",
"CUST_MV$SUB1"."M3" "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT"
FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1"
WHERE "CUST_MV$SUB1"."C1"=2005OR "CUST_MV$SUB1"."C1"=1020)
UNION
(SELECT "CUST_MV$SUB1"."C2" "PROD_ID",
"CUST_MV$SUB1"."C1" "CUST_ID",
"CUST_MV$SUB1"."M3" "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT"
FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1"
WHERE "CUST_MV$SUB1"."C1"=1012 OR "CUST_MV$SUB1"."C1"=1010 OR
"CUST_MV$SUB1"."C1"=1005)',
1811223110);
cust_mvの元の問合せ定義は、サブマテリアライズド・ビューCUST_MV$SUB1の2つの副問合せの条件を結合することで最適化されています。また、必要なマテリアライズド・ビュー・ログも追加され、サブマテリアライズド・ビューの高速リフレッシュが可能です。
DROPの出力は次のとおりです。
DROP MATERIALIZED VIEW SH.CUST_MV$SUB1
DROP MATERIALIZED VIEW SH.CUST_MV
DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE('SH.CUST_MV$RWEQ');
次の文では、IMPLEMENTATIONの出力が、/myscript/mv_create3.sqlにあるスクリプト・ファイルに保存されます。
CREATE DIRECTORY TUNE_RESULTS AS '/myscript'
GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC;
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('cust_mv3'),
'TUNE_RESULTS', 'mv_create3.sql');
