180 DBMS_SQLPA
DBMS_SQLPAパッケージは、SQLパフォーマンス・アナライザを実装するためのインタフェースを提供します。
この章の内容は次のとおりです。
180.1 DBMS_SQLPAの概要
DBMS_SQLPA
パッケージは、システム環境の変更がSQLワークロードのパフォーマンスに与える影響をユーザーが予測する場合に役立つ機能を提供します。このインタフェースを使用すると、2つの異なるバージョンのワークロード・パフォーマンスを作成してから比較し、それらの2つのバージョンの違いを分析して、変更によって影響を受ける可能性があるSQL文を明らかにできます。
このパッケージは、SQLパフォーマンス・アナライザを実装するためのタスク指向のインタフェースを提供します。たとえば
-
CREATE_ANALYSIS_TASKファンクションを使用して、1つの文またはSQL文の集合に対する分析タスクを作成します。
-
EXECUTE_ANALYSIS_TASKファンクションおよびプロシージャを使用して、作成した分析タスクを実行します。
-
REPORT_ANALYSIS_TASKファンクションを使用して、分析タスクの結果を表示します。
180.2 DBMS_SQLPAのセキュリティ・モデル
このパッケージは、PUBLIC
で使用可能で、独自のセキュリティ・チェックが実行されます。すべての分析タスク・インタフェース(XXX_ANALYSIS_TASK
)にはADVISOR
権限が必要です。
180.3 DBMS_SQLPAサブプログラムの要約
この表は、DBMS_SQLPA
サブプログラムを示し、簡単に説明しています。
表180-1 DBMS_SQLPAパッケージのサブプログラム
サブプログラム | 説明 |
---|---|
1つ以上のSQL文に対して現在実行中の分析タスクを取り消します。 |
|
1つ以上のSQL文を処理および分析するアドバイザ・タスクを作成します。 |
|
SQL分析タスクを削除します。 |
|
以前に作成した分析タスクを実行します。 |
|
現在実行中の分析タスクを中断します。 |
|
分析タスクの結果を表示します。 |
|
現在実行中の分析タスクを初期状態にリセットします。 |
|
SQLチューニング・セットを処理するために作成され、以前に中断された分析タスクを再開します。 |
|
SQL分析タスク・パラメータの値を設定します。 |
|
SQL分析タスク・パラメータのデフォルト値を設定します。 |
180.3.1 CANCEL_ANALYSIS_TASKプロシージャ
このプロシージャは、現在実行中の分析タスクを取り消します。タスクから、すべての中間結果データが削除されます。
構文
DBMS_SQLPA.CANCEL_ANALYSIS_TASK( task_name IN VARCHAR2);
パラメータ
表180-2 CANCEL_ANALYSIS_TASKプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
取り消すタスクの名前。 |
例
タスクの実行を停止する必要があり、実行済結果の確認が不要な場合のタスクの取消し
EXEC DBMS_SQLPA.CANCEL_ANALYSIS_TASK(:my_task);
180.3.2 CREATE_ANALYSIS_TASKファンクション
このファンクションは、1つ以上のSQL文を処理および分析するアドバイザ・タスクを作成します。
ノート:
マルチテナント・コンテナ・データベースが、Oracle Database 21c以降のリリースで唯一サポートされているアーキテクチャです。ドキュメントが改訂されている間は、従来の用語が残っている可能性があります。ほとんどの場合、"データベース"と"非CDB"は、文脈に応じてCDBまたはPDBを指しています。アップグレードなどでは、"非CDB"が以前のリリースの非CDBを指している場合もあります。
このファンクションを様々な形式で使用して次の操作ができます。
-
テキストを指定して、単一の文に対する分析タスクを作成する。
-
識別子を指定して、カーソル・キャッシュ内の単一の文に対する分析タスクを作成する。
-
スナップショット識別子の範囲を指定して、ワークロード・リポジトリ内の単一文に対する分析タスクを作成する。
-
SQLチューニング・セットに対する分析タスクを作成する。
すべての場合において、このファンクションは、アドバイザ・タスクの作成とそのパラメータの設定を行います。
構文
SQLテキスト形式。この形式のファンクションは、テキストを指定して、単一の文の診断を準備する場合にコールします。
DBMS_SQLPA.CREATE_ANALYSIS_TASK( sql_text IN CLOB, bind_list IN sql_binds := NULL, parsing_schema IN VARCHAR2 := NULL, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL) RETURN VARCHAR2;
SQL ID形式。この形式のファンクションは、識別子を指定して、カーソル・キャッシュ内の単一の文に対する分析を準備する場合にコールします。
DBMS_SQLPA.CREATE_ANALYSIS_TASK( sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, task_name IN VARCHAR2 := NULL, con_name IN VARCHAR2 DEFAULT, description IN VARCHAR2 := NULL) RETURN VARCHAR2;
ワークロード・リポジトリ形式。この形式のファンクションは、スナップショット識別子の範囲を指定して、ワークロード・リポジトリ内の単一の文に対する分析を準備する場合にコールします。
DBMS_SQLPA.CREATE_ANALYSIS_TASK( dbid IN NUMBER DEFAULT, begin_snap IN NUMBER, end_snap IN NUMBER, sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL) con_name IN VARCHAR2 DEFAULT, RETURN VARCHAR2;
SQLSET形式。この形式のファンクションは、SQLチューニング・セットの診断を準備する場合にコールします。
DBMS_SQLPA.CREATE_ANALYSIS_TASK( sqlset_name IN VARCHAR2, basic_filter IN VARCHAR2 := NULL, con_name IN VARCHAR2 DEFAULT, order_by IN VARCHAR2 := NULL, top_sql IN VARCHAR2 := NULL, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL sqlset_owner IN VARCHAR2 := NULL) RETURN VARCHAR2;
パラメータ
表180-3 CREATE_ANALYSIS_TASKファンクションのパラメータ
パラメータ | 説明 |
---|---|
|
SQL文のテキスト。 |
|
バインド値のセット。 |
|
文をコンパイルできるスキーマの名前。 |
|
分析タスク名(オプション)。 |
|
インポート済または |
|
SPAタスクのコンテナ。セマンティクスはファンクションの形式によって、次のように異なります。 SQL ID形式の場合、このパラメータは、データベースがSPAで使用するSQL文を取得するコンテナを指定します。SPAはこのコンテナの文を分析します。Nullの場合、データベースはSPA分析に現在のPDBを使用します。 AWR形式の場合、このパラメータは、データベースがSPAで使用するSQL文を取得するAWRデータが含まれるコンテナを指定します。SPAはこのコンテナの文を分析します。Nullの場合、データベースはSPA分析に現在のPDBを使用します。 すべてのファンクション形式で、次のことが当てはまります。
|
|
SQL分析タスクの説明(最大256文字)。 |
|
SQL文の識別子。 |
|
SQL実行計画のハッシュ値。 |
|
開始スナップショット識別子 |
|
終了スナップショット識別子 |
|
SQLチューニング・セットの名前。 |
|
SQLチューニング・セットからSQLをフィルタするためのSQL述語。 |
|
選択したSQLのorder-by句。 |
|
フィルタ処理およびランク付け後のTop N SQL。 |
|
SQLチューニング・セットの所有者。現行のスキーマ所有者の場合は |
戻り値
ユーザーごとに一意のSQL分析タスク名(2人の異なるユーザーが自分のアドバイザ・タスクに同じ名前を付けることはできます)。
例
variable stmt_task VARCHAR2(64); variable sts_task VARCHAR2(64); -- Sql text format EXEC :stmt_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( sql_text => 'select quantity_sold from sales s, times t where s.time_id = t.time_id and s.time_id = TO_DATE(''24-NOV-00'')'); -- Sql id format (cursor cache) EXEC :stmt_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( sql_id => 'ay1m3ssvtrh24'); -- Workload repository format exec :stmt_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( begin_snap => 1, end_snap => 2, sql_id => 'ay1m3ssvtrh24'); -- Sql tuning set format (first we need to load an STS, then analyze it) EXEC :sts_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( - sqlset_name => 'my_workload', - order_by => 'BUFFER_GETS', - description => 'process workload ordered by buffer gets');
180.3.3 DROP_ANALYSIS_TASKプロシージャ
このプロシージャは、SQL分析タスクを削除します。タスクとそのすべての結果データが削除されます。
構文
DBMS_SQLPA.DROP_ANALYSIS_TASK( task_name IN VARCHAR2);
パラメータ
表180-4 DROP_ANALYSIS_TASKプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
削除する分析タスクの名前。 |
180.3.4 EXECUTE_ANALYSIS_TASKファンクションおよびプロシージャ
このファンクションおよびプロシージャは、以前に作成した分析タスクを実行します。ファンクション・バージョンは、新しい実行名を戻します。
構文
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name IN VARCHAR2, execution_type IN VARCHAR2 := 'test execute', execution_name IN VARCHAR2 := NULL, execution_params IN dbms_advisor.argList := NULL, execution_desc IN VARCHAR2 := NULL) RETURN VARCHAR2;
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name IN VARCHAR2, execution_type IN VARCHAR2 := 'test execute', execution_name IN VARCHAR2 := NULL, execution_params IN dbms_advisor.argList := NULL, execution_desc IN VARCHAR2 := NULL);
パラメータ
表180-5 EXECUTE_ANALYSIS_TASKファンクションおよびプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
実行するタスクの識別子。 |
|
ファンクションで実行するアクションのタイプ。
|
|
実行を修飾して識別するための名前。指定されていない場合は、アドバイザによって生成され、ファンクションによって戻されます。 |
|
指定した実行のパラメータ(名前、値)のリスト。実行パラメータは、指定した実行に対してのみ影響します。これらのパラメータは、(SET_ANALYSIS_DEFAULT_PARAMETERプロシージャを使用して設定された)タスクに格納されているパラメータの値より優先されます。 |
|
実行について説明する256文字の長さの文字列。 |
使用上のノート
SQLパフォーマンス・アナライザのタスクは、リセットせずに複数回実行できます。たとえば、SQLワークロードに対して変更の影響分析を実行するためにタスクが作成された場合は、システム環境を変更する前に、作成されたタスクを実行して、パフォーマンス分析の資料として使用されるワークロードのバージョンを構築する必要があります。変更が行われた後で、変更後バージョンのワークロードを構築するには、2回目の実行が必要になります。最後に、両方のバージョンのワークロードのパフォーマンスをアドバイザで分析および比較するために、タスクの3回目の実行が必要になります。
例
1. 変更の影響を分析する目的でのタスクの作成
EXEC :tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK( sqlset_name => 'my_sts');
2. ベースラインの作成または変更前の実行
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => :tname, execution_type => 'test execute', execution_name => 'before_change');
3. 変更の実行
...
4. 変更後バージョンのワークロード・パフォーマンスの作成
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => :tname, - execution_type => 'test execute', execution_name => 'after_change')
5. 2つのバージョンのワークロードの比較
デフォルトでは、最後の2回の実行結果を常に比較します。SQLパフォーマンス・アナライザは、比較用のデフォルトのメトリックとしてelapsed_time
を使用します。ここでは、デフォルトのメトリックをbuffer_gets
に変更しています。
EXEC DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER( :tname,'comparison_metric', 'buffer_gets'); EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => :tname, - execution_type => 'compare performance', - execution_name => 'after_change');
比較する2つの実行、および使用する比較メトリックを明示的に指定する場合は、次のコールを使用します。
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => :tname, - execution_type => 'compare performance', execution_params => dbms_advisor.arglist( 'execution_name1', 'before_change', 'execution_name2', 'after_change', 'comparison_metric', 'buffer_gets'));
180.3.5 INTERRUPT_ANALYSIS_TASKプロシージャ
このプロシージャは、現在実行中の分析タスクを中断します。タスクから、すべての中間結果データが削除されるわけではありません。
構文
DBMS_SQLPA.INTERRUPT_ANALYSIS_TASK( task_name IN VARCHAR2);
パラメータ
表180-6 INTERRUPT_ANALYSIS_TASKプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
中断する分析タスクの識別子。 |
例
EXEC DBMS_SQLPA.INTERRUPT_ANALYSIS_TASK(:my_task);
180.3.6 REPORT_ANALYSIS_TASKファンクション
このプロシージャは、分析タスクの結果を表示します。
構文
DBMS_SQLPA.REPORT_ANALYSIS_TASK( task_name IN VARCHAR2, type IN VARCHAR2 := 'TEXT', level IN VARCHAR2 := 'TYPICAL', section IN VARCHAR2 := 'SUMMARY', object_id IN NUMBER := NULL, top_sql IN NUMBER := 100, execution_name IN VARCHAR2 := NULL, task_owner IN VARCHAR2 := NULL, order_by IN VARCHAR2 := NULL) RETURN CLOB;
パラメータ
表180-7 REPORT_ANALYSIS_TASKファンクションのパラメータ
パラメータ | 説明 |
---|---|
|
レポートするタスクの名前。 |
|
作成するレポートのタイプ。設定可能な値は、 |
|
レポートの詳細なレベル。
|
|
オプションで、レポートを単一のセクションに制限できます(すべてのセクションに関するレポートの場合は、
|
|
チューニング・セット(STS)の特定のSQLを表すアドバイザ・フレームワーク・オブジェクトの識別子。 |
|
レポート生成の対象となる、STS内のSQL文の数。 |
|
使用するタスク実行の名前。 |
|
関連する分析タスクの所有者。デフォルトは、現行のスキーマ所有者です。 |
|
レポート(要約および本文)でSQL文をソートする方法。有効な値は、次のとおりです。
|
戻り値
必要なレポートが含まれているCLOB
。
使用上のノート
ACTIVE
レポートは、Enterprise Managerと類似した豊富な対話型ユーザー・インタフェースを備えていますが、EMのインストールは必要ありません。レポート・ファイルはHTML
形式で作成されるため、最近のほとんどのブラウザで解釈可能です。アクティブ・レポートに機能を付与するコードは、最初に表示したときにWebブラウザにより透過的にダウンロードされるため、表示には外部接続が必要です。
例
-- Get the whole report for the single statement case. SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(:stmt_task) from dual; -- Show me the summary for the sts case. SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(:sts_task, 'TEXT', 'TYPICAL', 'SUMMARY') FROM DUAL; -- Show me the findings for the statement I'm interested in. SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(:sts_task, 'TEXT', 'TYPICAL', 'ALL', 5) from dual;
180.3.7 RESET_ANALYSIS_TASKプロシージャ
このプロシージャは、現在実行されていない分析タスクに対してコールされ、その分析タスクを再実行できるように準備します。
すべての中間結果データは削除されます。
構文
DBMS_SQLPA.RESET_ANALYSIS_TASK( task_name IN VARCHAR2);
パラメータ
表180-8 RESET_ANALYSIS_TASKプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
リセットする分析タスクの識別子。 |
例
-- reset and re-execute a task EXEC DBMS_SQLPA.RESET_ANALYSIS_TASK(:sts_task); -- re-execute the task EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(:sts_task);
180.3.8 RESUME_ANALYSIS_TASKプロシージャ
このプロシージャは、以前に中断されたか、または(致命的なエラーによって)FAILED
になったタスクの実行を再開します。
構文
DBMS_SQLPA.RESUME_ANALYSIS_TASK( task_name IN VARCHAR2, basic_filter IN VARCHAR2 := NULL);
パラメータ
表180-9 RESUME_ANALYSIS_TASKプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
再開する分析タスクの識別子。 |
|
SQLチューニング・セットからSQLをフィルタするためのSQL述語。このフィルタは、CREATE_ANALYSIS_TASKファンクションのコール時に、指定された基本フィルタ(パラメータ |
使用上のノート
単一のSQL分析タスク(SQLチューニング・セットではなく、1つのSQL文を分析するために作成されたタスク)の再開はサポートされていません。
例
-- Interrupt the task EXEC DBMS_SQLPA.INTERRUPT_ANALYSIS_TASK(:conc_task); -- Once a task is interrupted, we can elect to reset it, resume it, or check -- out its results and then decide. For this example we will just resume. EXEC DBMS_SQLPA.RESUME_ANALYSIS_TASK(:conc_task);
180.3.9 SET_ANALYSIS_TASK_PARAMETERプロシージャ
このプロシージャは、SQL分析タスク・パラメータの値を設定します。
構文
この形式のプロシージャは、VARCHAR2
タイプのSQL分析パラメータの値を更新します。
DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER( task_name IN VARCHAR2, parameter IN VARCHAR2, value IN VARCHAR2, test_execute_dop IN NUMBER DEFAULT 0, compare_resultset IN BOOLEAN DEFAULT TRUE);
この形式のプロシージャは、NUMBER
タイプのSQL分析パラメータの値を更新します。
DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER( task_name IN VARCHAR2, parameter IN VARCHAR2, value IN NUMBER test_execute_dop IN NUMBER DEFAULT 0, compare_resultset IN BOOLEAN DEFAULT TRUE);
パラメータ
表180-10 SET_ANALYSIS_TASK_PARAMETERプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
実行するタスクの識別子。 |
|
設定するパラメータの名前。このプロシージャによって設定可能な分析パラメータは、次のとおりです。
|
|
|
|
|
|
指定したパラメータの新しい値。 |
|
SPAタスクを実行する際に必要な同時実行性のレベルを指定します。 値 |
|
比較している2つの試行の結果セットが異なるかどうかを検出するようにSPAに指示します。比較している2つの試行間でSQL文の結果セットに相違点がある場合、SPA比較レポートはそのようなすべてのSQL文について相違点があることを示します。
|
使用上のノート
許可されるプロセスの実際の数は、test_execute_dop
パラメータを使用して要求した数以下になる場合があります。このパラメータは、SQLチューニング・セットを処理するtest-execute
またはexplain plan type
の試行にのみ適用されます。
例
2つの同時プロセスがSPAタスクを実行するように要求するには:
dbms_sqlpa.set_analysis_task_parameter(:tname,'TEST_EXECUTE_DOP',2)
結果セットの検証を有効にする場合:
exec dbms_sqlpa.set_analysis_task_parameter(:atname,'COMPARE_RESULTSET','TRUE')
結果セットの検証を無効にするには:
exec dbms_sqlpa.set_analysis_task_parameter(:atname,'COMPARE_RESULTSET','FALSE')
180.3.10 SET_ANALYSIS_DEFAULT_PARAMETERプロシージャ
このプロシージャは、SQL分析タスク・パラメータのデフォルト値を設定します。
構文
この形式のプロシージャは、VARCHAR2
タイプのアナライザ・パラメータのデフォルト値を更新します。
DBMS_SQLPA.SET_ANALYSIS_DEFAULT_PARAMETER( parameter IN VARCHAR2, value IN VARCHAR2);
この形式のプロシージャは、NUMBER
タイプのアナライザ・パラメータのデフォルト
値を更新します。
DBMS_SQLPA.SET_ANALYSIS_DEFAULT_PARAMETER( parameter IN VARCHAR2, value IN NUMBER);
パラメータ
表180-11 SET_ANALYSIS_DEFAULT_PARAMETERプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
設定するパラメータの名前。このプロシージャによって設定可能な分析パラメータは、次のとおりです。
|
|
|
|
指定したパラメータの新しい値。 |