26 SQLアクセス・アドバイザを使用したアクセス・パスの最適化
SQLアクセス・アドバイザは、どの索引、マテリアライズド・ビュー、マテリアライズド・ビュー・ログまたはパーティションを作成、削除または保持するのが適切かを提示することによって、SQLのパフォーマンスの問題を識別し、解決を支援する診断ソフトウェアです。
この章のトピックは、次のとおりです:
26.1 SQLアクセス・アドバイザについて
SQLアクセス・アドバイザは、SQLチューニング・セットを含む複数のソースから入力を受け入れ、推奨事項を発行します。
ノート:
SQLアクセス・アドバイザをプラガブル・データベースとともに使用すると、データの可視性と権限の要件が一致しない場合があります。
この項では、次の項目について説明します。
関連項目:
コンテナ・データベース(CDB)内での管理機能の動作をまとめた表が記載されている『Oracle Database管理者ガイド』を参照してください
26.1.1 SQLアクセス・アドバイザの目的
SQLアクセス・アドバイザは、指定のワークロードに関するマテリアライズド・ビュー、マテリアライズド・ビュー・ログ、パーティションおよび索引の適切なセットを推奨します。
データ集中型の複雑な問合せの実行時に最適なパフォーマンスを実現できるようにデータベースをチューニングする場合、マテリアライズド・ビュー、パーティションおよび索引が必要不可欠です。SQLアクセス・アドバイザは、入力として実際のワークロードを使用するか、またはスキーマから仮想ワークロードを導出します。アドバイザは、次に、高速実行パスのアクセス構造を推奨します。アドバイザには、次の利点があります。
-
専門知識が必要ありません。
-
オプティマイザのルールに基づいて決定されます。
-
単一アドバイザで、SQLアクセスのあらゆる側面をカバーします。
-
Cloud Controlでは、簡単で使いやすいGUIウィザードを提供します。
-
推奨事項を実装するスクリプトを生成します。
関連項目:
-
Cloud Controlを使用してSQLアクセス・アドバイザを使用する方法を学習するには、『Oracle Database 2日でパフォーマンス・チューニング・ガイド』を参照してください。
-
自動索引付けについてさらに学習するには、『Oracle Database管理者ガイド』を参照
-
各種エディションおよびサービスで自動索引付けがサポートされているかどうかの詳細は、『Oracle Databaseライセンス情報』を参照
26.1.2 SQLアクセス・アドバイザのアーキテクチャ
自動チューニング・オプティマイザは、SQLアクセス・アドバイザで使用する重要なツールです。
アドバイザは、図26-1に示すソースから入力としてSQL文を受け入れ、オプティマイザを使用してこれらの文を分析して推奨できます。
図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チューニング・セットを作成できます。 -
仮想ワークロード
ディメンションと制約を分析することで、スキーマから仮想ワークロードを作成できます。このオプションは、アプリケーションの初期の設計時に役立ちます。
関連項目:
-
共有SQL領域について学習するには、『Oracle Database概要』を参照してください。
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
パッケージを使用して、正確な統計または見積り統計を収集できます。
統計の収集は時間のかかる処理であり、完全な統計精度は必要ないため、通常は統計を見積ることをお薦めします。特定の表に関する統計情報を収集しない場合、その表を参照する問合せはワークロードで無効とマークされ、それらの問合せについては推奨事項は生成されません。また、既存のすべての索引とマテリアライズド・ビューを分析しておくこともお薦めします。
関連項目:
-
マテリアライズド・ビューについてさらに学習するには、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照
-
パーティションについてさらに学習するには、『Oracle Database VLDBおよびパーティショニング・ガイド』を参照してください
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回のみ表示されます。
関連項目:
-
アクションおよび推奨事項を表示する方法を学習するには、「SQLアクセス・アドバイザ・タスクの結果の表示」を参照してください
26.1.2.4.2 パーティション化の推奨事項の解釈のガイドライン
SQLアクセス・アドバイザは、実表のパーティション化によってパフォーマンスが向上すると判断した場合、その表を参照する問合せが含まれるすべての推奨事項にパーティション・アクションを追加します。このように、索引およびマテリアライズド・ビューの推奨事項が、適切にパーティション化された表に実装されます。
SQLアクセス・アドバイザは、既存のパーティション化されていない実表のパーティション化を推奨することがあります。アドバイザの実装スクリプトにパーティション推奨事項が含まれる場合は、次の問題に注意してください。
-
既存の表のパーティション化は複雑で広範な作業であるため、新しい索引やマテリアライズド・ビューを実装するよりも非常に時間がかかる場合があります。この推奨事項の実装には、時間を十分に取っておく必要があります。
-
索引およびマテリアライズド・ビューの推奨事項は、索引やビューを削除すれば簡単に元に戻せますが、表をパーティション化すると簡単には元の状態に戻せません。このため、パーティション化の推奨が含まれるスクリプトを実行する前に、データベースのバックアップが必要です。
-
実表を再パーティション化する際に、SQLアクセス・アドバイザのスクリプトにより、元の表の一時コピーが作成されますが、その一時コピーは元の表と同じ容量の領域を使用します。そのため、再パーティション化プロセスには、再パーティション化する最も大きな表のコピーをもう1つ作成できる程度に十分な空きディスク領域が必要です。実装スクリプトを実行する前に、使用可能な領域を確保しておく必要があります。
パーティション実装スクリプトは、索引、マテリアライズド・ビューおよび制約などの依存オブジェクトの移行を試みます。しかし、一部のオブジェクトは、自動的に移行できません。たとえば、再パーティション化された実表に対するPL/SQLストアド・プロシージャの定義は、通常では無効になり、再コンパイルが必要です。
-
アドバイザのパーティション化の推奨を実装しない場合、同じスクリプトの同じ表に対するその他のすべての推奨事項(
CREATE INDEX
およびCREATE MATERIALIZED VIEW
などの推奨)は、パーティション化の推奨に依存していることに注意してください。正確な推奨を得るには、パーティション化の推奨をスクリプトから単純に削除しないでください。かわりに、パーティション化を無効にしてアドバイザを再実行します(たとえば、ANALYSIS_SCOPE
パラメータの値にTABLE
キーワードを含めないなど)。
関連項目:
-
CREATE DIRECTORY
の構文の詳細は、Oracle Database SQL言語リファレンスを参照してください -
DBMS_ADVISOR.GET_TASK_SCRIPT
ファンクションの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
26.1.3 SQLアクセス・アドバイザのユーザー・インタフェース
Cloud Controlで利用可能なGUIウィザードを介して、SQLアクセス・アドバイザを使用することをお薦めします。
DBMS_ADVISOR
パッケージを介してSQLアクセス・アドバイザを起動することもできます。この章では、APIの使用方法について説明します。
この項では、次の項目について説明します。
関連項目:
-
SQLアクセス・アドバイザ・ウィザードの使用方法については、『Oracle Database 2日でパフォーマンス・チューニング・ガイド』に説明があります。
-
完全なセマンティクスおよび構文については、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
26.1.3.1 Cloud Controlを使用した「SQLアクセス・アドバイザ: 初期オプション」ページへのアクセス
Cloud Controlの「SQLアクセス・アドバイザ: 初期オプション」ページは、推奨事項を取得するプロセスを示すウィザードの開始ページです。
「SQLアクセス・アドバイザ: 初期オプション」ページにアクセスするには:
-
適切な資格証明を使用してCloud Controlにログインします。
-
「ターゲット」メニューの下で、「データベース」を選択します。
-
データベース・ターゲットのリストで、管理対象のOracle Databaseインスタンスのターゲットを選択します。
-
データベースの資格証明の入力を求められた場合は、実行するタスクに必要な最小限の資格証明を入力します。
-
「パフォーマンス」メニューから「SQL」を選択し、さらに「SQLアクセス・アドバイザ」を選択します。
図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アクセス・アドバイザの基本ワークフローを示します。
通常、次のステップを実行して、SQLアクセス・アドバイザを使用します。
-
SQLチューニング・セットの作成
SQLアクセス・アドバイザの入力ワークロード・ソースは、SQLチューニング・セットです(STS)。
DBMS_SQLTUNE.CREATE_SQLSET
を使用して、SQLチューニング・セットを作成します。このタスクについては、「SQLアクセス・アドバイザの入力としてのSQLチューニング・セットの作成」を参照してください。
-
SQLチューニング・セットのロード
SQLアクセス・アドバイザのパフォーマンスは、実際の使用状況に基づいたワークロードが使用可能な場合に最も高くなります。
DBMS_SQLTUNE.LOAD_SQLSET
を使用し、ユーザー定義のワークロードをSQLチューニング・セットに移入します。このタスクについては、「ユーザー定義のワークロードのSQLチューニング・セットへの移入」を参照してください。
-
タスクの作成および構成
タスクでは、SQLアクセス・アドバイザが分析する内容と分析結果の格納場所を定義します。タスクを作成するには、
DBMS_ADVISOR.CREATE_TASK
プロシージャを使用します。タスクのパラメータを定義するにはSET_TASK_PARAMETER
プロシージャを使用し、タスクをSTSにリンクするにはDBMS_ADVISOR.ADD_STS_REF
プロシージャを使用します。このタスクについては、「SQLアクセス・アドバイザ・タスクの作成および構成」を参照してください。
-
タスクの実行
推奨事項を生成するには、
DBMS_ADVISOR.EXECUTE_TASK
プロシージャを実行します。各推奨事項には、1つ以上のアクションが指定されます。たとえば、1つの推奨事項には、複数のマテリアライズド・ビュー・ログの作成、マテリアライズド・ビューの作成、およびその分析による統計の収集が含まれます。このタスクについては、「SQLアクセス・アドバイザ・タスクの実行」を参照してください。
-
推奨事項の表示
データ・ディクショナリ・ビューを問い合せて、推奨事項を表示できます。
このタスクについては、「SQLアクセス・アドバイザ・タスクの結果の表示」を参照してください。
-
オプション: 推奨事項を実装する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を作成するには:
-
SQL*Plusで、ユーザー
sh
としてデータベースにログインします。 -
SQL*Plus変数を設定します。
たとえば、次のコマンドを入力します。
SET SERVEROUTPUT ON; VARIABLE task_id NUMBER; VARIABLE task_name VARCHAR2(255); VARIABLE workload_name VARCHAR2(255);
-
SQLチューニング・セットを作成します。
たとえば、値を
workload_name
変数に割り当て、STSを作成するには次のように行います。EXECUTE :workload_name := 'MY_STS_WORKLOAD'; EXECUTE DBMS_SQLTUNE.CREATE_SQLSET(:workload_name, 'test purpose');
関連項目:
-
CREATE_SQLSET
について学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
26.2.2 ユーザー定義のワークロードのSQLチューニング・セットへの移入
ワークロードは、1つ以上のSQL文と、各文を完全に説明する統計情報および属性で構成されます。
全ワークロードには、対象となるビジネス・アプリケーションのすべてのSQL文が含まれます。部分ワークロードには、SQL文のサブセットが含まれます。これらの違いは、全ワークロードの場合、使用されていないマテリアライズド・ビューと索引を削除するようSQLアクセス・アドバイザが推奨する点にあります。
ワークロードなしでSQLアクセス・アドバイザを使用することはできません。SQLアクセス・アドバイザは特定の統計、ビジネスの重要性、またはその2つの組合せに応じてエントリにランクを付けます。これにより、アドバイザは最も重要なSQL文を先に処理できるようになります。
SQLアクセス・アドバイザのパフォーマンスは、実際の使用状況に基づいたワークロードを使用する場合に最も高くなります。複数のワークロードをSQLチューニング・セットとして保存することにより、長期およびデータベース・インスタンスの起動から停止までの全ライフサイクルにわたって、実際のデータ・ウェアハウスまたはOLTP環境の様々な使用状況を参照できます。
次の表は、ユーザー定義のワークロードをSTSに移入するために使用できるプロシージャを説明しています。
表26-1 STSのロードのプロシージャ
プロシージャ | 説明 | さらに学習するには |
---|---|---|
|
選択したSQLのセットをSQLチューニング・セットに移入します。このプロシージャを複数回コールすると、新しいSQL文の追加または既存の文の属性の置換を行うことができます。 |
Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス |
|
SQLワークロード・データをユーザーが指定したSQLチューニング・セットにコピーします。ユーザーには、SQLチューニング・セットの権限と |
Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス |
前提条件
このチュートリアルでは、次の操作を行うものとします。
-
sh.user_workload
という名前の表を作成し、SQL文に関する情報を格納します。 -
sh.user_workload
表にsh
スキーマ内の表の3つの問合せに関する情報をロードします。 -
「SQLアクセス・アドバイザの入力としてのSQLチューニング・セットの作成」で作成されたSTSに、
sh.user_workload
に格納されているワークロードを移入します。
ユーザー定義ワークロードをSTSに移入するには:
-
ユーザー
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 */ );
-
問合せに関する情報を
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;
-
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
権限が付与されている必要があります。
前提条件
このチュートリアルでは、次のことが前提となっています。
-
MYTASK
という名前のタスクを作成します。 -
このタスクを使用して、「ユーザー定義のワークロードのSQLチューニング・セットへの移入」で定義されたワークロードを分析します。
-
実行に30分以上かかる場合は、タスクを終了します。
-
SQLアクセス・アドバイザで、索引のみが考慮されるようにします。
SQLアクセス・アドバイザ・タスクを作成および構成するには:
-
ユーザー
sh
としてSQL*Plusをデータベースに接続して、タスクを作成します。たとえば、次のコマンドを入力します。
EXEC :task_name := 'MYTASK'; EXEC DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', :task_id, :task_name);
-
タスク・パラメータを設定します。
たとえば、次の文を実行します。
EXEC DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, 'TIME_LIMIT', 30); EXEC DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, 'ANALYSIS_SCOPE', 'ALL');
-
ワークロードへタスクをリンクします。
たとえば、次の文を実行します。
EXECUTE DBMS_ADVISOR.ADD_STS_REF(:task_name, 'SH', :workload_name);
関連項目:
-
DBMS_ADVISOR.CREATE_TASK
、DBMS_ADVISOR.SET_TASK_PARAMETER
およびDBMS_ADVISOR.ADD_STS_REF
プロシージャについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
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アクセス・アドバイザ・タスクを作成および構成するには:
-
SQL*PlusまたはSQL Developerで、必要な権限を持つユーザーとしてデータベースにログインします。
-
タスクを実行します。
たとえば、次の文を実行します。
EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);
-
オプションで、
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) | 説明 |
---|---|
|
アドバイザ・タスクに関する情報を示します。SQLアクセス・アドバイザ・タスクを参照するには、 |
|
データベース内のすべての推奨項目の分析結果を示します。推奨項目には、複数のアクションを対応付けることができます。このアクションについては、 |
|
データベース内のすべての推奨項目に関連付けられたアクションに関する情報を示します。各アクションは、 |
|
データベース内のすべての推奨項目の根拠に関する情報を示します。 |
|
SQLアクセス・アドバイザの分析後、データベース内のすべてのワークロード・オブジェクトに関する情報を示します。事前コストと事後コストの数は、推奨されたアクセス構造がある場合とない場合とで見積られたオプティマイザのコスト( |
前提条件
このチュートリアルでは、「SQLアクセス・アドバイザ・タスクの実行」で実行されたタスクの結果を表示するものとします。
SQLアクセス・アドバイザ・タスクの結果を表示するには:
-
適切な権限で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
)とそのランクおよび全利点を示しています。ランクとは、推奨事項がサポートする問合せの重要性の尺度です。利点とは、推奨事項を使用したすべての問合せの実行コスト(オプティマイザのコストの観点から)における改善結果です。 -
推奨事項が役立つ問合せを識別します。
たとえば、次の
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
を参照)に関するものです。 -
この一連の推奨事項について個別アクションの数を示します。
たとえば、次の問合せを使用します(出力例も示します)。
SELECT 'Action Count', COUNT(DISTINCT action_id) cnt FROM USER_ADVISOR_ACTIONS WHERE TASK_NAME = :task_name; 'ACTIONCOUNT CNT ------------ ---------- Action Count 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
-
推奨事項の属性を示します。
たとえば、次のアクションの属性を参照するには、次の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============
関連項目:
-
Attr5
およびAttr6
の詳細は、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください
26.2.6 タスク・スクリプトの生成および実行
DBMS_ADVISOR.GET_TASK_SCRIPT
プロシージャを使用して、SQLアクセス・アドバイザの推奨事項に対するSQL文のスクリプトを作成できます。スクリプトは実行可能SQLファイルで、DROP
文、CREATE
文およびALTER
文を含めることができます。
新しいオブジェクトの場合、マテリアライズド・ビューの名前、マテリアライズド・ビュー・ログおよび索引は、ユーザー指定の名前テンプレートを使用して自動的に生成されます。生成されたSQLスクリプトは、実行する前に見直します。
前提条件
このチュートリアルでは、「SQLアクセス・アドバイザ・タスクの実行」で生成された推奨事項を含むスクリプトの保存および実行を行うものとします。
SQLスクリプトを保存および実行するには:
-
管理者としてSQL*Plusをデータベースに接続します。
-
ディレクトリ・オブジェクトを作成し、読取り/書込みを行う権限を付与します。
たとえば、次の文を使用します。
CREATE DIRECTORY ADVISOR_RESULTS AS '/tmp'; GRANT READ ON DIRECTORY ADVISOR_RESULTS TO PUBLIC; GRANT WRITE ON DIRECTORY ADVISOR_RESULTS TO PUBLIC;
-
データベースに
sh
として接続し、ファイルにスクリプトを保存します。たとえば、次の文を使用します。
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('MYTASK'), 'ADVISOR_RESULTS', 'advscript.sql');
-
テキスト・エディタを使用して、スクリプトの内容を表示します。
次の例は、このプロシージャよって生成されたスクリプトのフラグメントです。
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')) ); . . .
-
オプションとして、SQL*PlusでSQLスクリプトを実行できます。
たとえば、次のコマンドを入力します。
@/tmp/advscript.sql
関連項目:
-
CREATE DIRECTORY
の構文の詳細は、Oracle Database SQL言語リファレンスを参照してください -
GET_TASK_SCRIPT
ファンクションについてさらに学習するには、『Oracle Database PL/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
という名前を付けます。
テンプレートを作成し、このテンプレートをタスクのベースとするには:
-
ユーザー
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';
-
クィック・チューニングを実行します。
たとえば、次の文は
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アクセス・アドバイザは、既存の実表のパーティション化によるパフォーマンスへの影響は評価されません。
タスクを作成して評価モードに設定するには:
-
適切な権限でSQL*Plusをデータベースに接続して、タスクを作成します。
たとえば、次の文を入力します。
t_name
はタスクの名前に設定されたSQL*Plus変数です。EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:t_name);
-
クィック・チューニングを実行します。
たとえば、次の文は、前述のタスクを評価モードに設定します。
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
を読取り専用にします。
タスクの属性を更新するには:
-
ユーザー
sh
としてSQL*Plusをデータベースに接続して、タスクの名前を変更します。たとえば、次の文を使用します。
EXECUTE DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES('MYTASK', 'TUNING1');
-
タスクを読取り専用に設定します。
たとえば、次の文を使用します。
EXECUTE DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES('TUNING1', read_only => 'true');
関連項目:
-
UPDATE_TASK_ATTRIBUTES
プロシージャとそのパラメータの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
26.4.3 SQLアクセス・アドバイザのタスク・テンプレートの作成および使用
タスク・テンプレートは、構成を保存したもので、今後のタスクおよびワークロードのベースとなります。
タスクまたはワークロードをテンプレートとして設定すると、今後のタスクの作成で、出発点またはテンプレートとして使用できます。テンプレートを設定することにより、チューニング分析の実行時間を短縮できます。また、このアプローチにより、ビジネス・オペレーションに適合したチューニング分析が可能になります。
物理的に、タスクとテンプレートは同じです。ただし、テンプレートは実行できません。テンプレートからタスクを作成するには、新しいタスクの作成時に使用するテンプレートを指定します。このとき、SQLアクセス・アドバイザは、新しく作成されたタスクにテンプレートからデータおよびパラメータ設定をコピーします。また、既存のタスクをテンプレートとして設定するには、タスクの作成時にテンプレート属性を設定するか、後でUPDATE_TASK_ATTRIBUTE
プロシージャを使用します。
次の表では、タスク・テンプレートを管理する際に使用できるプロシージャを説明します。
表26-3 タスク・テンプレートのDBMS_ADVISORプロシージャ
プロシージャ | 説明 |
---|---|
|
|
|
|
|
|
前提条件
このチュートリアルでは、次のことが前提となっています。
-
MY_TEMPLATE
という名前のテンプレートを作成します。 -
MY_TEMPLATE
に基づいてタスクによって推奨される索引およびマテリアライズド・ビューのネーミング規則を設定します。 -
MY_TEMPLATE
に基づいてNEWTASK
タスクを作成します。
テンプレートを作成し、このテンプレートをタスクのベースとするには:
-
ユーザー
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;
-
テンプレートのパラメータを設定します。
たとえば、次の文は、推奨される索引およびマテリアライズド・ビューのネーミング規則を設定します。
-- 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;
-
既存のテンプレートに基づいてタスクを作成します。
たとえば、次のコマンドを入力して、
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
が現在実行中です。 -
このタスクに割り込んで、推奨事項を表示します。
現在実行中のタスクに割り込むには:
-
ユーザー
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
が現在実行中です。このタスクは、推奨事項のパーティション化を行うために設定されます。 -
このタスクを取り消して、タスクが索引のみ推奨するようにリセットします。
現在実行中のタスクを取り消すには:
-
ユーザー
sh
としてSQL*Plusをデータベースに接続して、タスクを取り消します。たとえば、次のように
MY_TEMPLATE
という名前のテンプレートを作成します。EXECUTE DBMS_ADVISOR.CANCEL_TASK ('MYTASK');
-
タスクをリセットします。
たとえば、
RESET_TASK
プロシージャを次のように実行します。EXECUTE DBMS_ADVISOR.RESET_TASK('MYTASK');
-
タスク・パラメータを設定します。
たとえば、分析の有効範囲を次のように
INDEX
に変更します。EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, 'ANALYSIS_SCOPE', 'INDEX');
-
タスクを実行します。
たとえば、
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アクセス・アドバイザ・タスクを削除するには:
-
ユーザー
sh
としてSQL*Plusをデータベースに接続して、SQLアクセス・アドバイザの既存のタスクを問い合せます。たとえば、データ・ディクショナリを次のように問い合せます(出力例も示します)。
SELECT TASK_NAME FROM USER_ADVISOR_TASKS WHERE ADVISOR_NAME = 'SQL Access Advisor'; TASK_NAME ------------------------- MYTASK NEWTASK
-
MYTASK
とMY_STS_WORKLOAD
間のリンクを削除します。たとえば、リファレンスを次のように削除します。
EXECUTE DBMS_ADVISOR.DELETE_STS_REF('MYTASK', null, 'MY_STS_WORKLOAD');
-
目的のタスクを削除します。
たとえば、
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);
前提条件
このチュートリアルでは、次のことが前提となっています。
-
チュートリアルの「SQLアクセス・アドバイザ・タスクの結果の表示」の説明に従って、推奨事項を確認します。
-
最初の推奨事項(表をパーティション化する)を拒否します。
推奨事項をマークするには:
-
ユーザー
sh
としてSQL*Plusをデータベースに接続して、推奨事項をマークします。たとえば、
1
の推奨事項を次のように拒否します。EXECUTE DBMS_ADVISOR.MARK_RECOMMENDATION('MYTASK', 1, 'REJECT');
この推奨事項およびこれに依存する推奨事項は、スクリプトに表示されません。
-
「タスク・スクリプトの生成および実行」の説明に従って、スクリプトを生成します。
関連項目:
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
推奨オブジェクトの表領域を指定します。
前提条件
このチュートリアルでは、次のことが前提となっています。
-
チュートリアルの「SQLアクセス・アドバイザ・タスクの結果の表示」の説明に従って、推奨事項を確認します。
-
推奨事項1、アクション1の表領域を
SH_MVIEWS
に変更します。
推奨事項をマークするには:
-
ユーザー
sh
としてSQL*Plusをデータベースに接続して、推奨事項の属性を更新します。たとえば、表領域の名前を次のように
SH_MVIEWS
に変更します。BEGIN DBMS_ADVISOR.UPDATE_REC_ATTRIBUTES ( 'MYTASK' , 1 , 1 , 'TABLESPACE' , 'SH_MVIEWS' ); END;
-
「タスク・スクリプトの生成および実行」の説明に従って、スクリプトを生成します。
関連項目:
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列 |
---|---|---|---|---|---|---|---|
|
索引名 |
索引表領域 |
ターゲット表 |
|
索引列リスト/式 |
未使用 |
索引の記憶域のサイズ(バイト単位) |
|
MV名 |
MV表領域 |
|
|
SQL |
未使用 |
MVの記憶域のサイズ(バイト単位) |
|
ターゲット表名 |
MVログ表領域 |
|
|
表列リスト |
パーティション副次句 |
未使用 |
|
等価名 |
チェックサム値 |
未使用 |
未使用 |
ソースSQL文 |
等価SQL文 |
未使用 |
|
索引名 |
未使用 |
未使用 |
未使用 |
索引列 |
未使用 |
索引の記憶域のサイズ(バイト単位) |
|
MV名 |
未使用 |
未使用 |
未使用 |
未使用 |
未使用 |
MVの記憶域のサイズ(バイト単位) |
|
ターゲット表名 |
未使用 |
未使用 |
未使用 |
未使用 |
未使用 |
未使用 |
|
表名 |
|
パーティション化のパーティション・キー(列名または列名のリスト) |
サブパーティション化のパーティション・キー(列名または列名のリスト) |
SQL |
SQL |
未使用 |
|
索引名 |
|
パーティション化のパーティション・キー(列名のリスト) |
未使用 |
SQL |
未使用 |
未使用 |
|
MV名 |
|
パーティション化のパーティション・キー(列名または列名のリスト) |
サブパーティション化のパーティション・キー(列名または列名のリスト) |
SQL |
SQL |
未使用 |
|
索引名 |
未使用 |
ターゲット表 |
|
索引列 |
未使用 |
索引の記憶域のサイズ(バイト単位) |
|
MV名 |
未使用 |
|
未使用 |
SQL |
未使用 |
MVの記憶域のサイズ(バイト単位) |
|
ターゲット表名 |
未使用 |
未使用 |
未使用 |
未使用 |
未使用 |
未使用 |
26.6.2 SQLアクセス・アドバイザのタスク・パラメータのカテゴリ
SQLアクセス・アドバイザのタスク・パラメータは、ワークロード・フィルタ、タスク構成、スキーマ属性および推奨オプションに分類されます。
次の表では、最も関連性の高いSQLアクセス・アドバイザのタスク・パラメータをカテゴリにグループ化します。ワークロード・フィルタのタスク・パラメータはすべて非推奨です。
表26-5 アドバイザのタスク・パラメータのタイプとその使用方法
ワークロード・フィルタ | タスク構成 | スキーマ属性 | 推奨オプション |
---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
|
|
||
|
|
||
|
|||
|
|||
|
|||
|
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アクセス・アドバイザの正式名称を格納します。プロシージャでアドバイザ名を引数として指定する必要がある場合、この名前を指定できます。 |