1. SQLチューニング・ガイド
  2. 自動SQLチューニング
  3. SQLアクセス・アドバイザを使用したアクセス・パスの最適化

26 SQLアクセス・アドバイザを使用したアクセス・パスの最適化

SQLアクセス・アドバイザは、どの索引、マテリアライズド・ビュー、マテリアライズド・ビュー・ログまたはパーティションを作成、削除または保持するのが適切かを提示することによって、SQLのパフォーマンスの問題を識別し、解決を支援する診断ソフトウェアです。

この章のトピックは、次のとおりです:

26.1 SQLアクセス・アドバイザについて

SQLアクセス・アドバイザは、SQLチューニング・セットを含む複数のソースから入力を受け入れ、推奨事項を発行します。

ノート:

SQLアクセス・アドバイザをプラガブル・データベースとともに使用すると、データの可視性と権限の要件が一致しない場合があります。

この項では、次の項目について説明します。

関連項目:

コンテナ・データベース(CDB)内での管理機能の動作をまとめた表が記載されている『Oracle Database管理者ガイド』を参照してください

26.1.1 SQLアクセス・アドバイザの目的

SQLアクセス・アドバイザは、指定のワークロードに関するマテリアライズド・ビュー、マテリアライズド・ビュー・ログ、パーティションおよび索引の適切なセットを推奨します。

データ集中型の複雑な問合せの実行時に最適なパフォーマンスを実現できるようにデータベースをチューニングする場合、マテリアライズド・ビュー、パーティションおよび索引が必要不可欠です。SQLアクセス・アドバイザは、入力として実際のワークロードを使用するか、またはスキーマから仮想ワークロードを導出します。アドバイザは、次に、高速実行パスのアクセス構造を推奨します。アドバイザには、次の利点があります。

  • 専門知識が必要ありません。

  • オプティマイザのルールに基づいて決定されます。

  • 単一アドバイザで、SQLアクセスのあらゆる側面をカバーします。

  • Cloud Controlでは、簡単で使いやすいGUIウィザードを提供します。

  • 推奨事項を実装するスクリプトを生成します。

関連項目:

26.1.2 SQLアクセス・アドバイザのアーキテクチャ

自動チューニング・オプティマイザは、SQLアクセス・アドバイザで使用する重要なツールです。

アドバイザは、図26-1に示すソースから入力としてSQL文を受け入れ、オプティマイザを使用してこれらの文を分析して推奨できます。

図26-1は、SQLアクセス・アドバイザの基本アーキテクチャを示しています。

図26-1 SQLアクセス・アドバイザのアーキテクチャ

図26-1の説明は次にあります
「図26-1 SQLアクセス・アドバイザのアーキテクチャ」の説明

この項では、次の項目について説明します。

関連項目:

「自動チューニング・オプティマイザについて」

26.1.2.1 SQLアクセス・アドバイザへの入力

SQLアクセス・アドバイザでは、1つ以上のSQL文と、各文を完全に説明する統計情報および属性で構成されているワークロードが必要です。

全ワークロードには、対象となるビジネス・アプリケーションのすべてのSQL文が含まれます。部分ワークロードには、SQL文のサブセットが含まれます。

図26-1のように、SQLアクセス・アドバイザの入力は、次の複数のソースから取り込むことができます。

  • 共有SQL領域

    データベースでは、共有SQL領域を使用して、V$SQLに現在ある新しいSQL文を分析します。

  • SQLチューニング・セット

    SQLチューニング・セット(STS)は、SQL文とその実行コンテキストを格納するデータベース・オブジェクトです。SQL文のセットを入力として使用する場合、最初にSTSを作成し、使用する必要があります。

    ノート:

    最良の結果を得るには、ワークロードをSQLチューニング・セットとして提供します。DBMS_SQLTUNEパッケージに提供されているヘルパー・ファンクションにより、一般的なワークロード・ソース(SQLキャッシュ、表に格納されたユーザー定義のワークロード、仮想ワークロードなど)からSQLチューニング・セットを作成できます。

  • 仮想ワークロード

    ディメンションと制約を分析することで、スキーマから仮想ワークロードを作成できます。このオプションは、アプリケーションの初期の設計時に役立ちます。

関連項目:

26.1.2.2 SQLアクセス・アドバイザのフィルタ・オプション

ワークロードにフィルタを適用して分析対象を制限できます。

たとえば、オプティマイザ・コストに基づいて、ワークロードの上位30のリソース集中型文のみを確認するようアドバイザを指定します。こうして制限することで、様々なワークロード・シナリオに基づいた推奨事項のセットを生成できます。

SQLアクセス・アドバイザ・パラメータは、ワークロードの推奨プロセスとカスタマイズを制御します。これらのパラメータは、必要な推奨事項のタイプや推奨内容のネーミング規則など、プロセスの様々な側面を制御します。

これらのパラメータを設定するには、DBMS_ADVISOR.SET_TASK_PARAMETERプロシージャを使用します。パラメータは、タスクの存続期間は設定されたままであるという点において永続的です。DBMS_ADVISOR.SET_TASK_PARAMETERを使用してパラメータ値を設定した場合、このプロシージャをもう1回コールするまで、その値は変わりません。

関連項目:

DBMS_ADVISOR.SET_TASK_PARAMETERプロシージャについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。

26.1.2.3 SQLアクセス・アドバイザ推奨事項

タスクの推奨事項は、簡単なソリューションから複雑なソリューションにまで及ぶことがあります。

アドバイザは、次のようなデータベース・オブジェクトの作成を推奨できます。

  • 索引

    SQLアクセス・アドバイザの索引の推奨事項には、ビットマップ索引、ファンクション索引およびBツリー索引が含まれます。ビットマップ索引を使用すると、多くのタイプの非定型問合せのレスポンス時間が短縮され、その他の索引付けの方法と比べて記憶域要件が軽減されます。Bツリー索引は、一意またはほぼ一意のキーに索引を付ける方法で、データ・ウェアハウスで最も一般的に使用されています。SQLアクセス・アドバイザのマテリアライズド・ビューの推奨には、通常のリライトまたはテキストの完全一致リライトでの、高速リフレッシュおよび完全リフレッシュが可能なマテリアライズド・ビューの推奨が含まれます。

  • マテリアライズド・ビュー

    SQLアクセス・アドバイザでは、TUNE_MVIEWプロシージャを使用することで、高速リフレッシュが可能で、通常のクエリー・リライトを活用できるマテリアライズド・ビューの最適化の方法も推奨されます。

  • マテリアライズド・ビュー・ログ

    マテリアライズド・ビュー・ログは、マテリアライズド・ビューのマスター・サイトまたはマスター・マテリアライズド・ビュー・サイトの表で、マスター表またはマスター・マテリアライズド・ビューに対するDML変更をすべて記録します。マテリアライズド・ビューの高速リフレッシュは、マテリアライズド・ビューのマスターにマテリアライズド・ビュー・ログがある場合にのみ可能です。

  • パーティション

    SQLアクセス・アドバイザは、既存のパーティション化されていない実表のパーティション化によるパフォーマンスの改善を推奨する場合があります。さらに、パーティション化された新しい索引およびマテリアライズド・ビューを推奨することもあります。

    パーティション化された新しい索引およびマテリアライズド・ビューの作成は、パーティション化されていない場合と同じですが、既存の実表のパーティション化は慎重に実行する必要があります。表に索引、ビュー、制約またはトリガーが定義されている場合は、特に慎重に行う必要があります。

推奨する際に、SQLアクセス・アドバイザは、ディメンション・レベルの列、JOIN KEY列およびファクト表のキー列における表および索引のカーディナリティに関する構造の統計情報に依存します。DBMS_STATSパッケージを使用して、正確な統計または見積り統計を収集できます。

統計の収集は時間のかかる処理であり、完全な統計精度は必要ないため、通常は統計を見積ることをお薦めします。特定の表に関する統計情報を収集しない場合、その表を参照する問合せはワークロードで無効とマークされ、それらの問合せについては推奨事項は生成されません。また、既存のすべての索引とマテリアライズド・ビューを分析しておくこともお薦めします。

関連項目:

