10gリリース2（10.2）
B19207-02
 目次 |
 索引 |
| Oracle Database パフォーマンス・チューニング・ガイド 10gリリース2（10.2） B19207-02 |
|
この章では、SQLアクセス・アドバイザの使用方法について説明します。SQLアクセス・アドバイザは、マテリアライズド・ビュー、索引およびマテリアライズド・ビュー・ログについてアドバイスを提供するチューニング・ツールです。この章には次の項があります。
データ集中型の複雑な問合せの実行時に最適なパフォーマンスを実現できるようにデータベースをチューニングする際、マテリアライズド・ビューと索引が必要になります。SQLアクセス・アドバイザでは、指定のワークロードに関するマテリアライズド・ビュー、マテリアライズド・ビュー・ログおよび索引の適切なセットが推奨されるため、パフォーマンスの目標を達成するのに役立ちます。SQLを最適化する際には、これらの構造を理解して使用することが重要です。これにより、データを取り出す際のパフォーマンスが大幅に向上します。ただし、このような利点を利用するにはそれなりの負担が伴います。これらのオブジェクトの作成やメンテナンスには時間がかかり、また領域要件も重要になります。
SQLアクセス・アドバイザでは、ビットマップ索引、ファンクション索引およびBツリー索引が推奨されています。ビットマップ索引を使用すると、多くのタイプの非定型問合せの応答時間が短縮され、その他の索引付けの方法と比べて記憶域要件が軽減されます。Bツリー索引は、一意またはほぼ一意のキーに索引を付ける方法で、データ・ウェアハウスで最も一般的に使用されています。
また、SQLアクセス・アドバイザの別のコンポーネントにより、マテリアライズド・ビューの最適化も推奨されています。これにより、マテリアライズド・ビューの高速リフレッシュが可能になり、汎用的なクエリー・リライトを利用できます。
SQLアクセス・アドバイザを実行するには、Oracle Enterprise Manager（「セントラル・アドバイザ」ページからアクセス可能）からSQLアクセス・アドバイザ・ウィザードを使用するか、DBMS_ADVISORパッケージを起動します。DBMS_ADVISORパッケージは、任意のPL/SQLプログラムからコール可能な分析およびアドバイザ用のファンクションおよびプロシージャの集合です。図17-1は、ユーザー定義の表またはSQLキャッシュから取得した特定のワークロードについてSQLアクセス・アドバイザがマテリアライズド・ビューを推奨する方法を示します。ワークロードが提供されていない場合、SQLアクセス・アドバイザは仮想ワークロードも生成および使用できます。
SQLアクセス・アドバイザ・ウィザードまたはAPIを使用して、次の操作を実行できます。
また、SQLアクセス・アドバイザのAPIを使用して、次の操作を実行できます。
表と索引のカーディナリティ、およびディメンション・レベルのすべての列、JOIN KEY列およびファクト表のキー列の個別カーディナリティに関する構造的な統計を収集すると、SQLアクセス・アドバイザの推奨事項は大幅に向上します。これを行うには、DBMS_STATSパッケージを使用して正確な統計または見積り統計を収集します。統計の収集は時間のかかる処理であり、極端な統計精度は必要ないため、通常は統計を見積もることをお薦めします。これらの統計がない場合、その表を参照する問合せはワークロードで無効とマークされるため、これらの問合せについては推奨事項が生成されなくなります。また、既存のすべての索引とマテリアライズド・ビューを分析しておくこともお薦めします。DBMS_STATSパッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
SQLアクセス・アドバイザを使用する最も簡単な方法は、ウィザードを起動する方法です。このウィザードは、Oracle Enterprise Managerの「セントラル・アドバイザ」ページからアクセスできます。DBMS_ADVISORパッケージを介してSQLアクセス・アドバイザを使用する方が望ましい場合は、この項の説明にある基本的なコンポーネントや、様々なプロシージャをコールする必要があるシーケンスについて参照してください。
この項では、一連の推奨事項を生成するための4つの手順について説明します。
推奨を行う前に、タスクを作成する必要があります。推奨プロセスの結果など、推奨プロセスに関するすべての情報はタスク内に格納されるため、タスクは重要です。Oracle Enterprise ManagerのウィザードまたはDBMS_ADVISOR.QUICK_TUNEプロシージャを使用する場合、タスクは自動的に作成されます。これ以外のすべてのケースにおいては、DBMS_ADVISOR.CREATE_TASKプロシージャを使用してタスクを作成する必要があります。
タスクの処理内容を制御するには、DBMS_ADVISOR.SET_TASK_PARAMETERプロシージャを使用してタスクのパラメータを定義します。
タスクの作成の詳細は、「タスクの作成」を参照してください。
ワークロードは、SQLアクセス・アドバイザの主な入力データであり、1つ以上のSQL文と、各文を完全に説明する様々な統計や属性で構成されています。ワークロードにターゲットのビジネス・アプリケーションのすべてのSQL文が含まれる場合、このワークロードは全ワークロードとみなされます。一方、ワークロードにSQL文のサブセットが含まれる場合、このワークロードは部分ワークロードと呼ばれます。全ワークロードと部分ワークロードの違いは、全ワークロードの場合、効率的に使用されていない既存のマテリアライズド・ビューや索引を検出すると、SQLアクセス・アドバイザがこれらを削除するよう推奨する点にあります。
通常、SQLアクセス・アドバイザでは、すべての分析アクティビティの基礎としてワークロードを使用します。ワークロードには様々な種類の文が含まれることがありますが、ワークロードでは、特定の統計、ビジネスの重要性、または統計とビジネスの重要性の組合せに応じてエントリにランクが付けられます。このランクは重要です。このようにランクを付けることにより、SQLアクセス・アドバイザはビジネスへの影響が少ないSQL文よりも最も重要なSQL文を先に処理できるようになるためです。
データの集合を有効なワークロードとみなすために、SQLアクセス・アドバイザには特定の属性が必要になることがあります。一部の項目が欠落していても分析は実行できますが、推奨事項の品質が大幅に低下する場合があります。たとえば、SQLアクセス・アドバイザでは、SQL問合せとこの問合せを実行したユーザーがワークロードに含まれている必要があります。その他すべての属性はオプションです。ただし、ワークロードにI/OおよびCPU情報も含まれていると、SQLアクセス・アドバイザでは文の現在の効率をより正確に評価できる場合があります。ワークロードは、DBMS_ADVISOR.CREATE_SQLWKLDプロシージャを使用して作成される別のオブジェクトとして格納され、多くのアドバイザ・タスク間で簡単に共有できます。ワークロードは独立しているため、DBMS_ADVISOR.ADD_SQLWKLD_REFプロシージャを使用してタスクにリンクする必要があります。いったんこのリンクが確立されると、すべてのアドバイザ・タスクからワークロードに対する依存性が削除されるまではワークロードを削除または変更できなくなります。ワークロード参照が削除されるのは、親アドバイザ・タスクが削除される場合か、ユーザーがDBMS_ADVISOR.DELETE_SQLWKLD_REFプロシージャを使用してアドバイザ・タスクからワークロード参照を手動で削除する場合です。
SQLアクセス・アドバイザはワークロードなしでも使用できますが、最良の結果を得るには、ユーザー指定の表またはSQLチューニング・セットの形式でワークロードを提供するか、SQLキャッシュからワークロードをインポートする必要があります。ワークロードが提供されない場合、SQLアクセス・アドバイザは、スキーマに定義されているディメンションに基づいて仮想ワークロードを生成および使用できます。
ワークロードがリポジトリにロードされるか、推奨事項が生成されると、ワークロードにフィルタを適用し、分析対象を制限できます。これにより、様々なワークロード・シナリオに基づいて一連の各種推奨事項を生成できるようになります。
ワークロードの推奨プロセスとカスタマイズは、SQLアクセス・アドバイザ・パラメータによって制御されます。これらのパラメータは、必要な推奨事項のタイプや推奨内容のネーミング規則など、推奨プロセスの様々な側面を制御します。ワークロードに関しては、パラメータは、ワークロードの存続時間やワークロードに適用するフィルタを制御します。
これらのパラメータを設定するには、SET_TASK_PARAMETERおよびSET_SQLWKLD_PARAMETERプロシージャを使用します。パラメータは、タスクまたはワークロード・オブジェクトの存続期間は設定されたままであるという点において永続的です。SET_TASK_PARAMETERプロシージャを使用してパラメータを設定した場合、SET_TASK_PARAMETERをもう1回コールするまではこのパラメータは変更されません。
ワークロードの詳細は、「ワークロードの内容の定義」を参照してください。
タスクが存在するときにこのタスクにワークロードをリンクし、適切なパラメータを設定すると、DBMS_ADVISOR.EXECUTE_TASKプロシージャを使用して推奨事項を生成できます。これらの推奨事項は、SQLアクセス・アドバイザ・リポジトリに格納されます。
推奨プロセスにより、多数の推奨事項が生成されます。各推奨事項には、1つ以上のアクションが含まれます。たとえば、マテリアライズド・ビューを作成し、これを分析して統計情報を収集します。
タスクの推奨事項には、単純な提案から、索引、マテリアライズド・ビューおよびマテリアライズド・ビュー・ログなどの一連のデータベース・オブジェクトの実装を必要とする複雑なソリューションまで多岐にわたります。アドバイザ・タスクが実行されると、収集されたデータとユーザー調整のタスク・パラメータが慎重に分析されます。これにより、SQLアクセス・アドバイザでは、組み込まれているナレッジに基づいて精度を形成しようとします。次に、この精度は微調整され、ユーザーが表示および実装できるよう構造化された推奨事項の形式で格納されます。
推奨事項の生成の詳細は、「推奨事項の生成」を参照してください。
SQLアクセス・アドバイザの推奨事項を表示する方法には2通りあります。カタログ・ビューを使用する方法と、DBMS_ADVISOR.GET_TASK_SCRIPTプロシージャを使用してスクリプトを生成する方法です。Enterprise Managerでは、SQLアクセス・アドバイザ・プロセスが完了すると推奨事項が表示されます。
推奨事項を表示するためにカタログ・ビューを使用する方法の詳細は、「推奨事項の表示」を参照してください。 スクリプトの作成方法の詳細は、「SQLスクリプトの生成」を参照してください。
すべての推奨事項を受け入れる必要はなく、推奨事項のスクリプトに含める推奨事項をマークできます。
最終手順では、推奨事項を実装し、問合せのパフォーマンスが向上したかどうかを検証します。
SQLアクセス・アドバイザによって生成されたすべての必要情報は、データベース・ディクショナリの一部であるアドバイザ・リポジトリに格納されます。リポジトリを使用する利点は、次のとおりです。
この項では、SQLアクセス・アドバイザに関する一般的な情報や、SQLアクセス・アドバイザを使用するために必要な手順について説明します。この項には、次の項目があります。
図17-2には、SQLアクセス・アドバイザの使用手順と、SQLアクセス・アドバイザのすべてのパラメータの概要、およびその適切な使用タイミングを示します。
SQLアクセス・アドバイザを管理または使用するには、ADVISOR権限が必要です。SQLアクセス・アドバイザは、ワークロードの処理時に各文を検証し、表と列の参照関係を識別しようとします。この文のオリジナル・ユーザーが実行しているものとして各文を処理することによって検証されます。このユーザーが特定の表に対するSELECT権限を持っていない場合、SQLアクセス・アドバイザは、この表を参照している文をバイパスします。これにより、多くの文が分析から除外されることがあります。SQLアクセス・アドバイザがワークロード内のすべての文を除外すると、ワークロードは無効になり、次のメッセージが戻されます。
QSM-00774, there are no SQL statements to process for task TASK_NAME
重要なワークロード問合せが欠落しないようにするには、現在のデータベース・ユーザーが、マテリアライズド・ビューの分析対象の表に対するSELECT権限を持つ必要があります。これらの表については、ロールを介してこれらのSELECT権限を取得できません。
この項では、タスクとテンプレートの設定に関する次の側面について説明します。
アドバイザ・タスクとは、分析する内容と、この分析結果の提供先を定義するタスクです。ユーザーは、各タスクが特化した任意の数のタスクを作成できます。これらすべてのタスクは同じアドバイザ・タスク・モデルに基づいており、同じリポジトリを共有します。
タスクを作成するには、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); DBMS_ADVISOR.CREATE_TASK ( advisor_name IN VARCHAR2, task_name IN 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およびCREATE_SQLWKLDプロシージャとそのパラメータの詳細は、『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を使用します。詳細は、表17-4を参照してください。
EXECUTE DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', - :task_id, :task_name, template=>'SQLACCESS_WAREHOUSE');
この項では、ワークロードの管理に関する次の側面について説明します。
ワークロードは個別のワークロード・オブジェクトとして格納されるため、多くのアドバイザ・タスク間で簡単に共有できます。ワークロード・オブジェクトは、アドバイザ・タスクによっていったん参照されると、すべてのアドバイザ・タスクからこのデータに対する依存性が削除されるまでは削除または変更できなくなります。ワークロード参照が削除されるのは、親アドバイザ・タスクが削除される場合か、ユーザーがアドバイザ・タスクからワークロード参照を手動で削除する場合です。
SQLアクセス・アドバイザのパフォーマンスは、使用状況に基づいたワークロードが使用可能な場合に最も高くなります。SQLアクセス・ワークロード・リポジトリには複数のワークロードを格納できるため、長期間にわたってデータベース・インスタンスの起動から停止までのライフサイクル全体について、実際のデータ・ウェアハウスまたはトランザクション処理環境の様々な使用状況を参照できます。
実際にワークロードのSQL文を定義する前に、CREATE_SQLWKLDプロシージャを使用してワークロードを作成する必要があります。次に、IMPORT_SQLWKLDプロシージャを使用してワークロードをロードします。特定のワークロードを削除するには、DELETE_SQLWKLDプロシージャをコールし、このプロシージャに有効なワークロード名を渡します。現在のユーザーのすべてのワークロードを削除するには、DELETE_SQLWKLDをコールし、定数値ADVISOR_ALLまたは%を渡します。
CREATE_SQLWKLDプロシージャは、ワークロードを作成します。このプロシージャは、SQL文のインポートや更新などのその他のワークロード操作を実行する前に存在している必要があります。ワークロードは名前で識別されるため、操作に関連する一意の名前を定義する必要があります。
この構文は、次のとおりです。
DBMS_ADVISOR.CREATE_SQLWKLD ( workload_name IN OUT VARCHAR2, description IN VARCHAR2 := NULL, template IN VARCHAR2 := NULL. is_template IN VARCHAR2 := 'FALSE');
このプロシージャの使用例は、次のとおりです。
VARIABLE workload_name VARCHAR2(255); EXECUTE :workload_name := 'MYWORKLOAD'; EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name,'This is my first workload');
VARIABLE template_id NUMBER; VARIABLE template_name VARCHAR2(255);
MY_WK_TEMPLATEと呼ばれるテンプレートを作成します。
EXECUTE :template_name := 'MY_WK_TEMPLATE'; EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:template_name, is_template=>'TRUE');
shスキーマ内の表のみをチューニングするフィルタを設定します。
-- set USERNAME_LIST filter to SH EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER( - :template_name, 'USERNAME_LIST', 'SH');
VARIABLE workload_name VARCHAR2(255); EXECUTE :workload_name := 'MYWORKLOAD'; EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD ( - :workload_name, 'This is my first workload', 'MY_WK_TEMPLATE');
CREATE_SQLWKLDプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
推奨プロセスを開始する前に、タスクをワークロードにリンクする必要があります。これを行うには、ADD_SQKLWKLD_REFプロシージャを使用します。タスクとワークロードはそれぞれの名前を使用してリンクされます。このプロシージャは、アドバイザ・タスクとワークロード間のリンクを確立します。アドバイザ・タスクとワークロード間のリンクがいったん確立されると、ワークロードは削除できなくなります。 この構文は、次のとおりです。
DBMS_ADVISOR.ADD_SQLWKLD_REF (task_name IN VARCHAR2, workload_name IN VARCHAR2);
次の例では、作成したMYTASKタスクをMYWORKLOAD SQLワークロードにリンクします。
EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_REF('MYTASK', 'MYWORKLOAD');
ADD_SQLWKLD_REFプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
ワークロードの作成後、ワークロードに情報を移入する必要があります。ワークロードは、データベースに使用されているSQL文で構成されるのが理想的です（仮想ワークロードである場合を除きます）。SQLアクセス・アドバイザは、次のソースからワークロードを取得できます。
SQLチューニング・セットは、ワークロード・リポジトリにあるワークロードです。SQLチューニング・セットをSQLアクセス・アドバイザのワークロードとして使用するには、IMPORT_WORKLOAD_STSプロシージャを使用してこれをインポートします。このプロシージャの構文は、次のとおりです。
DBMS_ADVISOR.IMPORT_SQLWKLD_STS (workload_name IN VARCHAR2, sts_owner IN VARCHAR2, sts_name IN VARCHAR2, import_mode IN VARCHAR2 := 'NEW', priority IN NUMBER := 2, saved_rows OUT NUMBER, failed_rows OUT NUMBER); DBMS_ADVISOR.IMPORT_SQLWKLD_STS (workload_name IN VARCHAR2, sts_name IN VARCHAR2, import_mode IN VARCHAR2 := 'NEW', priority IN NUMBER := 2, saved_rows OUT NUMBER, failed_rows OUT NUMBER);
ワークロードが収集され、文にフィルタがかけられた後、SQLアクセス・アドバイザは、ワークロード内のDML文に関する使用状況統計を計算します。
次の例では、MY_STS_WORKLOADという名前のSQLチューニング・セットからワークロードを作成します。
VARIABLE sqlsetname VARCHAR2(30); VARIABLE workload_name VARCHAR2(30); VARIABLE saved_stmts NUMBER; VARIABLE failed_stmts NUMBER; EXECUTE :sqlsetname := 'MY_STS_WORKLOAD'; EXECUTE :workload_name := 'MY_WORKLOAD'; EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD (:workload_name); EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_STS (:workload_name , - :sqlsetname, 'NEW', 1, :saved_stmts, :failed_stmts);
ユーザー定義のワークロードをロードするには、IMPORT_SQLWKLD_USERプロシージャを使用します。このプロシージャは、ユーザー構成表からアプリケーション・ワークロードを収集するか、アプリケーション・ワークロードをアドバイザ・リポジトリに表示して保存します。owner_nameとtable_nameという2つのパラメータにより、ワークロードの取得元の表が識別されます。
ワークロードが格納されるスキーマ、表の名前、またはユーザー定義表の数には制限がありません。唯一の要件は、ユーザー表の形式がUSER_WORKLOAD表と一致することと（表17-1を参照）、ユーザーにはワークロード表またはビューに対するSELECTアクセス権があることが必要です。 この構文は、次のとおりです。
DBMS_ADVISOR.IMPORT_SQLWKLD_USER ( workload_name IN VARCHAR2, import_mode IN VARCHAR2 := 'NEW', owner_name IN VARCHAR2, table_name IN VARCHAR2, saved_rows OUT NUMBER, failed_rows OUT NUMBER);
次の例では、ユーザー表SH.USER_WORKLOADを使用して、前に作成したMYWORKLOADワークロードをロードします。この表には、SQL文が移入されており、表17-1に指定されている形式に準拠していることが前提です。
VARIABLE saved_stmts NUMBER; VARIABLE failed_stmts NUMBER; EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_USER( - 'MYWORKLOAD', 'NEW', 'SH', 'USER_WORKLOAD', :saved_stmts, :failed_stmts);
IMPORT_SQLWKLD_USERプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
SQLキャッシュのワークロードを取得するには、IMPORT_SQLWKLD_SQLCACHEプロシージャを使用します。このプロシージャがコールされると、SQLキャッシュの現在の内容が分析され、ワークロードに格納されます。IMPORT_SQLWKLD_SQLCACHEプロシージャにより、SQLキャッシュからSQLワークロードがロードされます。 この構文は、次のとおりです。
DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE ( workload_name IN VARCHAR2, import_mode IN VARCHAR2, priority IN NUMBER := 2, saved_rows OUT NUMBER, failed_rows OUT NUMBER);
IMPORT_SQLWKLD_SQLCACHEプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
次の例では、前に作成したMYWORKLOADワークロードをSQLキャッシュからロードします。ロードしたワークロード文の優先順位は、2（MEDIUM）です。
VARIABLE saved_stmts NUMBER; VARIABLE failed_stmts NUMBER; EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE (- 'MYWORKLOAD', 'APPEND', 2, :saved_stmts, :failed_stmts);
SQLアクセス・アドバイザでは、ワークロード情報をSQLキャッシュから取得できます。収集したデータが、インスタンス・パラメータcursor_sharingがSIMILARまたはFORCEに設定されたサーバーから取得された場合、リテラル値が組み込まれたユーザー問合せは、システム生成のバインド変数が含まれる文に変換されます。SQLアクセス・アドバイザを使用してマテリアライズド・ビューを推奨する場合、サーバーのインスタンス・パラメータcursor_sharingをEXACTに設定し、WHERE句を持つマテリアライズド・ビューが推奨されるようにする必要があります。
多くの場合、アプリケーション・ワークロードはまだ存在していません。このような場合、SQLアクセス・アドバイザは、現在の論理スキーマ設計を調べ、表間に定義されている関係に基づいて推奨事項を形成できます。このタイプのワークロードは、仮想ワークロードとも呼ばれます。SQLアクセス・アドバイザは、推奨事項の初期セットを生成し、アプリケーションをチューニングするための信頼できるベースとなります。
仮想ワークロードを使用する利点は、次のとおりです。
仮想ワークロードを使用する短所は、次のとおりです。
仮想ワークロードを正常にインポートするには、ターゲット・スキーマにディメンション情報が含まれている必要があります。この場合、IMPORT_SQLWKLD_SCHEMAプロシージャを使用します。 この構文は、次のとおりです。
DBMS_ADVISOR.IMPORT_SQLWKLD_SCHEMA ( workload_name IN VARCHAR2, import_mode IN VARCHAR2 := 'NEW', priority IN NUMBER := 2, saved_rows OUT NUMBER, failed_rows OUT NUMBER);
IMPORT_SQLWKLD_SCHEMAプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。このプロシージャを使用するには、外部プロシージャを構成する必要があります。
次の例では、SCHEMA_WKLDと呼ばれる仮想ワークロードを作成し、VALID_TABLE_LISTをshに設定し、IMPORT_SQLWKLD_SCHEMAをコールして仮想ワークロードを生成します。
VARIABLE workload_name VARCHAR2(255); VARIABLE saved_stmts NUMBER; VARIABLE failed_stmts NUMBER; EXECUTE :workload_name := 'SCHEMA_WKLD'; EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name); EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER (:workload_name, - VALID_TABLE_LIST, 'SH'); EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_SCHEMA ( - :workload_name, 'NEW', 2, :saved_stmts, :failed_stmts);
IMPORT_SQLWKLD_SCHEMAを使用する場合、VALID_TABLE_LISTパラメータには、SCO%やSCOTT.EMP%などのワイルドカードは使用できません。サポートされているワイルドカードの形式は、特定のスキーマ内のすべての表を指定するSCOTT.%のみです。
Oracle Database 9i サマリー・アドバイザを使用してワークロードを作成したとします。これらのワークロードをSQLアクセス・アドバイザにより使用できるようにするには、IMPORT_SQLWLD_SUMADVプロシージャを使用してこれらをインポートします。このプロシージャを使用するには、Oracle Database 9i のワークロードIDを知っている必要があります。
このプロシージャは、SQLワークロードをサマリー・アドバイザのワークロードから収集します。このプロシージャは、Oracle Database 9i サマリー・アドバイザのユーザーによるSQLアクセス・アドバイザへの移行をサポートすることを目的としています。 この構文は、次のとおりです。
DBMS_ADVISOR.IMPORT_SQLWKLD_SUMADV ( workload_name IN VARCHAR2, import_mode IN VARCHAR2 := 'NEW', priority IN NUMBER := 2, sumadv_id IN NUMBER, saved_rows OUT NUMBER, failed_rows OUT NUMBER);
IMPORT_SQLWKLD_SUMADVプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
次の例では、Oracle Database 9i サマリー・アドバイザのワークロードからSQLワークロードを作成します。Oracle Database 9i のワークロードのworkload_idは、777です。
VARIABLE workload_name VARCHAR2(255); VARIABLE saved_stmts NUMBER; VARIABLE failed_stmts NUMBER;
WKLD_9Iという名前のワークロードを作成します。
EXECUTE :workload_name := 'WKLD_9I'; EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name);
EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_SUMADV ( - :workload_name, 'NEW', 2, 777, :saved_stmts, :failed_stmts);
ロード時にSQLワークロードにフィルタをかけるには、SET_SQLWKLD_PARAMETERを使用して『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』に記載されている1つ以上のパラメータを設定します。
次の例は、SQLワークロードのパラメータの設定を示しています。ここでは、SQL_LIMITを3、ORDER_LISTをOPTIMIZER_COSTに設定します。つまり、ワークロードのインポート時には、文はOPTIMIZER_COSTで順序付けられ、上位3つの文が保持されます。
-- Order statements by OPTIMIZER_COST EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER ( - 'MYWORKLOAD', 'ORDER_LIST', 'OPTIMIZER_COST'); -- Max number of statements 3 EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER('MYWORKLOAD', 'SQL_LIMIT', 3);
ワークロードをインポートするもう1つの方法は、SQL文を手動で指定し、ADD_SQLWKLD_STATEMENTプロシージャを使用してこれらをワークロードに追加する方法です。このプロシージャは、指定されたワークロードにSQL文を追加します。 この構文は、次のとおりです。
DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT ( workload_name IN VARCHAR2, module IN VARCHAR2, action IN VARCHAR2, cpu_time IN NUMBER := 0, elapsed_time IN NUMBER := 0, disk_reads IN NUMBER := 0, buffer_gets IN NUMBER := 0, rows_processed IN NUMBER := 0, optimizer_cost IN NUMBER := 0, executions IN NUMBER := 1, priority IN NUMBER := 2, last_execution_date IN DATE := 'SYSDATE', stat_period IN NUMBER := 0, username IN VARCHAR2, sql_text IN CLOB);
ADD_SQLWKLD_STATEMENTプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。次の例では、MYWORKLOADワークロードに文を1つ追加します。
VARIABLE sql_text VARCHAR2(400); EXECUTE :sql_text := 'SELECT AVG(amount_sold) FROM sales'; EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT ( - 'MYWORKLOAD', 'MONTHLY', 'ROLLUP', priority=>1, executions=>10, - username => 'SH', sql_text => :sql_text);
既存のSQL文を特定のワークロードから削除するには、DELETE_SQLWKLD_STATEMENTプロシージャを使用します。このプロシージャでは、sql_idによって指定した文を削除できます。
DBMS_ADVISOR.DELETE_SQLWKLD_STATEMENT (workload_name IN VARCHAR2, sql_id IN NUMBER);
次の例では、sql_idが10の文をMYWORKLOADから削除します。
EXECUTE DBMS_ADVISOR.DELETE_SQLWKLD_STATEMENT('MYWORKLOAD', 10);
ワークロードが現在アクティブなタスクによって参照されている場合、このワークロードは変更または削除できません。タスクは、初期状態にない場合アクティブであるとみなされます。タスクを初期状態に設定する方法の詳細は、RESET_TASKプロシージャを参照してください。 DELETE_SQLWKLD_STATEMENTプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
ワークロードのSQL文を変更するには、UPDATE_SQLWKLD_STATEMENTプロシージャを使用します。このプロシージャは、指定したワークロード内の既存のSQL文を更新します。
このプロシージャでは、sql_idを指定してSQL文を更新できます。 この構文は、次のとおりです。
DBMS_ADVISOR.UPDATE_SQLWKLD_STATEMENT ( workload_name IN VARCHAR2, sql_id IN NUMBER, application IN VARCHAR2 := NULL, action IN VARCHAR2 := NULL, priority IN NUMBER := NULL, username IN VARCHAR2 := NULL); DBMS_ADVISOR.UPDATE_SQLWKLD_STATEMENT ( workload_name IN VARCHAR2, search IN VARCHAR2, updated OUT NUMBER, application IN VARCHAR2 := NULL, action IN VARCHAR2 := NULL, priority IN NUMBER := NULL, username IN VARCHAR2 := NULL);
次の例では、IDが10の文の優先順位を3に変更します。
EXECUTE DBMS_ADVISOR.UPDATE_SQLWKLD_STATEMENT('MYWORKLOAD', 10, priority=>3);
UPDATE_SQLWKLD_STATEMENTプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
ワークロードまたは実行可能なその他の操作には、次のようなものがあります。
UPDATE_SQLWKLD_ATTRIBUTESは、ワークロード・オブジェクトまたはテンプレートの様々な属性を変更します。これには、ワークロードの説明、ワークロードがテンプレートであるか読取り専用であるかなどの属性があります。 この構文は、次のとおりです。
DBMS_ADVISOR.UPDATE_SQLWKLD_ATTRIBUTES ( workload_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);
次の例では、MYWORKLOADワークロードを読取り専用に変更します。
EXECUTE DBMS_ADVISOR.UPDATE_SQLWKLD_ATTRIBUTES ( - 'MYWORKLOAD', read_only=> 'TRUE');
UPDATE_SQLWKLD_ATTRIBUTESプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
RESET_SQLWKLDプロシージャは、ワークロードを初期の開始ポイントにリセットします。これには、ワークロード・データに影響を与えずに、すべてのジャーナルおよびログ・メッセージの削除や不安定統計の再計算を実行できる利点があります。このプロシージャは、SQL文の追加や削除などのワークロード調整の後に実行する必要があります。次の例では、MYWORKLOADワークロードをリセットします。
EXECUTE DBMS_ADVISOR.RESET_SQLWKLD('MYWORKLOAD');
RESET_SQLWKLDプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
タスクまたはワークロードがそれぞれワークロードまたはタスクにリンクされている場合、これらを削除する前に、DELETE_SQLWKLD_REFプロシージャを使用して、タスクとワークロード間のリンクを削除する必要があります。次の例では、MYTASKタスクとMYWORKLOAD SQLワークロード間のリンクを削除します。
EXECUTE DBMS_ADVISOR.DELETE_SQLWKLD_REF('MYTASK', 'MYWORKLOAD');
ワークロードが必要なくなった場合、DELETE_SQLWKLDプロシージャを使用してこれらを削除できます。すべてのワークロードまたは特定の収集を削除できますが、ワークロードがタスクにリンクされている場合は削除できません。
次のプロシージャは、特定のワークロードの削除例です。ここでは、リポジトリから既存のワークロードを削除します。
DBMS_ADVISOR.DELETE_SQLWKLD (workload_name IN VARCHAR2); EXECUTE DBMS_ADVISOR.DELETE_SQLWKLD('MYWORKLOAD');
DELETE_SQLWKLDプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
この項では、推奨事項の処理に関する次の側面について説明します。
推奨事項を生成する前に、SET_TASK_PARAMETERプロシージャを使用してタスクのパラメータを最初に定義する必要があります。パラメータを定義しない場合、デフォルト値が使用されます。
タスクのパラメータを設定するには、SET_TASK_PARAMETERプロシージャを使用します。この構文は、次のとおりです。
DBMS_ADVISOR.SET_TASK_PARAMETER ( task_name IN VARCHAR2, parameter IN VARCHAR2, value IN [VARCHAR2 | NUMBER]);
タスク・パラメータは多数存在するため、関連するパラメータを識別しやすいように、これらのカテゴリ分けした表を表17-2に示します。
次の例では、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アクセス・アドバイザは、指定されたワークロードが使用するアクセス構造についてのみコメントします。たとえば、問題解決の実行では、新しい索引の作成やマテリアライズド・ビュー・ログへの新しい列の追加などが推奨されますが、評価のみのシナリオでは、索引の保持やマテリアライズド・ビューの保持などの推奨事項のみが生成されます。評価を行う場合、アクセス方法の調整は考慮されません。評価では、既存のアクセス方法構造の参照や、指定されたワークロードによるこれらの使用方法の参照のみを行います。
推奨事項を生成するには、タスク名とともに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アクセス・アドバイザでは、次のタイプのアクションを生成します。
CREATE|DROP|RETAIN MATERIALIZED VIEW
CREATE|ALTER|RETAIN MATERIALIZED VIEW LOG
CREATE|DROP|RETAIN INDEX
GATHER STATS
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にあります。その他すべての属性は、アクションごとに異なります。
表17-3では、SQLアクセス・アドバイザのアクション情報をDBA_ADVISOR_ACTIONS内の該当する列にマップします。
次の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アクセス・アドバイザにより、分析に関して役に立つ情報がジャーナルに保存されます。ジャーナルを表示するには、USER_ADVISOR_JOURNALビューを使用します。情報の出力量は、JOURNALINGタスク・パラメータの設定に応じて異なります。
ワークロードのインポート時には、様々な情報メッセージがSQLワークロードのジャーナルに記録されます。これらのジャーナルを表示するには、USER_ADVISOR_SQLW_JOURNALを使用します。ジャーナルは、文がフィルタによってワークロードから除外された理由を特定するときに役に立ちます。たとえば、特定のSQL文が無効な表や統計が欠落している表を参照している場合や、特定のSQL文に権限エラーがある場合、この情報がジャーナルに記録されます。情報の出力量は、JOURNALINGパラメータの設定によって制御されます。
ジャーナルへの記録をオフにするには、次の文を発行します。
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER('MYTASK', 'JOURNALING', 0);
ワークロードをインポートする前にジャーナルへの記録をオフにするには、次の文を発行します。
EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER('MYWORKLOAD', 'JOURNALING', 0);
情報メッセージのみを表示するには、次の文を発行します。
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER('MYTASK', 'JOURNALING', 4);
致命的メッセージのみを表示するには、次の文を発行します。
EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER('MYWORKLOAD', 'JOURNALING', 1);
ジャーナル内の情報は診断のみを目的としており、将来のリリースでは変更される場合があります。ジャーナル内の情報は、アプリケーション内で使用しないでください。
JOURNALINGパラメータのすべての設定の詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
SQLアクセス・アドバイザがEXECUTE_TASKプロシージャを使用して推奨を行う時間が長すぎる場合、これを停止できます。停止するには、CANCEL_TASKプロシージャをコールし、この推奨プロセスのtask_nameを渡します。CANCEL_TASKを使用すると、推奨は行われません。また、推奨プロセスに割り込むには、INTERRUPT_TASKプロシージャを使用します。
INTERRUPT_TASKプロシージャを使用すると、アドバイザ操作は通常の終了に達したものとして終了されます。これにより、ユーザーには、割込みポイントまでに形成された推奨事項が表示されます。
割り込まれたタスクは再開できません。 この構文は、次のとおりです。
DBMS_ADVISOR.INTERRUPT_TASK (task_name IN VARCHAR2);
次に、このプロシージャの使用例を示します。
EXECUTE DBMS_ADVISOR.INTERRUPT_TASK ('MY_TASK');
CANCEL_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');
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パラメータには、次の値を使用できます。
次の例では、推奨事項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）を制御するタスク・パラメータは4つあります。
次の例は、推奨事項のスクリプトが含まれる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 V10.1.0.0.0 - Production Rem Rem Username: SH Rem Task: MYTASK Rem Execution date: 15/04/2005 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; /
RESET_TASKプロシージャは、タスクを初期の開始ポイントにリセットします。これにより、すべての推奨事項と中間データがタスクから削除されます。実際のタスクのステータスは、INITIALに設定されます。 この構文は、次のとおりです。
DBMS_ADVISOR.RESET_TASK (task_name IN VARCHAR2);
次に、このプロシージャの使用例を示します。
EXECUTE DBMS_ADVISOR.RESET_TASK('MYTASK');
RESET_TASKプロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
単一のSQL文のみをチューニングする場合、QUICK_TUNEプロシージャでは、task_nameとSQL文を入力できます。次に、タスクとワークロードが作成され、このタスクが実行されます。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アクセス・アドバイザでは、表17-4に示されている定数を使用できます。
この項では、SQLアクセス・アドバイザの一般的な使用例について説明します。Oracle Databaseでは、この章の例を含むaadvdemo.sqlというスクリプトが提供されています。
次の例では、ユーザー定義表SH.USER_WORKLOADからワークロードがインポートされます。次に、MYTASKというタスクが作成され、記憶域上限が100 MBに設定されて、タスクが実行されます。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); VARIABLE saved_stmts NUMBER; VARIABLE failed_stmts NUMBER;
EXECUTE :workload_name := 'MYWORKLOAD'; EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name);
EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_USER (:workload_name, 'APPEND', 'SH', - 'USER_WORKLOAD', :saved_stmts, :failed_stmts); PRINT :saved_stmts; PRINT :failed_stmts;
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, 'EXECUTION_TYPE', 'INDEX_ONLY');
EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_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%');
VARIABLE workload_name VARCHAR2(255); VARIABLE saved_stmts NUMBER; VARIABLE failed_stmts NUMBER; EXECUTE :workload_name := 'MYWORKLOAD'; EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name);
EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_USER ( - :workload_name, 'APPEND', 'SH', 'USER_WORKLOAD', :saved_stmts,:failed_stmts);
EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_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キャッシュからワークロードを収集する方法を示します。まず、一連のSQL文でキャッシュをロードします。次に、いくつかのフィルタを設定してそれらの文のサブセットのみを選択し、SQLアクセス・アドバイザのワークロードにインポートします。その後、ワークロードを使用して、推奨事項を生成します。
次の文が実行され、SQLキャッシュにロードされます。
CONNECT / AS SYSDBA --Clear any prior contents of the cache. ALTER SYSTEM FLUSH SHARED_POOL; CONNECT SH/SH; 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; -- Order by 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; -- Queries to illustrate filtering CONNECT scott/tiger; SELECT e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno; SELECT COUNT(*) FROM dept; CONNECT sh/sh VARIABLE task_id NUMBER; VARIABLE task_name VARCHAR2(255); VARIABLE workload_name VARCHAR2(255); VARIABLE saved_stmts NUMBER; VARIABLE failed_stmts NUMBER;
EXECUTE :workload_name := 'MY_CACHE_WORKLOAD'; EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name);
SH表を含むSQL文のみのロード
EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER ( - :workload_name, 'USERNAME_LIST', 'SH');
EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE ( - :workload_name, 'APPEND', 2, :saved_stmts, :failed_stmts); PRINT :saved_stmts; PRINT :failed_stmts; --See the workload statements in catalog views SELECT num_select_stmt, create_date FROM user_advisor_sqlw_sum WHERE workload_name = :workload_name; SELECT sql_id, username, optimizer_cost, SUBSTR(sql_text, 1, 30) FROM user_advisor_sqlw_stmts WHERE workload_name = :workload_name ORDER BY sql_id;
EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT (:workload_name, username => 'SH', - priority => 1, executions => 10, sql_text => - 'select count(*) from customers where cust_state_province=''CA'''); SELECT num_select_stmt, create_date FROM user_advisor_sqlw_sum WHERE workload_name = :workload_name;
EXECUTE :task_name := 'MYTASK'; EXECUTE DBMS_ADVISOR.CREATE_TASK ('SQL Access Advisor', :task_id, :task_name);
EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_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', 'Example3_script.sql');
この例では、SQLアクセス・アドバイザを使用して、既存の索引およびマテリアライズド・ビューの使用率を評価する方法を示します。 「ユーザー定義のワークロードの推奨事項」に記載されているように、ワークロードをUSER_WORKLOAD表にロードするとします。（特定のワークロードによって）現在使用されている索引およびマテリアライズド・ビューは、SQLアクセス・アドバイザの推奨事項でRETAINアクションとして表示されます。
CONNECT SH/SH; VARIABLE task_id NUMBER; VARIABLE task_name VARCHAR2(255); VARIABLE workload_name VARCHAR2(255); VARIABLE saved_stmts NUMBER; VARIABLE failed_stmts NUMBER;
EXECUTE :workload_name := 'MYWORKLOAD'; EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name);
EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_USER ( - :workload_name, 'APPEND', 'SH','USER_WORKLOAD', :saved_stmts, :failed_stmts); PRINT :saved_stmts; PRINT :failed_stmts;
EXECUTE :task_name := 'MY_EVAL_TASK'; EXECUTE DBMS_ADVISOR.CREATE_TASK ('SQL Access Advisor', :task_id, :task_name);
EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_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つの出力結果（マテリアライズド・ビューの実装とマテリアライズド・ビュー作成操作の取消し）が生成されます。この2つの出力結果は、Oracleのビューからアクセスできる他、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プロシージャでは、次の複雑なクエリー構造を持つ問合せ定義がサポートされます。
ENABLE QUERY REWRITE句を指定した場合、TUNE_MVIEWでは、REFRESH FASTと同様のプロセスを使用して文が修正され、最大限の高度なクエリー・リライトが可能となるようにマテリアライズド・ビューが再定義されます。
TUNE_MVIEWプロシージャでは、実行可能文として、2つの出力結果が生成されます。1つの出力結果（IMPLEMENTATION）は、マテリアライズド・ビューと高速リフレッシュやクエリー・リライトを可能にするために必要なコンポーネント（マテリアライズド・ビュー・ログやリライト同値化）を最大限実装するためのものです。もう1つの出力結果（UNDO）は、不要と判断した場合にマテリアライズド・ビューとリライト同値化を削除するためのものです。
IMPLEMENTATIONプロセスの出力文には、次のものがあります。
CREATE MATERIALIZED VIEW LOG文: 高速リフレッシュに必要で、欠落しているマテリアライズド・ビュー・ログを作成します。
ALTER MATERIALIZED VIEW LOG FORCE文: マテリアライズド・ビュー・ログに関する要件（高速リフレッシュに必要で、欠落しているフィルタ列やシーケンスなど）を修正します。
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アクセス・アドバイザのリポジトリ表に出力され、Oracleビューである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プロシージャの使用例を次にいくつか示します。
この例では、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;
SELECT STATEMENT FROM USER_TUNE_MVIEW WHERE TASK_NAME= :task_cust_mv AND SCRIPT_TYPE='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');
この例では、クエリー・リライトでサポートされない集合演算子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文は、古い問合せ定義を、新しいトップレベルのマテリアライズド・ビューの問合せ定義に結合します。これによって、クエリー・リライトで新しいトップレベルのマテリアライズド・ビューを使用して問合せに応答できるようにします。
SELECT * FROM USER_TUNE_MVIEW WHERE TASK_NAME='cust_mv2' AND SCRIPT_TYPE='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 :task_cust_mv := 'cust_mv3'; 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');
|
 Copyright © 2000, 2008, Oracle Corporation. All Rights Reserved. |
|