26.1.2.4 SQLアクセス・アドバイザ・アクション

一般に、各推奨事項は、一連の問合せに対して利点をもたらします。

利点を最大限に得るには、推奨事項の個々のアクションをすべてまとめて実装する必要があります。複数の推奨事項でアクションを共有できます。

たとえば、CREATE INDEX文は複数の問合せに対して利点をもたらしますが、それらの問合せの一部は、他のCREATE MATERIALIZED VIEW文から利点を得ることがあります。その場合、アドバイザは2つの推奨事項を生成します。索引のみを必要とする問合せセット用と、索引とマテリアライズド・ビューの両方を必要とする問合せセット用です。

この項では、次の項目について説明します。

26.1.2.4.1 アクションのタイプ

SQLアクセス・アドバイザは、様々なタイプの推奨を作成します。

推奨には次のタイプのアクションが含まれます。

  • PARTITION BASE TABLE

    このアクションは、既存のパーティション化されていない実表をパーティション化します。

  • CREATE|DROP|RETAIN {MATERIALIZED VIEW|MATERIALIZED VIEW LOG|INDEX}

    CREATEアクションは、新しいアクセス構造に対応します。RETAINは、既存のアクセス構造の維持を推奨します。SQLアクセス・アドバイザは、WORKLOAD_SCOPEパラメータがFULLに設定されている場合、DROPのみを推奨します。

  • GATHER STATS

    このアクションは、DBMS_STATSプロシージャへのコールを生成し、新しく生成されたアクセス構造に関する統計を収集します。

複数の推奨事項が同じアクションを参照する場合があります。ただし、推奨事項のスクリプトの生成では、各アクションは1回のみ表示されます。

関連項目:

26.1.2.4.2 パーティション化の推奨事項の解釈のガイドライン

SQLアクセス・アドバイザは、実表のパーティション化によってパフォーマンスが向上すると判断した場合、その表を参照する問合せが含まれるすべての推奨事項にパーティション・アクションを追加します。このように、索引およびマテリアライズド・ビューの推奨事項が、適切にパーティション化された表に実装されます。

SQLアクセス・アドバイザは、既存のパーティション化されていない実表のパーティション化を推奨することがあります。アドバイザの実装スクリプトにパーティション推奨事項が含まれる場合は、次の問題に注意してください。

  • 既存の表のパーティション化は複雑で広範な作業であるため、新しい索引やマテリアライズド・ビューを実装するよりも非常に時間がかかる場合があります。この推奨事項の実装には、時間を十分に取っておく必要があります。

  • 索引およびマテリアライズド・ビューの推奨事項は、索引やビューを削除すれば簡単に元に戻せますが、表をパーティション化すると簡単には元の状態に戻せません。このため、パーティション化の推奨が含まれるスクリプトを実行する前に、データベースのバックアップが必要です。

  • 実表を再パーティション化する際に、SQLアクセス・アドバイザのスクリプトにより、元の表の一時コピーが作成されますが、その一時コピーは元の表と同じ容量の領域を使用します。そのため、再パーティション化プロセスには、再パーティション化する最も大きな表のコピーをもう1つ作成できる程度に十分な空きディスク領域が必要です。実装スクリプトを実行する前に、使用可能な領域を確保しておく必要があります。

    パーティション実装スクリプトは、索引、マテリアライズド・ビューおよび制約などの依存オブジェクトの移行を試みます。しかし、一部のオブジェクトは、自動的に移行できません。たとえば、再パーティション化された実表に対するPL/SQLストアド・プロシージャの定義は、通常では無効になり、再コンパイルが必要です。

  • アドバイザのパーティション化の推奨を実装しない場合、同じスクリプトの同じ表に対するその他のすべての推奨事項(CREATE INDEXおよびCREATE MATERIALIZED VIEWなどの推奨)は、パーティション化の推奨に依存していることに注意してください。正確な推奨を得るには、パーティション化の推奨をスクリプトから単純に削除しないでください。かわりに、パーティション化を無効にしてアドバイザを再実行します(たとえば、ANALYSIS_SCOPEパラメータの値にTABLEキーワードを含めないなど)。

関連項目:

26.1.2.5 SQLアクセス・アドバイザ・リポジトリ

SQLアクセス・アドバイザによって生成された必要情報は、データ・ディクショナリのアドバイザ・リポジトリに格納されます。

SQLアクセス・アドバイザのリポジトリには、次の利点があります。

  • SQLアクセス・アドバイザの完全なワークロードが収集されます。

  • 履歴データがサポートされます

  • データベースで管理されます。

26.1.3 SQLアクセス・アドバイザのユーザー・インタフェース

Cloud Controlで利用可能なGUIウィザードを介して、SQLアクセス・アドバイザを使用することをお薦めします。

DBMS_ADVISORパッケージを介してSQLアクセス・アドバイザを起動することもできます。この章では、APIの使用方法について説明します。

この項では、次の項目について説明します。

関連項目:

26.1.3.1 Cloud Controlを使用した「SQLアクセス・アドバイザ: 初期オプション」ページへのアクセス

Cloud Controlの「SQLアクセス・アドバイザ: 初期オプション」ページは、推奨事項を取得するプロセスを示すウィザードの開始ページです。

「SQLアクセス・アドバイザ: 初期オプション」ページにアクセスするには:

  1. 適切な資格証明を使用してCloud Controlにログインします。

  2. 「ターゲット」メニューの下で、「データベース」を選択します。

  3. データベース・ターゲットのリストで、管理対象のOracle Databaseインスタンスのターゲットを選択します。

  4. データベースの資格証明の入力を求められた場合は、実行するタスクに必要な最小限の資格証明を入力します。

  5. 「パフォーマンス」メニューから「SQL」を選択し、さらに「SQLアクセス・アドバイザ」を選択します。

    図26-2のように、SQLアクセス・アドバイザ: 初期オプション・ページが表示されます。

    図26-2 SQLアクセス・アドバイザ: 初期オプション

    図26-2の説明は次にあります
    「図26-2 SQLアクセス・アドバイザ: 初期オプション」の説明

    ほとんどのSQL計画管理タスクは、このページ、またはこのページからアクセスするページで実行できます。

関連項目:

  • 「SQLアクセス・アドバイザ: 初期オプション」ページのオプションについて学習するには、Cloud Controlの状況依存オンライン・ヘルプを参照してください。

  • Cloud Controlを使用したSQLチューニング・アドバイザの構成および実行方法を学習するには、『Oracle Database 2日でパフォーマンス・チューニング・ガイド』を参照してください。

26.1.3.2 SQLチューニング・セットへのコマンドライン・インタフェース

コマンドラインで、DBMS_ADVISORパッケージを使用してSQLチューニング・アドバイザを管理できます。

DBMS_ADVISORパッケージは、任意のPL/SQLプログラムからコール可能な分析およびアドバイザ用のファンクションおよびプロシージャの集合です。DBMS_ADVISORを使用するには、ADVISOR権限が必要です。

関連項目:

DBMS_ADVISORについて学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください

26.2 SQLアクセス・アドバイザを使用したアクセス・パスの最適化: 基本タスク

基本タスクには、STSの作成、そのロード、SQLアクセス・アドバイザ・タスクの作成およびタスクの実行が含まれます。

次の図に、SQLアクセス・アドバイザの基本ワークフローを示します。

図26-3 SQLアクセス・アドバイザの使用

図26-3の説明が続きます
「図26-3 SQLアクセス・アドバイザの使用」の説明

通常、次のステップを実行して、SQLアクセス・アドバイザを使用します。

  1. SQLチューニング・セットの作成

    SQLアクセス・アドバイザの入力ワークロード・ソースは、SQLチューニング・セットです(STS)。DBMS_SQLTUNE.CREATE_SQLSETを使用して、SQLチューニング・セットを作成します。

    このタスクについては、「SQLアクセス・アドバイザの入力としてのSQLチューニング・セットの作成」を参照してください。

  2. SQLチューニング・セットのロード

    SQLアクセス・アドバイザのパフォーマンスは、実際の使用状況に基づいたワークロードが使用可能な場合に最も高くなります。DBMS_SQLTUNE.LOAD_SQLSETを使用し、ユーザー定義のワークロードをSQLチューニング・セットに移入します。

    このタスクについては、「ユーザー定義のワークロードのSQLチューニング・セットへの移入」を参照してください。

  3. タスクの作成および構成

    タスクでは、SQLアクセス・アドバイザが分析する内容と分析結果の格納場所を定義します。タスクを作成するには、DBMS_ADVISOR.CREATE_TASKプロシージャを使用します。タスクのパラメータを定義するにはSET_TASK_PARAMETERプロシージャを使用し、タスクをSTSにリンクするにはDBMS_ADVISOR.ADD_STS_REFプロシージャを使用します。

    このタスクについては、「SQLアクセス・アドバイザ・タスクの作成および構成」を参照してください。

  4. タスクの実行

    推奨事項を生成するには、DBMS_ADVISOR.EXECUTE_TASKプロシージャを実行します。各推奨事項には、1つ以上のアクションが指定されます。たとえば、1つの推奨事項には、複数のマテリアライズド・ビュー・ログの作成、マテリアライズド・ビューの作成、およびその分析による統計の収集が含まれます。

    このタスクについては、「SQLアクセス・アドバイザ・タスクの実行」を参照してください。

  5. 推奨事項の表示

    データ・ディクショナリ・ビューを問い合せて、推奨事項を表示できます。

    このタスクについては、「SQLアクセス・アドバイザ・タスクの結果の表示」を参照してください。

  6. オプション: 推奨事項を実装するSQLスクリプトの生成および実行。

    このタスクについては、「タスク・スクリプトの生成および実行」を参照してください。

この項では、次の項目について説明します。

26.2.1 SQLアクセス・アドバイザの入力としてのSQLチューニング・セットの作成

SQLアクセス・アドバイザの入力ワークロード・ソースは、STSです。

STSは、個別のエンティティとして格納されているため、複数のアドバイザ・タスクが共有できます。DBMS_SQLTUNE.CREATE_SQLSETプロシージャを使用してSTSを作成します。

アドバイザ・タスクがSTSを参照した後は、すべてのアドバイザ・タスクからSTSに対する依存性が削除されるまで、STSを削除または変更できません。ワークロード参照が削除されるのは、親アドバイザ・タスクが削除された場合、またはアドバイザ・タスクからワークロード参照が手動で削除された場合です。

前提条件

STSを作成するユーザーには、ADMINISTER SQL TUNING SET権限が付与されている必要があります。他のユーザーが所有するSQLチューニング・セットでSQLアクセス・アドバイザを実行するには、ADMINISTER ANY SQL TUNING SET権限が必要です。

前提条件

このチュートリアルでは、次のことが前提となっています。

  • MY_STS_WORKLOADという名前のSTSを作成します。

  • shスキーマから導出されたワークロードへの入力としてこのSTSを使用します。

STSを作成するには:

  1. SQL*Plusで、ユーザーshとしてデータベースにログインします。

  2. SQL*Plus変数を設定します。

    たとえば、次のコマンドを入力します。

    SET SERVEROUTPUT ON;
VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2(255);
VARIABLE workload_name VARCHAR2(255);

  3. SQLチューニング・セットを作成します。

    たとえば、値をworkload_name変数に割り当て、STSを作成するには次のように行います。 

    EXECUTE :workload_name := 'MY_STS_WORKLOAD';
EXECUTE DBMS_SQLTUNE.CREATE_SQLSET(:workload_name, 'test purpose');

関連項目:

26.2.2 ユーザー定義のワークロードのSQLチューニング・セットへの移入

ワークロードは、1つ以上のSQL文と、各文を完全に説明する統計情報および属性で構成されます。

全ワークロードには、対象となるビジネス・アプリケーションのすべてのSQL文が含まれます。部分ワークロードには、SQL文のサブセットが含まれます。これらの違いは、全ワークロードの場合、使用されていないマテリアライズド・ビューと索引を削除するようSQLアクセス・アドバイザが推奨する点にあります。

ワークロードなしでSQLアクセス・アドバイザを使用することはできません。SQLアクセス・アドバイザは特定の統計、ビジネスの重要性、またはその2つの組合せに応じてエントリにランクを付けます。これにより、アドバイザは最も重要なSQL文を先に処理できるようになります。

SQLアクセス・アドバイザのパフォーマンスは、実際の使用状況に基づいたワークロードを使用する場合に最も高くなります。複数のワークロードをSQLチューニング・セットとして保存することにより、長期およびデータベース・インスタンスの起動から停止までの全ライフサイクルにわたって、実際のデータ・ウェアハウスまたはOLTP環境の様々な使用状況を参照できます。

次の表は、ユーザー定義のワークロードをSTSに移入するために使用できるプロシージャを説明しています。

表26-1 STSのロードのプロシージャ

プロシージャ 説明 さらに学習するには

DBMS_SQLTUNE.LOAD_SQLSET

選択したSQLのセットをSQLチューニング・セットに移入します。このプロシージャを複数回コールすると、新しいSQL文の追加または既存の文の属性の置換を行うことができます。

 Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス

DBMS_ADVISOR.COPY_SQLWKLD_TO_STS

SQLワークロード・データをユーザーが指定したSQLチューニング・セットにコピーします。ユーザーには、SQLチューニング・セットの権限とADVISOR権限が必要です。

Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス

前提条件

このチュートリアルでは、次の操作を行うものとします。

ユーザー定義ワークロードをSTSに移入するには:

  1. ユーザーshとしてSQL*Plusをデータベースに接続して、user_workload表を作成します。

    たとえば、次のコマンドを入力します。

    DROP TABLE user_workload;
CREATE TABLE user_workload
(
  username             varchar2(128),   /* User who executes statement */
  module               varchar2(64),        /* Application module name */
  action               varchar2(64),        /* Application action name */
  elapsed_time         number,               /* Elapsed time for query */
  cpu_time             number,                   /* CPU time for query */
  buffer_gets          number,        /* Buffer gets consumed by query */
  disk_reads           number,         /* Disk reads consumed by query */
  rows_processed       number,         /* # of rows processed by query */
  executions           number,            /* # of times query executed */
  optimizer_cost       number,             /* Optimizer cost for query */
  priority             number,             /* User-priority (1,2 or 3) */
  last_execution_date  date,               /* Last time query executed */
  stat_period          number,          /* Window exec time in seconds */
  sql_text             clob                           /* Full SQL Text */
);

  2. 問合せに関する情報をuser_workload表にロードします。

    たとえば、次の文を実行します。

    -- 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')
/
 
-- 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;

  3. PL/SQLプログラムを実行して、user_workload表の行でカーソルを埋めて、このカーソルの内容をMY_STS_WORKLOADという名前のSTSにロードします。

    たとえば、次のPL/SQLプログラムを実行します。

    DECLARE
  sqlset_cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN sqlset_cur FOR
    SELECT SQLSET_ROW(null,null, SQL_TEXT, null, null, 'SH', module,
                     'Action', 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, null, 2, 3,
                     sysdate, 0, 0, null, 0, null, null)
    FROM USER_WORKLOAD;
  DBMS_SQLTUNE.LOAD_SQLSET('MY_STS_WORKLOAD', sqlset_cur);
END;
/

26.2.3 SQLアクセス・アドバイザ・タスクの作成および構成

DBMS_ADVISOR.CREATE_TASKプロシージャを使用して、SQLアクセス・アドバイザ・タスクを作成します。

SQLアクセス・アドバイザでは、アドバイザが分析する内容と分析結果の格納場所を定義します。ユーザーは、それぞれが特化した複数のタスクを作成できます。これらすべてのタスクは同じアドバイザ・タスク・モデルに基づいており、同じリポジトリを共有します。

タスクを構成するステップは次のとおりです。

  • タスク・パラメータの定義

    推奨事項の生成時に、ワークロードにフィルタを適用して分析対象を制限できます。こうして制限することで、様々なワークロード・シナリオに基づいた推奨事項のセットを生成できます。

    SQLアクセス・アドバイザ・パラメータは、ワークロードの推奨プロセスとカスタマイズを制御します。これらのパラメータは、必要な推奨事項のタイプや推奨内容のネーミング規則など、プロセスの様々な側面を制御します。

    パラメータの定義がない場合、データベースはデフォルトを使用します。タスクのパラメータを設定するには、DBMS_ADVISOR.SET_TASK_PARAMETERプロシージャを使用します。パラメータは、タスクの存続期間は設定されたままであるという点において永続的です。SET_TASK_PARAMETERを使用してパラメータ値を設定した場合、このプロシージャをもう1回コールするまで、その値は変わりません。

  • ワークロードへのタスクのリンク

    ワークロードは独立しているため、DBMS_ADVISOR.ADD_STS_REFプロシージャを使用してタスクにリンクする必要があります。このリンクが確立された後は、すべてのアドバイザ・タスクからワークロードに対する依存性が削除されるまで、ワークロードを削除または変更できません。ユーザーが親アドバイザ・タスクを削除するか、DBMS_ADVISOR.DELETE_STS_REFプロシージャを使用してタスクからワークロード参照を手動で削除すると、ワークロード参照は削除されます。

前提条件

タスクを作成するユーザーには、ADVISOR権限が付与されている必要があります。

前提条件

このチュートリアルでは、次のことが前提となっています。

SQLアクセス・アドバイザ・タスクを作成および構成するには:

  1. ユーザーshとしてSQL*Plusをデータベースに接続して、タスクを作成します。

    たとえば、次のコマンドを入力します。

    EXEC :task_name := 'MYTASK';
EXEC DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', :task_id, :task_name);

  2. タスク・パラメータを設定します。

    たとえば、次の文を実行します。

    EXEC DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, 'TIME_LIMIT', 30);
EXEC DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, 'ANALYSIS_SCOPE', 'ALL');

  3. ワークロードへタスクをリンクします。

    たとえば、次の文を実行します。

    EXECUTE DBMS_ADVISOR.ADD_STS_REF(:task_name, 'SH', :workload_name);

関連項目:

26.2.4 SQLアクセス・アドバイザ・タスクの実行

DBMS_ADVISOR.EXECUTE_TASKプロシージャにより、指定したタスクがSQLアクセス・アドバイザによって分析または評価されます。

タスクの実行は同期処理であるため、処理が完了するか、ユーザーによる割込みが検出されるまで、制御はユーザーに戻されません。タスクが戻されるか実行されると、実行ステータスについてDBA_ADVISOR_LOG表をチェックできます。

推奨事項を生成するには、EXECUTE_TASKを実行します。推奨事項には、マテリアライズド・ビュー・ログまたはマテリアライズド・ビューの作成などの1つ以上のアクションが含まれます。

前提条件

ワークロードを処理する際、SQLアクセス・アドバイザは表と列の参照を識別するために各文の検証を試みます。検証では、本来のユーザーが文を実行する場合と同様に各文が処理されます。

ユーザーに特定の表に対するSELECT権限がない場合、SQLアクセス・アドバイザはその表を参照している文をバイパスします。この動作によって、多くの文が分析から除外される可能性があります。SQLアクセス・アドバイザがワークロードのすべての文を除外した場合、ワークロードは無効になります。SQLアクセス・アドバイザは、次のメッセージを戻します。 

QSM-00774, there are no SQL statements to process for task TASK_NAME

重要なワークロード問合せが欠落しないようにするには、現在のデータベース・ユーザーが、マテリアライズド・ビューの分析対象の表に対するSELECT権限を持っている必要があります。これらの表では、ロールを通じてこれらのSELECT権限を取得することはできません。

前提条件

このチュートリアルでは、「SQLアクセス・アドバイザ・タスクの作成および構成」で構成されたタスクを実行するものとします。

SQLアクセス・アドバイザ・タスクを作成および構成するには:

  1. SQL*PlusまたはSQL Developerで、必要な権限を持つユーザーとしてデータベースにログインします。

  2. タスクを実行します。

    たとえば、次の文を実行します。

    EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);

  3. オプションで、USER_ADVISOR_LOGを問い合せて、タスクのステータスを確認できます。

    たとえば、次の文を実行します(出力例も示します)。

    COL TASK_ID FORMAT 999
COL TASK_NAME FORMAT a25
COL STATUS_MESSAGE FORMAT a25

SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE 
FROM   USER_ADVISOR_LOG;

TASK_ID TASK_NAME                 STATUS      STATUS_MESSAGE
------- ------------------------- ----------- -------------------------
    103 MYTASK                    COMPLETED   Access advisor execution
                                              completed

関連項目:

EXECUTE_TASKプロシージャとそのパラメータについてさらに学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください

26.2.5 SQLアクセス・アドバイザ・タスクの結果の表示

SQLアクセス・アドバイザにより生成された各推奨事項は、いくつかのデータ・ディクショナリ・ビューを使用して表示できます。

ビューについては、表26-2を参照してください。ただし、DBMS_ADVISOR.GET_TASK_SCRIPTプロシージャまたはCloud Controlを使用する方が簡単です。この場合、推奨事項がグラフィック表示され、推奨事項の利点を得るSQL文を簡単に参照するためのハイパーリンクが用意されています。

SQLアクセス・アドバイザによって生成された各推奨事項は、その利点を得るSQL文にリンクされています。各推奨事項には、1つ以上のアクションが対応します。各アクションには、1つ以上の属性があります。

各アクションには、アクセス構造のプロパティに関連する複数の属性があります。適用可能な各アクセス構造の名前と表領域は、USER_ADVISOR_ATTRIBUTESのATTR1およびATTR2列にあります。新しい各アクセス構造が占有する領域は、NUM_ATTR1列にあります。その他の属性は、アクションごとに異なります。

表26-2 タスクの結果の表示ビュー

データ・ディクショナリ・ビュー(DBA、USER) 説明

DBA_ADVISOR_TASKS

アドバイザ・タスクに関する情報を示します。SQLアクセス・アドバイザ・タスクを参照するには、ADVISOR_NAME = 'SQL Access Advisor'の場所を選択します。

DBA_ADVISOR_RECOMMENDATIONS

データベース内のすべての推奨項目の分析結果を示します。推奨項目には、複数のアクションを対応付けることができます。このアクションについては、DBA_ADVISOR_ACTIONSビューを参照してください。推奨項目は、その正当な理由を表す一連の根拠も示します。この根拠については、DBA_ADVISOR_RATIONALEビューを参照してください。

DBA_ADVISOR_ACTIONS

データベース内のすべての推奨項目に関連付けられたアクションに関する情報を示します。各アクションは、COMMAND列およびATTR1からATTR6列で指定されます。各コマンドは、属性列の使用方法を定義します。

DBA_ADVISOR_RATIONALE

データベース内のすべての推奨項目の根拠に関する情報を示します。

DBA_ADVISOR_SQLA_WK_STMTS

SQLアクセス・アドバイザの分析後、データベース内のすべてのワークロード・オブジェクトに関する情報を示します。事前コストと事後コストの数は、推奨されたアクセス構造がある場合とない場合とで見積られたオプティマイザのコスト(EXPLAIN PLANを参照)に関するものです。

前提条件

このチュートリアルでは、「SQLアクセス・アドバイザ・タスクの実行」で実行されたタスクの結果を表示するものとします。

SQLアクセス・アドバイザ・タスクの結果を表示するには:

  1. 適切な権限でSQL*Plusをデータベースに接続して、アドバイザの推奨事項を問い合せます。

    たとえば、次の文を実行します(出力例も示します)。

    VARIABLE workload_name VARCHAR2(255); 
VARIABLE task_name VARCHAR2(255);
EXECUTE :task_name := 'MYTASK';
EXECUTE :workload_name := 'MY_STS_WORKLOAD'; 

SELECT REC_ID, RANK, BENEFIT
FROM   USER_ADVISOR_RECOMMENDATIONS 
WHERE  TASK_NAME = :task_name
ORDER BY RANK;

    REC_ID       RANK    BENEFIT
---------- ---------- ----------
         1          1        236
         2          2        356

    前述の出力は、SQLアクセス・アドバイザの実行によって生成された推奨事項(rec_id)とそのランクおよび全利点を示しています。ランクとは、推奨事項がサポートする問合せの重要性の尺度です。利点とは、推奨事項を使用したすべての問合せの実行コスト(オプティマイザのコストの観点から)における改善結果です。

  2. 推奨事項が役立つ問合せを識別します。

    たとえば、次のUSER_ADVISOR_SQLA_WK_STMTSの問合せを実行します(出力例も示します)。 

    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
ORDER BY percent_benefit DESC;

SQL_ID            REC_ID    PRECOST   POSTCOST PERCENT_BENEFIT
------------- ---------- ---------- ---------- ---------------
fn4bsxdm98w3u          2        578        222      61.5916955
29bbju72rv3t2          1       5750       5514      4.10434783
133ym38r6gbar          0        772        772               0

    事前コストと事後コストの数は、推奨されたアクセス構造の変更がある場合とない場合とで見積られたオプティマイザのコスト(EXPLAIN PLANを参照)に関するものです。

  3. この一連の推奨事項について個別アクションの数を示します。

    たとえば、次の問合せを使用します(出力例も示します)。

    SELECT 'Action Count', COUNT(DISTINCT action_id) cnt
FROM   USER_ADVISOR_ACTIONS 
WHERE  TASK_NAME = :task_name;

'ACTIONCOUNT        CNT
------------ ----------
Action Count          4

  4. この一連の推奨事項に対するアクションを示します。

    たとえば、次の問合せを使用します(出力例も示します)。

    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          1 PARTITION TABLE
         1          2 RETAIN INDEX
         2          1 PARTITION TABLE
         2          3 RETAIN INDEX
         2          4 RETAIN INDEX

  5. 推奨事項の属性を示します。

    たとえば、次のアクションの属性を参照するには、次のPL/SQLプロシージャshow_recmを作成して実行します。 

    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;
/

SET SERVEROUTPUT ON SIZE 99999
EXECUTE show_recm(:task_name);

    次の出力は、推奨事項のアクションの属性を示しています。

    =========================================
Task_name = MYTASK
Action ID: 1
Command : PARTITION TABLE
Attr1 (name)      : "SH"."SALES"
Attr2 (tablespace):
Attr3             : ("TIME_ID")
Attr4             : INTERVAL
Attr5             :
----------------------------------------
Action ID: 2
Command : RETAIN INDEX
Attr1 (name)      : "SH"."PRODUCTS_PK"
Attr2 (tablespace):
Attr3             : "SH"."PRODUCTS"
Attr4             : BTREE
Attr5             :
----------------------------------------
Action ID: 3
Command : RETAIN INDEX
Attr1 (name)      : "SH"."TIMES_PK"
Attr2 (tablespace):
Attr3             : "SH"."TIMES"
Attr4             : BTREE
Attr5             :
----------------------------------------
Action ID: 4
Command : RETAIN INDEX
Attr1 (name)      : "SH"."SALES_TIME_BIX"
Attr2 (tablespace):
Attr3             : "SH"."SALES"
Attr4             : BITMAP
Attr5             :
----------------------------------------
=========END RECOMMENDATIONS============

関連項目:

26.2.6 タスク・スクリプトの生成および実行

DBMS_ADVISOR.GET_TASK_SCRIPTプロシージャを使用して、SQLアクセス・アドバイザの推奨事項に対するSQL文のスクリプトを作成できます。スクリプトは実行可能SQLファイルで、DROP文、CREATE文およびALTER文を含めることができます。

新しいオブジェクトの場合、マテリアライズド・ビューの名前、マテリアライズド・ビュー・ログおよび索引は、ユーザー指定の名前テンプレートを使用して自動的に生成されます。生成されたSQLスクリプトは、実行する前に見直します。

前提条件

このチュートリアルでは、「SQLアクセス・アドバイザ・タスクの実行」で生成された推奨事項を含むスクリプトの保存および実行を行うものとします。

SQLスクリプトを保存および実行するには:

  1. 管理者としてSQL*Plusをデータベースに接続します。

  2. ディレクトリ・オブジェクトを作成し、読取り/書込みを行う権限を付与します。

    たとえば、次の文を使用します。

    CREATE DIRECTORY ADVISOR_RESULTS AS '/tmp';
GRANT READ ON DIRECTORY ADVISOR_RESULTS TO PUBLIC;
GRANT WRITE ON DIRECTORY ADVISOR_RESULTS TO PUBLIC;

  3. データベースにshとして接続し、ファイルにスクリプトを保存します。

    たとえば、次の文を使用します。

    EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('MYTASK'),
'ADVISOR_RESULTS', 'advscript.sql');

  4. テキスト・エディタを使用して、スクリプトの内容を表示します。

    次の例は、このプロシージャよって生成されたスクリプトのフラグメントです。

    Rem  Username:        SH
Rem  Task:            MYTASK
Rem  Execution date:
Rem
 
Rem
Rem  Repartitioning table "SH"."SALES"
Rem
 
SET SERVEROUTPUT ON
SET ECHO ON
 
Rem
Rem Creating new partitioned table
Rem
  CREATE TABLE "SH"."SALES1"
   (    "PROD_ID" NUMBER,
        "CUST_ID" NUMBER,
        "TIME_ID" DATE,
        "CHANNEL_ID" NUMBER,
        "PROMO_ID" NUMBER,
        "QUANTITY_SOLD" NUMBER(10,2),
        "AMOUNT_SOLD" NUMBER(10,2)
   ) PCTFREE 5 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS  NOLOGGING
  TABLESPACE "EXAMPLE"
PARTITION BY RANGE ("TIME_ID") INTERVAL( NUMTOYMINTERVAL( 1, 'MONTH')) 
( PARTITION VALUES LESS THAN (TO_DATE(' 1998-02-01 00:00:00', 
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) );
.
.
.

  5. オプションとして、SQL*PlusでSQLスクリプトを実行できます。

    たとえば、次のコマンドを入力します。

    @/tmp/advscript.sql

関連項目:

26.3 SQLアクセス・アドバイザ・クイック・チューニングの実行

単一のSQL文をチューニングするには、task_nameと単一のSQL文を入力として受け入れるDBMS_ADVISOR.QUICK_TUNEプロシージャを使用します。

DBMS_ADVISOR.QUICK_TUNEプロシージャは、タスクとワークロードを作成し、そのタスクを実行します。EXECUTE_TASKとQUICK_TUNEでは、同じ結果が得られます。ただし、単一のSQL文をチューニングする場合、QUICK_TUNEがより簡単です。

前提条件

このチュートリアルでは、次のことが前提となっています。

  • 単一のSQL文をチューニングします。

  • タスクにMY_QUICKTUNE_TASKという名前を付けます。

テンプレートを作成し、このテンプレートをタスクのベースとするには:

  1. ユーザーshとしてSQL*Plusをデータベースに接続して、SQL文とタスク名に対してSQL*Plus変数を初期化します。

    たとえば、次のコマンドを入力します。

    VARIABLE t_name VARCHAR2(255);
VARIABLE sq VARCHAR2(4000);
EXEC :sq := 'SELECT COUNT(*) FROM customers WHERE cust_state_province =''CA''';
EXECUTE :t_name := 'MY_QUICKTUNE_TASK';

  2. クィック・チューニングを実行します。

    たとえば、次の文はMY_QUICKTUNE_TASKを実行します。 

    EXEC DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR,:t_name,:sq);

関連項目:

QUICK_TUNEプロシージャとそのパラメータについてさらに学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください

26.4 SQLアクセス・アドバイザの使用: 詳細タスク

この項では、SQLアクセス・アドバイザを伴う高度なタスクについて説明します。

この項では、次の項目について説明します。

26.4.1 既存のアクセス構造の評価

SQLアクセス・アドバイザは、問題解決モードと評価モードの2つのモードで動作します。

SQLアクセス・アドバイザは、デフォルトでは、アクセス方法の問題を解決するために、索引構造、パーティション、マテリアライズド・ビューおよびマテリアライズド・ビュー・ログの拡張を求めます。たとえば、問題解決モードで実行すると、新しい索引の作成やマテリアライズド・ビュー・ログへの新しい列の追加などが推奨されます。

ANALYSIS_SCOPEパラメータをEVALUATIONに設定すると、SQLアクセス・アドバイザは、指定されたワークロードがどのアクセス構造を使用するかについてのみコメントします。評価のみの実行では、索引の保持やマテリアライズド・ビューの保持などの推奨のみとなります。評価モードでは、ワークロードがどの索引およびマテリアライズド・ビューを使用するのか正確に知ることができます。SQLアクセス・アドバイザは、既存の実表のパーティション化によるパフォーマンスへの影響は評価されません。

タスクを作成して評価モードに設定するには:

  1. 適切な権限でSQL*Plusをデータベースに接続して、タスクを作成します。

    たとえば、次の文を入力します。t_nameはタスクの名前に設定されたSQL*Plus変数です。 

    EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:t_name);

  2. クィック・チューニングを実行します。

    たとえば、次の文は、前述のタスクを評価モードに設定します。

    EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER(:t_name,'ANALYSIS_SCOPE','EVALUATION');

関連項目:

SET_TASK_PARAMETERプロシージャとそのパラメータについて学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください

26.4.2 SQLアクセス・アドバイザのタスク属性の更新

DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTESプロシージャを使用してタスクの属性を設定できます。

次の属性を設定できます。

  • タスク名の変更。

  • タスクの説明の付加。

  • タスクの読取り専用(変更不可)設定。

  • 他のタスクを定義できるようにタスクをテンプレート化

  • タスクやタスク・テンプレートの様々な属性の変更。

前提条件

このチュートリアルでは、次のことが前提となっています。

  • 既存のタスクMYTASKの名前をTUNING1に変更します。

  • タスクTUNING1を読取り専用にします。

タスクの属性を更新するには:

  1. ユーザーshとしてSQL*Plusをデータベースに接続して、タスクの名前を変更します。

    たとえば、次の文を使用します。

    EXECUTE DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES('MYTASK', 'TUNING1');

  2. タスクを読取り専用に設定します。

    たとえば、次の文を使用します。

    EXECUTE DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES('TUNING1', 
  read_only => 'true');

関連項目:

26.4.3 SQLアクセス・アドバイザのタスク・テンプレートの作成および使用

タスク・テンプレートは、構成を保存したもので、今後のタスクおよびワークロードのベースとなります。

タスクまたはワークロードをテンプレートとして設定すると、今後のタスクの作成で、出発点またはテンプレートとして使用できます。テンプレートを設定することにより、チューニング分析の実行時間を短縮できます。また、このアプローチにより、ビジネス・オペレーションに適合したチューニング分析が可能になります。

物理的に、タスクとテンプレートは同じです。ただし、テンプレートは実行できません。テンプレートからタスクを作成するには、新しいタスクの作成時に使用するテンプレートを指定します。このとき、SQLアクセス・アドバイザは、新しく作成されたタスクにテンプレートからデータおよびパラメータ設定をコピーします。また、既存のタスクをテンプレートとして設定するには、タスクの作成時にテンプレート属性を設定するか、後でUPDATE_TASK_ATTRIBUTEプロシージャを使用します。

次の表では、タスク・テンプレートを管理する際に使用できるプロシージャを説明します。

表26-3 タスク・テンプレートのDBMS_ADVISORプロシージャ

プロシージャ 説明

CREATE_TASK

templateパラメータは、既存のタスクまたはタスク・テンプレートのオプションのタスク名です。SQLアクセス・アドバイザの組込みテンプレートを指定するには、表26-6の説明に従って、テンプレート名を使用します。is_templateはオプションのパラメータで、新たに作成されたタスクをテンプレートとして設定できます。有効な値は、trueおよびfalseです。

SET_TASK_PARAMETER

INDEX_NAME_TEMPLATEパラメータは、新しい索引名を構成する方法を指定します。MVIEW_NAME_TEMPLATEパラメータは、新しいマテリアライズド・ビュー名を構成する方法を指定します。PARTITION_NAME_TEMPLATEパラメータは、新しいパーティション名を構成する方法を指定します。

UPDATE_TASK_ATTRIBUTES

is_templateは、タスクをテンプレートとしてマークします。物理的にタスクとテンプレートの違いはありませんが、テンプレートは実行できません。設定可能な値はtrueおよびfalseです。値がNULLか、またはADVISOR_UNUSEDが格納されている場合、設定は変更されません。

前提条件

このチュートリアルでは、次のことが前提となっています。

  • MY_TEMPLATEという名前のテンプレートを作成します。

  • MY_TEMPLATEに基づいてタスクによって推奨される索引およびマテリアライズド・ビューのネーミング規則を設定します。

  • MY_TEMPLATEに基づいてNEWTASKタスクを作成します。

テンプレートを作成し、このテンプレートをタスクのベースとするには:

  1. ユーザーshとしてSQL*Plusをデータベースに接続して、タスクをテンプレートとして作成します。

    たとえば、次のようにMY_TEMPLATEという名前のテンプレートを作成します。 

    VARIABLE template_id NUMBER;
VARIABLE template_name VARCHAR2(255);
EXECUTE :template_name := 'MY_TEMPLATE';
BEGIN 
  DBMS_ADVISOR.CREATE_TASK (
    'SQL Access Advisor'
,   :template_id
,   :template_name
,   is_template => 'true'
);
END;

  2. テンプレートのパラメータを設定します。

    たとえば、次の文は、推奨される索引およびマテリアライズド・ビューのネーミング規則を設定します。

    -- set naming conventions for recommended indexes/mvs
BEGIN 
  DBMS_ADVISOR.SET_TASK_PARAMETER ( 
    :template_name
,   'INDEX_NAME_TEMPLATE'
,   'SH_IDX$$_<SEQ>'
);
END;

BEGIN 
  DBMS_ADVISOR.SET_TASK_PARAMETER (
    :template_name
,   'MVIEW_NAME_TEMPLATE'
,   'SH_MV$$_<SEQ>'
);
END;

  3. 既存のテンプレートに基づいてタスクを作成します。

    たとえば、次のコマンドを入力して、MY_TEMPLATEに基づいたNEWTASKを作成します。 

    VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2(255);
EXECUTE :task_name := 'NEWTASK';
BEGIN 
  DBMS_ADVISOR.CREATE_TASK (
    'SQL Access Advisor'
,   :task_id
,   :task_name
,   template=>'MY_TEMPLATE'
);
END;

関連項目:

CREATE_TASKおよびSET_TASK_PARAMETERプロシージャについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください

26.4.4 SQLアクセス・アドバイザ・タスクの実行終了

SQLアクセス・アドバイザにより、推奨プロセスへの割込みまたは推奨プロセスの完了が可能になります。

割込みによって、SQLアクセス・アドバイザは処理を停止し、タスクはINTERRUPTEDとマークされます。その時点で、推奨事項の属性を更新してスクリプトを生成できます。

中間結果は、その時点までのワークロードの内容に対する推奨事項を示しています。推奨事項がワークロード全体の影響を受けやすい場合は、タスクを完了させることをお薦めします。また、推奨プロセスの初期段階でアドバイザによって作成される推奨事項には、実表のパーティション化に関する推奨は含まれません。パーティション化分析では、パーティション化が有効かどうかを決定する前に、ワークロードの大部分を処理する必要があります。したがって、SQLアクセス・アドバイザが利点を検出した場合は、より遅い段階での中間結果に実表のパーティション化に関する推奨事項が含まれます。

この項では、SQLアクセス・アドバイザ・タスクの実行を終了する2つの方法を説明します。

26.4.4.1 SQLアクセス・アドバイザ・タスクへの割込み

DBMS_ADVISOR.INTERRUPT_TASKプロシージャを使用すると、SQLアクセス・アドバイザ・タスクの実行は正常終了に達したものとして終了されます。

これにより、ユーザーには、割込みポイントまでに構成された推奨事項が表示されます。割り込まれたタスクは再開できません。構文は次のとおりです。

DBMS_ADVISOR.INTERRUPT_TASK (task_name IN VARCHAR2);

前提条件

このチュートリアルでは、次のことが前提となっています。

  • 処理時間の長いタスクMYTASKが現在実行中です。

  • このタスクに割り込んで、推奨事項を表示します。

現在実行中のタスクに割り込むには:

  1. ユーザーshとしてSQL*Plusをデータベースに接続して、タスクに割り込みます。

    たとえば、次のようにMY_TEMPLATEという名前のテンプレートを作成します。 

    EXECUTE DBMS_ADVISOR.INTERRUPT_TASK ('MYTASK');

関連項目:

INTERRUPT_TASKプロシージャについて学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください

26.4.4.2 SQLアクセス・アドバイザ・タスクの取消し

タスクの実行を停止するには、DBMS_ADVISOR.CANCEL_TASKプロシージャをコールして、この推奨プロセスのタスク名を渡します。

SQLアクセス・アドバイザがこのリクエストに応答するのに数秒かかることがあります。すべてのアドバイザ・タスクのプロシージャは同期操作であるため、操作を取り消すには、別のデータベース・セッションを使用する必要があります。CANCEL_TASKを使用した場合、SQLアクセス・アドバイザの推奨事項はありません。

取消コマンドは、取り消された操作の開始前の状態にタスクを効率的にリストアします。そのため、取り消されたタスクまたはデータ・オブジェクトは再開できません。ただし、DBMS_ADVISOR.RESET_TASKを使用してタスクをリセットすると、再実行できます。CANCEL_TASKの構文は次のとおりです。 

DBMS_ADVISOR.CANCEL_TASK (task_name   IN  VARCHAR2);

RESET_TASKプロシージャは、タスクを初期の開始ポイントにリセットします。これにより、すべての推奨事項と中間データがタスクから削除されます。タスクのステータスは、INITIALに設定されます。構文は次のとおりです。 

DBMS_ADVISOR.RESET_TASK (task_name     IN VARCHAR2);

前提条件

このチュートリアルでは、次のことが前提となっています。

  • 処理時間の長いタスクMYTASKが現在実行中です。このタスクは、推奨事項のパーティション化を行うために設定されます。

  • このタスクを取り消して、タスクが索引のみ推奨するようにリセットします。

現在実行中のタスクを取り消すには:

  1. ユーザーshとしてSQL*Plusをデータベースに接続して、タスクを取り消します。

    たとえば、次のようにMY_TEMPLATEという名前のテンプレートを作成します。 

    EXECUTE DBMS_ADVISOR.CANCEL_TASK ('MYTASK');

  2. タスクをリセットします。

    たとえば、RESET_TASKプロシージャを次のように実行します。 

    EXECUTE DBMS_ADVISOR.RESET_TASK('MYTASK');

  3. タスク・パラメータを設定します。

    たとえば、分析の有効範囲を次のようにINDEXに変更します。 

    EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, 'ANALYSIS_SCOPE', 'INDEX');

  4. タスクを実行します。

    たとえば、MYTASKを次のように実行します。 

    EXECUTE DBMS_ADVISOR.EXECUTE_TASK ('MYTASK');

関連項目:

RESET_TASKおよびCANCEL_TASKについてさらに学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください

26.4.5 SQLアクセス・アドバイザ・タスクの削除

DBMS_ADVISOR.DELETE_TASKプロシージャでは、SQLアクセス・アドバイザの既存のタスクがリポジトリから削除されます。

SQLアクセス・アドバイザ・タスクを削除する構文は次のとおりです。

DBMS_ADVISOR.DELETE_TASK (task_name  IN VARCHAR2);

タスクがSTSワークロードにリンクされている場合、タスクまたはワークロードを削除するには、DELETE_STS_REFプロシージャを使用してタスクとワークロード間のリンクを削除する必要があります。次の例では、タスクMYTASKと現在のユーザーのSQLチューニング・セットMY_STS_WORKLOADの間のリンクを削除します。 

EXECUTE DBMS_ADVISOR.DELETE_STS_REF('MYTASK', null, 'MY_STS_WORKLOAD');

前提条件

このチュートリアルでは、次のことが前提となっています。

  • ユーザーshが、現在複数のSQLアクセス・アドバイザ・タスクを所有しています。

  • MYTASKを削除します。

  • MYTASKタスクは、現在 MY_STS_WORKLOADワークロードにリンクしています。

SQLアクセス・アドバイザ・タスクを削除するには:

  1. ユーザーshとしてSQL*Plusをデータベースに接続して、SQLアクセス・アドバイザの既存のタスクを問い合せます。

    たとえば、データ・ディクショナリを次のように問い合せます(出力例も示します)。

    SELECT TASK_NAME 
FROM   USER_ADVISOR_TASKS 
WHERE  ADVISOR_NAME = 'SQL Access Advisor';
 
TASK_NAME
-------------------------
MYTASK
NEWTASK

  2. MYTASKと MY_STS_WORKLOAD間のリンクを削除します。

    たとえば、リファレンスを次のように削除します。

    EXECUTE DBMS_ADVISOR.DELETE_STS_REF('MYTASK', null, 'MY_STS_WORKLOAD');

  3. 目的のタスクを削除します。

    たとえば、MYTASKを次のように削除します。 

    EXECUTE DBMS_ADVISOR.DELETE_TASK('MYTASK');

関連項目:

DELETE_TASKプロシージャとそのパラメータについてさらに学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。

26.4.6 SQLアクセス・アドバイザ推奨事項のマーク

デフォルトでは、SQLアクセス・アドバイザのすべての推奨事項は実装可能な状態にあります。ただし、DBMS_ADVISOR.MARK_RECOMMENDATIONプロシージャを使用して、選択した推奨事項のスキップまたは除外を選択できます。

MARK_RECOMMENDATIONでは、ユーザーがREJECTまたはIGNORE設定を使用して推奨事項に注釈を付けることができます。実装プロシージャを生成する際に、GET_TASK_SCRIPTは、注釈の付けられた推奨事項をスキップします。

SQLアドバイザにより、パーティション化されていない1つ以上の実表のパーティション化が推奨された場合、その推奨事項をスキップする前に慎重に検討してください。表のパーティション化スキームを変更すると、その表に定義されたすべての問合せ、索引およびマテリアライズド・ビューのコストに影響を及ぼします。このため、パーティション化の推奨事項をスキップした場合は、その表に対してアドバイザが行ったその他の推奨も最適ではありません。パーティション化を含めずにワークロードの推奨事項を確認するには、ANALYSIS_SCOPEパラメータを変更してパーティション化の推奨を除外し、アドバイザ・タスクをリセットして、再度タスクを実行します。

構文は次のとおりです。

DBMS_ADVISOR.MARK_RECOMMENDATION (
   task_name          IN VARCHAR2
   id                 IN NUMBER,
   action             IN VARCHAR2);

前提条件

このチュートリアルでは、次のことが前提となっています。

推奨事項をマークするには:

  1. ユーザーshとしてSQL*Plusをデータベースに接続して、推奨事項をマークします。

    たとえば、1の推奨事項を次のように拒否します。 

    EXECUTE DBMS_ADVISOR.MARK_RECOMMENDATION('MYTASK', 1, 'REJECT');

    この推奨事項およびこれに依存する推奨事項は、スクリプトに表示されません。

  2. 「タスク・スクリプトの生成および実行」の説明に従って、スクリプトを生成します。

関連項目:

MARK_RECOMMENDATIONSプロシージャとそのパラメータについてさらに学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください

26.4.7 SQLアクセス・アドバイザ推奨事項の変更

UPDATE_REC_ATTRIBUTESプロシージャを使用すると、SQLアクセス・アドバイザでは、分析時に索引やマテリアライズド・ビューなどの新しいオブジェクトに名前が付けられ、所有者が割り当てられます。

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

    推奨オブジェクトの表領域を指定します。

前提条件

このチュートリアルでは、次のことが前提となっています。

推奨事項をマークするには:

  1. ユーザーshとしてSQL*Plusをデータベースに接続して、推奨事項の属性を更新します。

    たとえば、表領域の名前を次のようにSH_MVIEWSに変更します。 

    BEGIN 
  DBMS_ADVISOR.UPDATE_REC_ATTRIBUTES (
    'MYTASK'
,   1
,   1
,   'TABLESPACE'
,   'SH_MVIEWS'
);
END;

  2. 「タスク・スクリプトの生成および実行」の説明に従って、スクリプトを生成します。

関連項目:

UPDATE_REC_ATTRIBUTESプロシージャとそのパラメータについてさらに学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください

26.5 SQLアクセス・アドバイザの例

Oracle Databaseでは、テスト・データベースで実行できるSQLアクセス・アドバイザの複数の例を含むスクリプトが提供されています。

スクリプトには、ORACLE_HOME/rdbms/demo/aadvdemo.sqlという名前が付けられています。

26.6 SQLアクセス・アドバイザ・リファレンス

データ・ディクショナリ・ビューを使用して、SQLアクセス・アドバイザに関するメタデータにアクセスできます。

この項では、次の項目について説明します。

26.6.1 DBA_ADVISOR_ACTIONSビューのアクション属性

DBA_ADVISOR_ACTIONSビューは、データベース内のすべての推奨項目に対応付けられたアクションに関する情報を示します。各アクションは、COMMAND列およびATTR1からATTR6列で指定されます。

次の表では、SQLアクセス・アドバイザ・アクションをDBA_ADVISOR_ACTIONSビューの属性列にマップします。表内のMVは、マテリアライズド・ビューを意味します。

表26-4 SQLアクセス・アドバイザのアクション属性

アクション ATTR1列 ATTR2列 ATTR3列 ATTR4列 ATTR5列 ATTR6列 NUM_ATTR1列

CREATE INDEX

索引名

索引表領域

ターゲット表

BITMAPまたはBTREE

索引列リスト/式

未使用

索引の記憶域のサイズ(バイト単位)

CREATE MATERIALIZED VIEW

MV名

MV表領域

REFRESH COMPLETE、REFRESH FAST、REFRESH FORCE、NEVER REFRESH

ENABLE QUERY REWRITE、DISABLE QUERY REWRITE

SQL SELECT文

未使用

MVの記憶域のサイズ(バイト単位)

CREATE MATERIALIZED VIEW LOG

ターゲット表名

MVログ表領域

ROWID PRIMARY KEY、SEQUENCE OBJECT ID

INCLUDING NEW VALUES、EXCLUDING NEW VALUES

表列リスト

パーティション副次句

未使用

CREATE REWRITE EQUIVALENCE

等価名

チェックサム値

未使用

未使用

ソースSQL文

等価SQL文

未使用

DROP INDEX

索引名

未使用

未使用

未使用

索引列

未使用

索引の記憶域のサイズ(バイト単位)

DROP MATERIALIZED VIEW

MV名

未使用

未使用

未使用

未使用

未使用

MVの記憶域のサイズ(バイト単位)

DROP MATERIALIZED VIEW LOG

ターゲット表名

未使用

未使用

未使用

未使用

未使用

未使用

PARTITION TABLE

表名

RANGE、INTERVAL、LIST、HASH、RANGE-HASH、RANGE-LIST

パーティション化のパーティション・キー(列名または列名のリスト)

サブパーティション化のパーティション・キー(列名または列名のリスト)

SQL PARTITION句

SQL SUBPARTITION句

未使用

PARTITION INDEX

索引名

LOCAL、RANGE、HASH

パーティション化のパーティション・キー(列名のリスト)

未使用

SQL PARTITION句

未使用

未使用

PARTITION ON MATERIALIZED VIEW

MV名

RANGE、INTERVAL、LIST、HASH、RANGE-HASH、RANGE-LIST

パーティション化のパーティション・キー(列名または列名のリスト)

サブパーティション化のパーティション・キー(列名または列名のリスト)

SQL SUBPARTITION句

SQL SUBPARTITION句

未使用

RETAIN INDEX

索引名

未使用

ターゲット表

BITMAPまたはBTREE

索引列

未使用

索引の記憶域のサイズ(バイト単位)

RETAIN MATERIALIZED VIEW

MV名

未使用

REFRESH COMPLETEまたはREFRESH FAST

未使用

SQL SELECT文

未使用

MVの記憶域のサイズ(バイト単位)

RETAIN MATERIALIZED VIEW LOG

ターゲット表名

未使用

未使用

未使用

未使用

未使用

未使用

26.6.2 SQLアクセス・アドバイザのタスク・パラメータのカテゴリ

SQLアクセス・アドバイザのタスク・パラメータは、ワークロード・フィルタ、タスク構成、スキーマ属性および推奨オプションに分類されます。

次の表では、最も関連性の高いSQLアクセス・アドバイザのタスク・パラメータをカテゴリにグループ化します。ワークロード・フィルタのタスク・パラメータはすべて非推奨です。

表26-5 アドバイザのタスク・パラメータのタイプとその使用方法

ワークロード・フィルタ タスク構成 スキーマ属性 推奨オプション

END_TIME

DAYS_TO_EXPIRE

DEF_INDEX_OWNER

ANALYSIS_SCOPE

INVALID_ACTION_LIST

JOURNALING

DEF_INDEX_TABLESPACE

COMPATIBILITY

INVALID_MODULE_LIST

REPORT_DATE_FORMAT

DEF_MVIEW_OWNER

CREATION_COST

INVALID_SQLSTRING_LIMIT

  

DEF_MVIEW_TABLESPACE

DML_VOLATILITY

INVALID_TABLE_LIST

  

DEF_MVLOG_TABLESPACE

LIMIT_PARTITION_SCHEMES

INVALID_USERNAME_LIST

  

DEF_PARTITION_TABLESPACE

MODE

RANKING_MEASURE

  

INDEX_NAME_TEMPLATE

PARTITIONING_TYPES

SQL_LIMIT

  

MVIEW_NAME_TEMPLATE

REFRESH_MODE

START_TIME

    

STORAGE_CHANGE

TIME_LIMIT

    

USE_SEPARATE_TABLESPACES

VALID_ACTION_LIST

    

WORKLOAD_SCOPE

VALID_MODULE_LIST

      

VALID_SQLSTRING_LIST

      

VALID_TABLE_LIST

      

VALID_USERNAME_LIST

      

26.6.3 SQLアクセス・アドバイザの定数

DBMS_ADVISORには、多数の定数が用意されています。

SQLアクセス・アドバイザでは、次の表に示されている定数を使用できます。

表26-6 SQLアクセス・アドバイザの定数

定数 説明
ADVISOR_ALL すべての可能な値を示す値。文字列パラメータでは、この値はワイルドカード(%)文字と同等です。
ADVISOR_CURRENT 現在の時刻またはアクティブな要素セットを示します。通常、これは時間パラメータで使用します。
ADVISOR_DEFAULT デフォルト値を示します。通常、タスクまたはワークロード・パラメータの設定時に使用します。
ADVISOR_UNLIMITED 無制限な数値を表す値です。
ADVISOR_UNUSED 未使用のエンティティを表す値。パラメータがADVISOR_UNUSEDに設定されている場合、現在の処理はこのパラメータの影響を受けません。通常、この定数を使用して、パラメータに依存する処理でパラメータを未使用に設定します。
SQLACCESS_GENERAL SQLアクセスの汎用タスク・テンプレートのデフォルト名を指定します。このテンプレートは、DML_VOLATILITYタスク・パラメータをtrueに設定し、ANALYSIS_SCOPEをINDEX、MVIEWに設定します。
SQLACCESS_OLTP SQLアクセスのOLTPタスク・テンプレートのデフォルト名を指定します。このテンプレートは、DML_VOLATILITYタスク・パラメータをtrueに設定し、ANALYSIS_SCOPEをINDEXに設定します。
SQLACCESS_WAREHOUSE SQLアクセスのウェアハウス・タスク・テンプレートのデフォルト名を指定します。このテンプレートは、DML_VOLATILITYタスク・パラメータをfalseに設定し、EXECUTION_TYPEをINDEX、MVIEWに設定します。
SQLACCESS_ADVISOR SQLアクセス・アドバイザの正式名称を格納します。プロシージャでアドバイザ名を引数として指定する必要がある場合、この名前を指定できます。