6 SQL試行の比較
-
ワークロードに対する影響
これは、SQL文に対するこの変更が、ワークロードの累積実行時間に与える影響の割合を、実行頻度を考慮して示しています。たとえば、SQL文の累積実行時間を101秒から1秒に改善する変更(変更前の残りのワークロードの実行時間の合計は99秒)の場合、この測定値は50パーセント(2x)になります。
-
SQLに対する影響
これは、SQL文に対するこの変更が、SQL文の応答時間に与える影響の割合を示しています。たとえば、SQL文の応答時間を10秒から1秒に改善する変更の場合、この測定値は90パーセント(10x)になります。
この章では、変更前と変更後のSQL試行のパフォーマンス・データを比較して分析する方法について説明します。内容は次のとおりです。
ノート:
SQL試行を比較するための主要インタフェースは、Oracle Enterprise Managerです。なんらかの理由でOracle Enterprise Managerを使用できない場合は、DBMS_SQLPA
PL/SQLパッケージを使用してSQL試行を比較できます。
関連項目:
Oracle Enterprise Managerを使用したSQL試行の比較
Oracle Enterprise Managerを使用してSQL試行を比較するには、次のステップを実行します。
Oracle Enterprise Managerを使用したSQLパフォーマンスの分析
この項では、Oracle Enterprise Managerを使用してシステム変更の前後にSQLパフォーマンスを分析する方法について説明します。
Enterprise Managerを使用してSQLパフォーマンスを分析するには:
-
「ガイド付きワークフロー」ページで、「ステップ2とステップ3を比較」の「実行」アイコンをクリックします。
「SQL試行比較の実行」ページが表示されます。
この例では、
SQL_TRIAL_1241213421833
試行およびSQL_TRIAL_1241213881923
試行が比較用に選択されています。 -
デフォルトで示されている試行以外の試行を比較するには、「試行1の名前」リストおよび「試行2の名前」リストで目的の試行を選択します。
統計試行は実行計画のみをテストする試行とは比較できないことに注意してください。
-
「比較メトリック」リストで、比較分析に使用する比較メトリックを選択します。
-
経過時間
-
CPU時間
-
ユーザーI/O時間
-
バッファ読取り
-
物理I/O
-
オプティマイザ・コスト
-
I/Oインターコネクト・バイト
SQL試行で実行計画のみを生成した場合に選択可能な比較メトリックは「オプティマイザ・コスト」のみです。
複数の比較メトリックを使用して比較分析を実行するには、異なるメトリックを使用してこの手順を繰り返すことによって比較分析を別々に実行します。
-
-
「スケジュール」セクションで、次の手順を実行します。
-
「タイムゾーン」リストで、タイムゾーン・コードを選択します。
-
「即時」(即時にタスクを開始する場合)または、「後で」(「日付」および「時間」フィールドで指定した時間にタスクを開始するようスケジュールする場合)を選択します。
-
-
「発行」をクリックします。
比較分析が開始されると、「ガイド付きワークフロー」ページが表示されます。
比較分析中、このステップのステータス・アイコンは矢印アイコンに変わります。ステータス・アイコンをリフレッシュするには、「リフレッシュ」をクリックします。変更前および変更後の実行から収集したパフォーマンス・データの量によっては、比較分析が完了するまで時間がかかる場合があります。比較分析が完了したら、「ステータス」アイコンがチェック・マークに変わり、次のステップの「実行」アイコンが有効になります。
-
SQLパフォーマンス・アナライザが変更前と変更後のパフォーマンス・データを分析した後、詳細な分析に使用できるSQLパフォーマンス・アナライザ・レポートを生成します。
「ガイド付きワークフロー」ページで、「試行比較レポートの表示」の「実行」アイコンをクリックします。
「SQLパフォーマンス・アナライザのタスク・レポート」ページが表示されます。レポートを確認します。詳細は、「Oracle Enterprise Managerを使用したSQLパフォーマンス・アナライザ・レポートの確認」を参照してください。
Oracle Enterprise Managerを使用したSQLパフォーマンス・アナライザ・レポートの確認
SQLパフォーマンス・アナライザのタスクが完了すると、変更前と変更後のSQLパフォーマンスを比較するSQLパフォーマンス・アナライザ・レポートに結果のデータが生成されます。
図6-1に、SQLパフォーマンス・アナライザ・レポートのサンプルを示します。このサンプル・レポートでは、経過時間比較メトリックを使用して、変更前と変更後のSQLワークロードの実行を比較します。
SQLパフォーマンス・アナライザ・レポートを確認する前に、変更前と変更後のバージョンのパフォーマンス・データを比較します。詳細は、「Oracle Enterprise Managerを使用したSQL試行の比較」を参照してください
SQLパフォーマンス・アナライザ・レポートを生成および確認するには:
-
「パフォーマンス」メニューから「SQL」を選択し、「SQLパフォーマンス・アナライザ」を選択します。
「データベース・ログイン」ページが表示されたら、管理者権限のあるユーザーとしてログインします。
「SQLパフォーマンス・アナライザ・ホーム」ページが表示されます。既存のSQLパフォーマンス・アナライザのリストが表示されます。
-
「SQLパフォーマンス・アナライザのタスク」で、SQLパフォーマンス・アナライザ・レポートを表示するタスクを選択し、「最新レポートの表示」をクリックします。
「SQLパフォーマンス・アナライザのタスク・レポート」ページが表示されます。
-
パフォーマンス分析に関する一般情報を確認します。詳細は、「SQLパフォーマンス・アナライザ・レポートの確認: 一般情報」を参照してください。
-
一般的な統計を確認します。詳細は、「SQLパフォーマンス・アナライザ・レポートの確認: グローバル統計」を参照してください。
-
オプションで、詳細な統計を確認します。詳細は、「SQLパフォーマンス・アナライザ・レポートの確認: グローバル統計の詳細」を参照してください。
-
アクティブ・レポートを生成するには、「保存」をクリックしてレポートを生成し保存するか、「メール」をクリックしてレポートを生成しHTML添付としてメールで送信します。
アクティブ・レポートには、各カテゴリ(改善された計画、低下した計画および変更された計画)の上位SQL文と、変更前と変更後の統計、実行計画およびタスク・サマリーに関する情報が含まれます。
詳細は、「SQLパフォーマンス・アナライザのアクティブ・レポートについて」を参照してください。
SQLパフォーマンス・アナライザ・レポートの確認: 一般情報
「一般情報」セクションには、SQLパフォーマンス・アナライザによって実行されたワークロードの比較に関する基本情報およびメタデータが含まれています。
一般情報を確認するには:
SQLパフォーマンス・アナライザ・レポートの確認: グローバル統計
グローバル統計を確認するには:
SQLパフォーマンス・アナライザ・レポートの確認: グローバル統計の詳細
ノート:
SQL文の実際の数が100を超える場合でも、最大で上位100個のSQL文だけがレポートに表示されます。
グローバル統計の詳細を確認するには:
SQLパフォーマンス・アナライザのアクティブ・レポートについて
SQLパフォーマンス・アナライザのアクティブ・レポートは、従来のHTMLレポートやテキスト・レポートよりも便利で、Oracle Enterprise Managerと似たユーザー・インタフェースを備えながら、データベースを使用できないときやデータベースを削除した後でも参照できます。そのため、アクティブ・レポートは、従来のレポートと動的なOracle Enterprise Manager分析の利点を生かすと同時に、両者の短所を解消しています。また、アクティブ・レポートでは比較分析に関する詳しい情報が提供され、より多くのインタラクティブなユーザー・オプションを使用できます。HTMLレポートやテキスト・レポートのかわりにアクティブ・レポートを使用することを強くお薦めします。
アクティブ・レポートのユーザー・インタフェース・コンポーネントは、Oracle Enterprise Managerに表示されるコンポーネントとよく似ています。ユーザー・インタフェース・コンポーネントの説明は、「Oracle Enterprise Managerを使用したSQLパフォーマンス・アナライザ・レポートの確認」の関連する項を参照してください。
Oracle Enterprise Managerを使用した、パフォーマンスが低下したSQL文のチューニング
パフォーマンスが低下したSQL文をチューニングしたら、SQLパフォーマンス・アナライザを使用して、それらの変更をテストする必要があります。新しいSQL試行をテスト・システムで実行すると、2番目の比較(新しいSQL試行と最初の試行の比較)が実行され、結果が検証されます。SQLパフォーマンス・アナライザにパフォーマンスが安定していることが示されたら、テストは完了します。このステップでの修正を本番システムに実装してください。
Oracle Database 11g リリース1以上では、SQL文のチューニング時に、SQLチューニング・アドバイザによって代替計画が分析されます。SQLチューニング・アドバイザは、現在のシステムで以前の実行計画(最初のSQL試行の計画も含む)を検索します。最初のSQL試行の実行計画が2番目のSQL試行の実行計画と異なる場合、SQLチューニング・アドバイザは、最初のSQL試行の計画を推奨します。これらの実行計画のパフォーマンスがよい場合は、最初のSQL試行の計画を使用して計画ベースラインを作成できます。
ノート:
SQLパフォーマンス・アナライザでは、リモートSQL試行の完了直後に、SQL計画ベースラインを作成したり、SQLチューニング・アドバイザを実行するオプションを提供しません。このような場合、APIを使用して、SQLチューニング・セットを手動で転送し、リモート・データベースで適切な手順を完了する必要があります。
関連項目:
-
代替計画の分析の詳細は、『Oracle Database SQLチューニング・ガイド』を参照してください
SQL計画ベースラインの作成
SQL計画ベースラインを作成すれば、既知の性能特性に基づいて実行計画を使用することによって、オプティマイザのパフォーマンス低下を避けることができます。計画を変更したためにパフォーマンスの低下をきたした場合は、SQL計画ベースラインを作成してこれを使用し、新たにパフォーマンスが低下した実行計画をオプティマイザが選択するのを防ぐことができます。
SQL計画ベースラインを作成するには:
-
「SQLパフォーマンス・アナライザのタスク結果」ページの「推奨」で、「SQL計画ベースラインの作成」をクリックします。
「SQL計画ベースラインの作成」ページが表示されます。「低下したSQL文」セクションに、新しいSQL計画ベースラインに関連付けるパフォーマンスが低下したSQL文が示されます。
-
「ジョブ・パラメータ」で、次のようにジョブのパラメータを指定します。
-
「ジョブ名」フィールドに、ジョブの名前を入力します。
-
「説明」フィールドに、オプションでジョブの説明を入力します。
-
-
「スケジュール」で、次の項目を選択します。
-
即時: ジョブを即時開始します。
-
後で: 「タイムゾーン」、「日付」および「時間」フィールドを使用して指定した時間に開始するようにジョブをスケジュールします。
-
-
「OK」をクリックします。
「SQLパフォーマンス・アナライザのタスク結果」ページが表示されます。ジョブが正常に実行されたことを示すメッセージが表示されます。
関連項目:
-
SQL計画ベースラインの作成および管理の詳細は、『Oracle Database 2日でパフォーマンス・チューニング・ガイド』を参照してください
SQLチューニング・アドバイザの実行
SQLチューニング・アドバイザは、パフォーマンスが低下したSQL文を詳細に分析し、問題の根本的な原因を解決しようとします。
SQLチューニング・アドバイザを実行するには:
関連項目:
-
SQLチューニング・アドバイザの実行の詳細は、『Oracle Database 2日でパフォーマンス・チューニング・ガイド』を参照してください
APIを使用したSQL試行の比較
APIを使用したSQLパフォーマンスの分析
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK
プロシージャまたはファンクションを使用して、比較分析を実行します。
変更前と変更後のSQLパフォーマンス・データを比較するには:
-
次のパラメータを使用して
EXECUTE_ANALYSIS_TASK
プロシージャまたはファンクションをコールします。-
task_name
パラメータをSQLパフォーマンス・アナライザのタスクの名前に設定します。 -
execution_type
パラメータをCOMPARE PERFORMANCE
に設定します。この設定によって、2つのバージョンのSQLパフォーマンス・データが分析および比較されます。 -
execution_name
パラメータを使用して、実行を識別するための名前を指定します。指定しない場合は、SQLパフォーマンス・アナライザによって名前が生成され、ファンクションによって戻されます。 -
execution_params
パラメータを使用して、2つのバージョンのSQLパフォーマンス・データを指定します。execution_params
パラメータは、指定した実行の名前/値ペアとして指定されます。SQLパフォーマンス・データの比較および分析に関連する実行パラメータを次のように設定します。-
execution_name1
パラメータを最初の実行(システム変更が実行される前)の名前に設定します。この値は、「APIを使用した変更前のSQL試行の作成」で指定されているexecution_name
パラメータの値に対応している必要があります。 -
execution_name2
パラメータを2番目の実行(システム変更が実行された後)の名前に設定します。システム変更後にSQLワークロードを実行した場合、この値は、「APIを使用した変更後のSQL試行の作成」で指定されているexecution_name
パラメータの値に対応している必要があります。コール元が実行を指定しなかった場合、SQLパフォーマンス・アナライザではデフォルトで最後の2回のタスク実行が常に比較されます。 -
comparison_metric
パラメータを設定して、パフォーマンスに対する影響の分析に使用する実行統計の式を指定します。指定可能な値は、elapsed_time
(デフォルト)、cpu_time
、buffer_gets
、disk_reads
、direct_writes
、optimizer_cost
およびio_interconnect_bytes
の各メトリックまたはこれらの組合せです。
比較用に設定可能なその他のパラメータについては、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』の
DBMS_SQLPA
パッケージに関する項を参照してください。 -
次の例は、ファンクション・コールを示しています。
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', - execution_type => 'COMPARE PERFORMANCE', - execution_name => 'my_exec_compare', - execution_params => dbms_advisor.arglist(- 'comparison_metric', 'buffer_gets'));
-
-
次のパラメータを使用して
REPORT_ANALYSIS_TASK
ファンクションをコールします。-
task_name
パラメータをSQLパフォーマンス・アナライザのタスクの名前に設定します。 -
execution_name
パラメータを、使用する実行の名前に設定します。この値は、レポートの生成対象となる実行のexecution_name
パラメータと一致している必要があります。レポートを生成して結果を表示するには:
-
SQLワークロードに対して生成された実行計画の場合は、目的の
EXPLAIN PLAN
実行のexecution_name
パラメータと一致するようにこの値を設定します。 -
SQLワークロードに対して生成された実行計画および実行統計の場合は、目的の
TEST EXECUTE
実行で使用されたexecution_name
パラメータの値と一致するようにこのパラメータを設定します。 -
比較分析の場合は、目的の
ANALYZE PERFORMANCE
実行のexecution_name
パラメータと一致するようにこの値を設定します。
指定しなかった場合、SQLパフォーマンス・アナライザによって最後の実行に関するレポートが生成されます。
-
-
type
パラメータを設定して、生成するレポートのタイプを指定します。指定可能な値は、TEXT
(デフォルト)、HTML
、XML
およびACTIVE
です。アクティブ・レポートには、インタラクティブなユーザー・インタフェースを使用するきめ細かなレポート機能が用意されており、データベースやOracle Enterprise Managerに接続されていないときでも詳細な分析を実行できます。可能なかぎり、HTMLレポートやテキスト・レポートのかわりにアクティブ・レポートを使用することをお薦めします。
アクティブ・レポートの詳細は、「SQLパフォーマンス・アナライザのアクティブ・レポートについて」を参照してください。
-
level
パラメータを設定して、推奨事項の形式を指定します。指定可能な値は、TYPICAL
(デフォルト)、ALL
、BASIC
、CHANGED
、CHANGED_PLANS
、ERRORS
、IMPROVED
、REGRESSED
、TIMEOUT
、UNCHANGED
、UNCHANGED_PLANS
およびUNSUPPORTED
です。 -
section
パラメータを設定して、レポートに生成する特定のセクションを指定します。指定可能な値は、SUMMARY
(デフォルト)およびALL
です。 -
top_sql
パラメータを設定して、レポートに生成するSQLチューニング・セット内のSQL文の数を指定します。デフォルトでは、システム変更によって影響を受ける上位100のSQL文がレポートに示されます。
アクティブ・レポートを生成するには、次のスクリプトを実行します。
set trimspool on set trim on set pages 0 set linesize 1000 set long 1000000 set longchunksize 1000000 spool spa_active.html SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(task_name => 'my_spa_task', type => 'active', section => 'all') FROM dual; spool off
次に、比較サマリー・レポートをテキスト形式で作成し表示するためのSQLスクリプトの例の一部を示します。
VAR rep CLOB; EXEC :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK('my_spa_task', - 'text', 'typical', 'summary'); SET LONG 100000 LONGCHUNKSIZE 100000 LINESIZE 130 PRINT :rep
-
-
「コマンドラインを使用したSQLパフォーマンス・アナライザ・レポートの確認」で説明されているSQLパフォーマンス・アナライザ・レポートを確認してください。
関連項目:
-
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK
およびDBMS_SQLPA.REPORT_ANALYSIS_TASK
ファンクションの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
コマンドラインを使用したSQLパフォーマンス・アナライザ・レポートの確認
SQLパフォーマンス・アナライザ・レポートは、次のセクションに分類されます。
この項では、サンプル・レポートを使用して、SQLパフォーマンス・アナライザ・レポートを確認する方法について説明します。このサンプル・レポートでは、buffer_gets
を比較メトリックとして使用して、変更前と変更後のSQLワークロードの実行を比較します。
一般情報
例6-1では、タスク名がmy_spa_task
であることがタスク情報セクションに示されています。ワークロード情報セクションには、101のSQL文を含むSQLチューニング・セットmy_sts
の実行がタスクによって比較されたことが示されています。実行情報セクションには、比較実行名がmy_exec_compare
であることが示されています。
分析情報セクションには、SQLパフォーマンス・アナライザによって、比較メトリックのbuffer_gets
を使用してSQLチューニング・セットmy_sts
の2つの実行(my_exec_BEFORE_change
とmy_exec_AFTER_change
)が比較されたことが示されています。
例6-1 一般情報
--------------------------------------------------------------------------------------------- General Information --------------------------------------------------------------------------------------------- Task Information: Workload Information: --------------------------------------------- --------------------------------------------- Task Name : my_spa_task SQL Tuning Set Name : my_sts Task Owner : APPS SQL Tuning Set Owner : APPS Description : Total SQL Statement Count : 101 Execution Information: --------------------------------------------------------------------------------------------- Execution Name : my_exec_compare Started : 05/21/2007 11:30:09 Execution Type : ANALYZE PERFORMANCE Last Updated : 05/21/2007 11:30:10 Description : Global Time Limit : UNLIMITED Scope : COMPREHENSIVE Per-SQL Time Limit : UNUSED Status : COMPLETED Number of Errors : 0 Analysis Information: --------------------------------------------------------------------------------------------- Comparison Metric: BUFFER_GETS ------------------ Workload Impact Threshold: 1% -------------------------- SQL Impact Threshold: 1% ---------------------- Before Change Execution: After Change Execution: --------------------------------------------- --------------------------------------------- Execution Name : my_exec_BEFORE_change Execution Name : my_exec_AFTER_change Execution Type : TEST EXECUTE Execution Type : TEST EXECUTE Description : Description : Degree of Parallelism: 4 Degree of Parallelism: 4 Scope : COMPREHENSIVE Scope : COMPREHENSIVE Status : COMPLETED Status : COMPLETED Started : 05/21/2007 11:22:06 Started : 05/21/2007 11:25:56 Last Updated : 05/21/2007 11:24:01 Last Updated : 05/21/2007 11:28:30 Global Time Limit : 1800 Global Time Limit : 1800 Per-SQL Time Limit : UNUSED Per-SQL Time Limit : UNUSED Number of Errors : 0 Number of Errors : 0 ---------------------------------------------------------------------------------------------
結果のサマリー
全体的なパフォーマンス統計
例6-2に、サンプル・レポートの全体的なパフォーマンス統計サブセクションを示します。
この例では、低下の影響が-10.08%であったにもかかわらず、SQLワークロードの全体的なパフォーマンスは47.94%改善されたことを示しています。つまり、この例のパフォーマンスの低下がすべて改善された場合、変更による全体の影響は、58.02%ということになります。システム変更後、101のSQL文のうち2つは実行速度が速くなりましたが、1つは遅くなりました。98の文のパフォーマンスには変化がありませんでした。
例6-2 全体的なパフォーマンス統計
Report Summary --------------------------------------------------------------------------------------------- Projected Workload Change Impact: ------------------------------------------- Overall Impact : 47.94% Improvement Impact : 58.02% Regression Impact : -10.08% SQL Statement Count ------------------------------------------- SQL Category SQL Count Plan Change Count Overall 101 6 Improved 2 2 Regressed 1 1 Unchanged 98 3 . . . ---------------------------------------------------------------------------------------------
SQL文のパフォーマンス統計
-
各SQL文の実行頻度(重要性)
-
SQLワークロード全体に対する各SQL文へのシステム変更の影響
-
各SQL文へのシステム変更の影響
-
各SQL文の実行計画の構造が変更されたかどうか
例6-3に、サンプル・レポートのSQL文のパフォーマンス統計サブセクションを示します。レポートは、ページに収まるように少し変更してあります。
これらのSQL文は、SQLワークロードに対する最終的な影響の絶対値で降順にソートされています。つまり、ソート順序は、影響の良し悪しとは関係がありません。
例6-3 SQL文のパフォーマンス統計
SQL Statements Sorted by their Absolute Value of Change Impact on the Workload --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- | | | Impact on | Execution | Metric | Metric | Impact | Plan | | object_id | sql_id | Workload | Frequency | Before | After | on SQL | Change | --------------------------------------------------------------------------------------------- | 205 | 73s2sgy2svfrw | 29.01% | 100000 | 1681683 | 220590 | 86.88% | y | | 206 | gq2a407mv2hsy | 29.01% | 949141 | 1681683 | 220590 | 86.88% | y | | 204 | 2wtgxbjz6u2by | -10.08% | 478254 | 1653012 | 2160529 | -30.7% | y | ---------------------------------------------------------------------------------------------
エラー
例6-4に、SQLパフォーマンス・アナライザ・レポートのエラー・サブセクションの例を示します。
例6-4 エラー
---------------------------------------------------------------------------------- SQL STATEMENTS WITH ERRORS ---------------------------------------------------------------------------------- SQL ID Error ------------- -------------------------------------------------------------------- 47bjmcdtw6htn ORA-00942: table or view does not exist br61bjp4tnf7y ORA-00920: invalid relational operator ----------------------------------------------------------------------------------
結果の詳細
このセクションには、SQLパフォーマンスの影響分析で処理されたすべてのSQL文のエントリが示されます。各エントリは、次のサブセクションで構成されています。
SQLの詳細
レポートのこのセクションには、SQL文の概要(SQL文の情報および実行の詳細)が表示されます。
例6-5に、サンプル・レポートのSQLの詳細サブセクションを示します。
例6-5には、パフォーマンスが低下したSQL文(IDは2wtgxbjz6u2by
、対応するオブジェクトIDは204
)の概要が示されています。
例6-5 SQLの詳細
SQL Details: ----------------------------- Object ID : 204 Schema Name : APPS SQL ID : 2wtgxbjz6u2by Execution Frequency : 1 SQL Text : SELECT /* my_query_14_scott */ /*+ ORDERED INDEX(t1) USE_HASH(t1) */ 'B' || t2.pg_featurevalue_05_id pg_featurevalue_05_id, 'r' || t4.elementrange_id pg_featurevalue_15_id, 'G' || t5.elementgroup_id pg_featurevalue_01_id, 'r' || t6.elementrange_id . . . . . . ---------------------------------------------------------------------------------------------
実行統計
実行統計サブセクションでは、変更前と変更後の実行からのSQL文の実行統計が比較され、検出結果の概要が示されます。
例6-6に、サンプル・レポートの実行統計サブセクションを示します。
例6-6 実行統計
Execution Statistics: ----------------------------- --------------------------------------------------------------------------------------- | | Impact on | Value | Value | Impact | % Workload | % Workload | | Stat Name | Workload | Before | After | on SQL | Before | After | --------------------------------------------------------------------------------------- | elapsed_time | -95.54%| 36.484 | 143.161 | -292.39% | 32.68% | 94.73% | | parse_time | -12.37%| .004 | .062 | -1450% | .85% | 11.79% | | exec_elapsed | -95.89%| 36.48 | 143.099 | -292.27% | 32.81% | 95.02% | | exec_cpu | -19.73%| 36.467 | 58.345 | -59.99% | 32.89% | 88.58% | | buffer_gets | -10.08%| 1653012 | 2160529 | -30.7% | 32.82% | 82.48% | | cost | 12.17%| 11224 | 2771 | 75.31% | 16.16% | 4.66% | | reads | -1825.72%| 4091 | 455280 | -11028.82% | 16.55% | 96.66% | | writes | -1500%| 0 | 15 | -1500% | 0% | 100% | | rows | | 135 | 135 | | | | --------------------------------------------------------------------------------------- Notes: ----------------------------- Before Change: 1. The statement was first executed to warm the buffer cache. 2. Statistics shown were averaged over next 9 executions. After Change: 1. The statement was first executed to warm the buffer cache. 2. Statistics shown were averaged over next 9 executions. Findings (2): ----------------------------- 1. The performance of this SQL has regressed. 2. The structure of the SQL execution plan has changed. ---------------------------------------------------------------------------------------------
実行計画
実行計画サブセクションには、SQL文の変更前および変更後の実行計画が表示されます。パフォーマンスが低下した場合、このセクションには、根本原因および兆候についての検出結果も表示されます。
例6-7に、サンプル・レポートの実行計画サブセクションを示します。
例6-7 実行計画
Execution Plan Before Change: ----------------------------- Plan Id : 1 Plan Hash Value : 3412943215 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 126 | 11224 | 00:02:15 | | 1 | HASH GROUP BY | | 1 | 126 | 11224 | 00:02:15 | | 2 | NESTED LOOPS | | 1 | 126 | 11223 | 00:02:15 | | * 3 | HASH JOIN | | 1 | 111 | 11175 | 00:02:15 | | * 4 | TABLE ACCESS FULL | LU_ELEMENTGROUP_REL | 1 | 11 | 162 | 00:00:02 | | * 5 | HASH JOIN | | 487 | 48700 | 11012 | 00:02:13 | | 6 | MERGE JOIN | | 14 | 924 | 1068 | 00:00:13 | | 7 | SORT JOIN | | 5391 | 274941 | 1033 | 00:00:13 | | * 8 | HASH JOIN | | 5391 | 274941 | 904 | 00:00:11 | | * 9 | TABLE ACCESS FULL | LU_ELEMENTGROUP_REL | 123 | 1353 | 175 | 00:00:03 | | * 10 | HASH JOIN | | 5352 | 214080 | 729 | 00:00:09 | | * 11 | TABLE ACCESS FULL | LU_ITEM_293 | 5355 | 128520 | 56 | 00:00:01 | | * 12 | TABLE ACCESS FULL | ADM_PG_FEATUREVALUE | 1629 | 26064 | 649 | 00:00:08 | | * 13 | FILTER | | | | | | | * 14 | SORT JOIN | | 1 | 15 | 36 | 00:00:01 | | * 15 | TABLE ACCESS FULL | LU_ELEMENTRANGE_REL | 1 | 15 | 35 | 00:00:01 | | 16 | INLIST ITERATOR | | | | | | | * 17 | TABLE ACCESS BY INDEX ROWID | FACT_PD_OUT_ITM_293 | 191837 | 6522458 | 9927 | 00:02:00 | | 18 | BITMAP CONVERSION TO ROWIDS | | | | | | | * 19 | BITMAP INDEX SINGLE VALUE | FACT_274_PER_IDX | | | | | | * 20 | TABLE ACCESS FULL | LU_ELEMENTRANGE_REL | 1 | 15 | 49 | 00:00:01 | ---------------------------------------------------------------------------------------------------------- . . . Execution Plan After Change: ----------------------------- Plan Id : 102 Plan Hash Value : 1923145679 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 126 | 2771 | 00:00:34 | | 1 | HASH GROUP BY | | 1 | 126 | 2771 | 00:00:34 | | 2 | NESTED LOOPS | | 1 | 126 | 2770 | 00:00:34 | | * 3 | HASH JOIN | | 1 | 111 | 2722 | 00:00:33 | | * 4 | HASH JOIN | | 1 | 100 | 2547 | 00:00:31 | | * 5 | TABLE ACCESS FULL | LU_ELEMENTGROUP_REL | 1 | 11 | 162 | 00:00:02 | | 6 | NESTED LOOPS | | | | | | | 7 | NESTED LOOPS | | 484 | 43076 | 2384 | 00:00:29 | | * 8 | HASH JOIN | | 14 | 770 | 741 | 00:00:09 | | 9 | NESTED LOOPS | | 4 | 124 | 683 | 00:00:09 | | * 10 | TABLE ACCESS FULL | LU_ELEMENTRANGE_REL | 1 | 15 | 35 | 00:00:01 | | * 11 | TABLE ACCESS FULL | ADM_PG_FEATUREVALUE | 4 | 64 | 649 | 00:00:08 | | * 12 | TABLE ACCESS FULL | LU_ITEM_293 | 5355 | 128520 | 56 | 00:00:01 | | 13 | BITMAP CONVERSION TO ROWIDS | | | | | | | * 14 | BITMAP INDEX SINGLE VALUE | FACT_274_ITEM_IDX | | | | | | * 15 | TABLE ACCESS BY INDEX ROWID | FACT_PD_OUT_ITM_293 | 36 | 1224 | 2384 | 00:00:29 | | * 16 | TABLE ACCESS FULL | LU_ELEMENTGROUP_REL | 123 | 1353 | 175 | 00:00:03 | | * 17 | TABLE ACCESS FULL | LU_ELEMENTRANGE_REL | 1 | 15 | 49 | 00:00:01 | ------------------------------------------------------------------------------------------------------
APIを使用したSQLチューニング・セットの比較
DBMS_SQLPA
パッケージを使用すると、2つのSQLチューニング・セットを比較できます。たとえば、データベース・リプレイの使用時に、本番システムでワークロードの取得中に1つのSQLチューニング・セットを取得し、テスト・システムでワークロードのリプレイ中にもう1つ別のSQLチューニング・セットを取得することがあります。その後、SQL文を再実行しなくても、SQLパフォーマンス・アナライザを使用してそれらのSQLチューニング・セットを比較できます。これは、システム変更の前後にワークロードを実行する別のユーティリティ(カスタム・スクリプトなど)がすでに存在する場合に便利です。
SQLチューニング・セットを比較する場合、SQLパフォーマンス・アナライザはSQLチューニング・セットで取得した実行時統計を使用して比較分析を行い、一方のSQLチューニング・セットに存在し、もう一方のチューニング・セットには存在しない新しいSQL文や欠落しているSQL文をレポートします。2つのSQLチューニング・セット間の実行計画における変更もレポートされます。両方のSQLチューニング・セットのSQL文ごとに、検出されたパフォーマンスの改善と低下が、SQL文単位(実行ごとの平均統計値に基づいて計算)とワークロード全体(累積統計値に基づいて計算)でレポートされます。
APIを使用してSQLチューニング・セットを比較するには:
-
SQLパフォーマンス・アナライザ・タスクを作成するには、次の手順を実行します。
VAR aname varchar2(30); EXEC :aname := 'compare_s2s'; EXEC :aname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(task_name => :aname);
作成時にSQLチューニング・セットをタスクに関連付ける必要はありません。
-
最初のSQL試行を作成し、最初のSQLチューニング・セットを変換するには、次の手順を実行します。
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => :aname, - execution_type => 'convert sqlset', - execution_name => 'first trial', - execution_params => DBMS_ADVISOR.ARGLIST( 'sqlset_name', 'my_first_sts', - 'sqlset_owner', 'APPS'));
SQLSET_NAME
およびSQLSET_OWNER
タスク・パラメータを使用して、SQLチューニング・セットの名前と所有者を指定します。SQLパフォーマンス・アナライザ・タスクでSQLチューニング・セットの内容が複製されることはありません。かわりに、SQLチューニング・セットへの参照が、新しいSQL試行(この例では「first trial」)との関連付けに記録されます。 -
2番目のSQL試行を作成し、比較する2番目のSQLチューニング・セットと関連付けます。
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => :aname, - execution_type => 'convert sqlset', - execution_name => 'second trial', - execution_params => DBMS_ADVISOR.ARGLIST( 'sqlset_name', 'my_second_sts', - 'sqlset_owner', 'APPS'));
-
比較分析を実行して、2つのSQL試行(SQLチューニング・セット)のパフォーマンス・データを比較します。
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => :aname, - execution_type => 'compare', - execution_name => 'comparison', - execution_params => DBMS_ADVISOR.ARGLIST( 'workload_impact_threshold', 0, - 'sql_impact_threshold', 0));
この例では、ワークロードとSQLごとの影響しきい値は、比較のために0%に設定されています(デフォルト値は1%)。
-
比較分析が完了した後、
DBMS_SQLPA.REPORT_ANALYSIS_TASK
ファンクションを使用して、SQLパフォーマンス・アナライザ・レポートを生成します。APIを使用したSQLパフォーマンス・アナライザ・レポートの生成については、「APIを使用したSQLパフォーマンスの分析」を参照してください。
レポートを作成した後、そのレポートを確認して、2つのSQLチューニング・セットの内容の違いを識別します。例6-8に、2つのSQLチューニング・セットを比較して生成されたサンプル・レポートの分析情報セクションとレポート・サマリー・セクションを示します。
例6-8 分析情報とレポート・サマリー
Analysis Information: ------------------------------------------------------------------------------------------------ Before Change Execution: After Change Execution: --------------------------------------------- --------------------------------------------- Execution Name : first trial Execution Name : second trial Execution Type : CONVERT SQLSET Execution Type : CONVERT SQLSET Status : COMPLETED Status : COMPLETED Started : … Last Updated : … Before Change Workload: After Change Workload: --------------------------------------------- --------------------------------------------- SQL Tuning Set Name : my_first_sts SQL Tuning Set Name : my_second_sts SQL Tuning Set Owner : APPS SQL Tuning Set Owner : APPS Total SQL Statement Count : 5 Total SQL Statement Count : 6 ------------------------------------------------------------------------------------------------ Report Summary ------------------------------------------------------------------------------------------------ Projected Workload Change Impact: ------------------------------------------- Overall Impact : 72.32% Improvement Impact : 47.72% Regression Impact : -.02% Missing-SQL Impact : 33.1% New-SQL Impact : -8.48% SQL Statement Count ------------------------------------------- SQL Category SQL Count Plan Change Count Overall 7 1 Common 4 1 Improved 3 1 Regressed 1 0 Different 3 0 Missing SQL 1 0 New SQL 2 0
例6-8に示すように、このレポートには、標準のSQLパフォーマンス・アナライザ・レポートにはない、2つの追加カテゴリがあります。この2つのカテゴリは「Different」という見出しの下にまとめられています。
-
欠落したSQL
このカテゴリは、最初のSQLチューニング・セットに存在し、2番目のSQLチューニング・セットに存在しすべてのSQL文を示します。この例では、SQL文が1つだけ欠落しています。例6-9に示すように、このSQL文は次のような内容になっています。
-
sql_id
値: gv7xb8tyd1v91 -
変更ごとのワークロードのパフォーマンスへの影響: 33.1%
-
変更ごとのSQL文のパフォーマンスへの影響: なし(「Total Metric After」の変更値が欠落している)
-
-
新規SQL
このカテゴリは、2番目のSQLチューニング・セットに存在し、最初のSQLチューニング・セットに存在しないすべてのSQL文を示します。この例では、2番目のSQLチューニング・セットで新しく追加されたSQL文は2つだけです。例6-9に示すように、これらSQL文は次のような内容になっています。
-
sql_id
値: 4c8nrqxhtb2sfおよび9utadgu5udmh4 -
ワークロードに対するパフォーマンス全体への影響: -8.48%
-
「Total Metric Before」の変更値: なし
-
例6-9に、欠落しているSQL文と新しいSQL文を示すサンプル・レポートの表を示します。ワークロードへの影響ごとに特定されたその他の上位SQL文も示されています。
例6-9 ワークロードに対する変更の影響の絶対値ごとに分類された上位7つのSQL
Top 7 SQL Sorted by Absolute Value of Change Impact on the Workload ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ | | | Impact on | Total Metric | Total Metric | Impact | Plan | | object_id | sql_id | Workload | Before | After | on SQL | Change | ------------------------------------------------------------------------------------------ | 4 | 7gj3w9ya4d9sj | 41.04% | 812791 | 36974 | 95% | y | | 7 | gv7xb8tyd1v91 | 33.1% | 625582 | | | n | | 2 | 4c8nrqxhtb2sf | -8.35% | | 157782 | | n | | 1 | 22u3tvrt0yr6g | 4.58% | 302190 | 215681 | 28.63% | n | | 6 | fgdd0fd56qmt0 | 2.1% | 146128 | 106369 | 27.21% | n | | 5 | 9utadgu5udmh4 | -.13% | | 2452 | | n | | 3 | 4dtv43awxnmv3 | -.02% | 3520 | 3890 | -47.35% | n | ------------------------------------------------------------------------------------------
対象のSQL文を識別した後、そのSQL文のレポートを作成して、さらに詳しく調べることができます。たとえば、ワークロードに対して最も大きな影響のあった、sql_id
の値が7gj3w9ya4d9sjで、object_id
の値が4のSQL文を調べることもできます。
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(task_name => :aname, object_id => 4) rep FROM dual;
例6-10に、このSQL文に対して生成されたサンプル・レポートを示します。
例6-10 SQL文のサンプル・レポート
SQL Details: ----------------------------- Object ID : 4 SQL ID : 7gj3w9ya4d9sj SQL Text : /* my_csts_query1 */ select * FROM emp where empno=2 SQL Execution Statistics (average): --------------------------------------- --------------------------------------------------------- | | Impact on | Value | Value | Impact | | Stat Name | Workload | Before | After | on SQL | --------------------------------------------------------- | elapsed_time | 41.04% | .036945 | .001849 | 95% | | cpu_time | 13.74% | .004772 | .00185 | 61.24% | | buffer_gets | 9.59% | 8 | 2 | 69.01% | | cost | 11.76% | 1 | 1 | 10% | | reads | 4.08% | 0 | 0 | 63.33% | | writes | 0% | 0 | 0 | 0% | | rows | | 0 | 0 | | | executions | | 22 | 20 | | | plan_count | | 3 | 2 | | --------------------------------------------------------- Findings (2): ----------------------------- 1. The performance of this SQL has improved. 2. The structure of the SQL execution plan has changed. Plan Execution Statistics (average): --------------------------------------- ---------------------------------------------------------------------------------- | Statistic Name | Plans Before Change | Plans After Change | ---------------------------------------------------------------------------------- | plan hash value | 440231712 571903972 3634526668 | 571903972 3634526668 | | --------------- | --------- --------- ---------- | --------- ---------- | | schema name | APPS1 APPS2 APPS2 | APPS2 APPS2 | | executions | 7 5 10 | 10 10 | | cost | 2 1 2 | 1 2 | | elapsed_time | .108429 .000937 .00491 | .000503 .003195 | | cpu_time | .00957 .0012 .0032 | .0005 .0032 | | buffer_gets | 18 0 5 | 0 5 | | reads | 0 0 0 | 0 0 | | writes | 0 0 0 | 0 0 | | rows | 0 0 0 | 0 0 | ---------------------------------------------------------------------------------- Execution Plans Before Change: ----------------------------- Plan Hash Value : 440231712 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 | | | 1 | PX COORDINATOR | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 87 | 2 | 00:00:01 | | 3 | PX BLOCK ITERATOR | | 1 | 87 | 2 | 00:00:01 | | 4 | TABLE ACCESS FULL | EMP | 1 | 87 | 2 | 00:00:01 | --------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Plan Hash Value : 571903972 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | | | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 87 | 1 |00:00:01| | 2 | INDEX UNIQUE SCAN | MY_EMP_IDX | 1 | | 0 | | ---------------------------------------------------------------------------------- Plan Hash Value : 3634526668 -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 | | | 1 | TABLE ACCESS FULL | EMP | 1 | 87 | 2 | 00:00:01 | -------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Executions Plan After Change: ----------------------------- Plan Hash Value : 571903972 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | | | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 87 | 1 |00:00:01| | 2 | INDEX UNIQUE SCAN | MY_EMP_IDX | 1 | | 0 | | ---------------------------------------------------------------------------------- Plan Hash Value : 3634526668 -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 | | | 1 | TABLE ACCESS FULL | EMP | 1 | 87 | 2 | 00:00:01 | -------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement ----------------------------------------------------------------------------------
「SQL Execution Statistics」セクションには、SQL文の(実行ごとの)平均実行時統計が表示されます。この表のデータから、このSQL文は両方のSQLチューニング・セットに存在していますが、最初のSQLチューニング・セットの実行計画は3つ、2番目のSQLチューニング・セットの実行計画は2だということがわかります。さらに、SQL文は最初のSQLチューニング・セットでは22回実行されたのに対し、2番目のSQLチューニング・セットでは20回しか実行されていません。
「Plan Execution Statistics」セクションには、実行計画(または計画ハッシュ値)ごとの実行時統計が表示されます。「Plans Before Change」列には、最初のSQLチューニング・セットについての計画とその計画に関連付けられた実行統計が表示され、「Plans After Change」列には、2番目のSQLチューニング・セットについてのそれらの値が表示されています。レポートの最後に、両方のSQLチューニング・セットの実行計画の構造が示されています。
レポートのこれらのセクションを使用して、2つのSQLチューニング・セット間の実行計画の違いを識別できます。これは、実行計画の違いによって、テスト結果が変わり、パフォーマンスに直接影響が出る可能性があるため重要です。2つのSQLチューニング・セットを比較した場合、SQL文が次のように設定されていると、SQLパフォーマンス・アナライザは実行計画の違いをレポートします。
-
両方のSQLチューニング・セットに計画が1つであるにもかかわらず、計画の構造が異なっている。
-
計画が複数で、両方のSQLチューニング・セットの計画の数が次のようになっている。
-
計画の数は同じでも、2番目のSQLチューニング・セットの1つ以上の計画が、最初のSQLチューニング・セットのすべての計画と異なっている。
-
計画数が異なっている
-
SQL文と計画の違いを評価した後、さらに処置が必要かどうかを決定します。SQL文のパフォーマンスが低下している場合は、次のいずれかの処置を実行します。
-
パフォーマンスが低下したSQL文をチューニングします。詳細は、「APIを使用した、パフォーマンスが低下したSQL文のチューニング」を参照してください
-
SQL計画ベースラインを作成します。詳細は、「APIを使用したSQL計画ベースラインの作成」を参照してください
APIを使用した、パフォーマンスが低下したSQL文のチューニング
SQLパフォーマンス・アナライザ・レポートを確認したら、SQLパフォーマンスの比較後に特定されるパフォーマンスが低下したSQL文をチューニングする必要があります。パフォーマンスが低下したSQL文が多数表示される場合は、根本原因を特定し、システム・レベルの変更を行って問題を修正する必要があります。パフォーマンスが低下したSQL文が少数の場合は、SQLチューニング・アドバイザを使用して、部分的な解決を検討するか、SQL計画ベースラインを作成して、今後は変更前の実行計画を選択するようにオプティマイザに指示します。
APIを使用して、パフォーマンスが低下したSQL文をチューニングするには:
-
DBMS_SQLTUNE
パッケージのCREATE_TUNING_TASK
ファンクションを使用して、SQLパフォーマンス・アナライザを実行するためのSQLチューニング・タスクを作成します。BEGIN DBMS_SQLTUNE.CREATE_TUNING_TASK( spa_task_name => 'my_spa_task', spa_task_owner => 'immchan', spa_compare_exec => 'my_exec_compare'); DBMS_SQLTUNE.EXECUTE_TUNING_TASK(spa_task_name => 'my_spa_task'); END; /
この例では、
my_spa_task
という名前のSQLパフォーマンス・アナライザ・タスクについて、my_exec_compare
という名前でパフォーマンスの比較を実行したときにパフォーマンスが低下したSQL文をチューニングするSQLチューニング・タスクを作成し、実行します。この場合、このバージョンのCREATE_TUNING_TASK
ファンクション・コールを使用することが重要です。そうしないと、SQL文が取得された本番システムの環境でチューニングが行われる可能性があるため、システムの変更が反映されません。ノート:
別のデータベースでSQLワークロードをリモートで実行することを選択した場合は、パフォーマンスが低下したSQL文のチューニングに、このバージョンの
CREATE_TUNING_TASK
ファンクション・コールを使用しないでください。かわりに、リモート・データベース上のSQL試行によって識別されたパフォーマンスの低下をチューニングしてください(これは、アプリケーション・スキーマがSQLパフォーマンス・アナライザを実行しているデータベース上に存在していないためです)。そのため、スキーマが存在し、変更が行われたデータベースで、SQLチューニング・アドバイザを実行する必要があります。
表6-1に、DBMS_SQLTUNE
.CREATE_TUNING_TASK
ファンクションで使用できるSQLパフォーマンス・アナライザのパラメータを示します。
表6-1 CREATE_TUNING_TASKファンクションのSQLパフォーマンス・アナライザのパラメータ
パラメータ | 説明 |
---|---|
|
SQLパフォーマンス・アナライザ・タスクの名前。 |
|
指定したSQLパフォーマンス・アナライザ・タスクの所有者。指定しないと、デフォル値の現行ユーザーになります。 |
|
指定したSQLパフォーマンス・アナライザ・タスクのパフォーマンスの比較試行の実行名。指定しないと、デフォルト値として、指定したSQLパフォーマンス・アナライザ・タスクで最後に実行した |
パフォーマンスが低下したSQL文をチューニングしたら、SQLパフォーマンス・アナライザを使用して、それらの変更をテストする必要があります。新しいSQL試行をテスト・システムで実行すると、2番目の比較(新しいSQL試行と最初の試行の比較)が実行され、結果が検証されます。SQLパフォーマンス・アナライザによってパフォーマンスが安定していることが表示されたら、このステップで行った修正を本番システムに対して実行します。
Oracle Database 11g リリース2以上では、SQL文のチューニング時に、SQLチューニング・アドバイザによって代替計画が分析されます。SQLチューニング・アドバイザは、自動ワークロード・リポジトリに保存されている履歴計画も含め、SQL文の実行履歴を確認します。SQLチューニング・アドバイザによって代替計画が検出された場合は、そのSQL文で適切な実行計画が使用されるように、特定の計画を選択して計画ベースラインを作成できます。
関連項目:
-
SQLチューニング・アドバイザの使用方法の詳細は、『Oracle Database SQLチューニング・ガイド』を参照してください
-
代替計画の分析の詳細は、『Oracle Database SQLチューニング・ガイド』を参照してください
-
DBMS_SQLTUNE
パッケージの詳細は、Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンスを参照
APIを使用した、リモートSQL試行からのパフォーマンスが低下したSQL文のチューニング
別のデータベースでSQLワークロードをリモートで実行することを選択した場合は、SQLパフォーマンス・アナライザ・タスクが存在するシステムではなく、リモート・データベースでSQL試行によって識別されたパフォーマンスの低下をチューニングする必要があります。
APIを使用して、リモートSQL試行からのパフォーマンスが低下したSQL文をチューニングするには:
-
SQLパフォーマンス・アナライザが実行されているシステムで、パフォーマンスが低下しているSQL文のサブセットをSQLチューニング・セットとして作成します。
DECLARE sqlset_cur DBMS_SQLTUNE.SQLSET_CURSOR; BEGIN DBMS_SQLTUNE.CREATE_SQLSET('SUB_STS1', 'test purpose'); OPEN sqlset_cur FOR SELECT value(p) FROM table( DBMS_SQLTUNE.SELECT_SQLPA_TASK( task_name => 'SPA_TASK1', execution_name => 'COMP', level_filter => 'REGRESSED')) p; DBMS_SQLTUNE.LOAD_SQLSET('SUB_STS1', sqlset_cur); CLOSE sqlset_cur; END; /
'REGRESSED'
以外にも、'CHANGED'
、'ERRORS'
、'CHANGED_PLANS'
などのフィルタを使用して、SQLチューニング・セットのSQL文を選択できます。詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。 -
SQLチューニング・セットをエクスポートするステージング表を作成します。
BEGIN DBMS_SQLTUNE.CREATE_STGTAB_SQLSET( table_name => 'STG_TAB1', schema_name => 'JOHNDOE', tablespace_name => 'TBS_1', db_version => DBMS_SQLTUNE.STS_STGTAB_11_1_VERSION); END; /
db_version
パラメータを使用して、SQLチューニング・セットのエクスポートとチューニングを行う適切なバージョンのデータベースを指定します。この例では、Oracle Database 11g リリース1を実行しているシステムにエクスポートできる形式でステージング表を作成し、後でSQLチューニング・アドバイザを使用してこの表をチューニングします。その他のデータベース・バージョンの詳細は、そのリリースの『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。 -
SQLチューニング・セットをステージング表にエクスポートするには、次の手順を実行します。
BEGIN DBMS_SQLTUNE.PACK_STGTAB_SQLSET( sqlset_name => 'SUB_STS1', sqlset_owner => 'JOHNDOE', staging_table_name => 'STG_TAB1', staging_schema_owner => 'JOHNDOE', db_version => DBMS_SQLTUNE.STS_STGTAB_11_1_VERSION); END; /
-
任意の方法(Oracle Data Pumpやデータベース・リンクなど)でステージング表を(SQLワークロードが実行された)リモート・データベースに移動します。
-
リモート・データベースで、ステージング表からSQLチューニング・セットをインポートします。
BEGIN DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET( sqlset_name => 'SUB_STS1', staging_table_name => 'STG_TAB1', replace => TRUE); END; /
-
SQLチューニング・アドバイザを実行し、SQLチューニング・セット内のパフォーマンスが低下したSQL文をチューニングします。
BEGIN sts_name := 'SUB_STS1'; sts_owner := 'JOHNDOE'; tune_task_name := 'TUNE_TASK1'; tname := DBMS_SQLTUNE.CREATE_TUNING_TASK(sqlset_name => sts_name, sqlset_owner => sts_owner, task_name => tune_task_name); EXEC DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(:tname, 'APPLY_CAPTURED_COMPILENV', 'FALSE'); exec_name := DBMS_SQLTUNE.EXECUTE_TUNING_TASK(tname); END; /
ノート:
この例で使用する
APPLY_CAPTURED_COMPILENV
パラメータは、Oracle Database 11gリリース1以上のリリースでのみサポートされています。これより前のバージョンのOracle Databaseからデータベース・アップグレードをテストする場合は、かわりにSQLチューニング・セットに保存されている環境変数が使用されます。
パフォーマンスが低下したSQL文をチューニングしたら、SQLパフォーマンス・アナライザを使用して、それらの変更をテストする必要があります。新しいSQL試行をテスト・システムで実行すると、2番目の比較(新しいSQL試行と最初の試行の比較)が実行され、結果が検証されます。SQLパフォーマンス・アナライザによってパフォーマンスが安定していることが表示されたら、このステップで行った修正を本番システムに対して実行します。
関連項目:
-
SQLチューニング・アドバイザの使用方法およびSQLチューニング・セットの転送の詳細は、『Oracle Database SQLチューニング・ガイド』を参照してください
-
DBMS_SQLTUNE
パッケージの詳細は、Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンスを参照
APIを使用したSQL計画ベースラインの作成
SQLチューニング・アドバイザを実行するためのもう1つの方法として、計画の変更によってパフォーマンスが低下したSQL文のSQL計画ベースラインを作成することがあります。これにより、このようなSQL文に対しては、今後変更前の実行計画を使用するように、オプティマイザに指示されます。
元の計画用のSQL計画ベースラインを作成するには:
関連項目:
-
SQL計画ベースラインの詳細は、『Oracle Database SQLチューニング・ガイド』を参照してください
-
DBMS_SPM
パッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
SQLパフォーマンス・アナライザのビューの使用
次のビューを問い合せて、SQLパフォーマンス・アナライザを監視し、分析結果を表示することができます。
ノート:
これらのビューで参照可能な情報は、SQLパフォーマンス・アナライザ・レポートにも含まれています。かわりに、SQLパフォーマンス・アナライザ・レポートを使用して分析結果を表示することをお薦めします。結果のより詳細な分析を実行する場合にのみ、これらのビューを使用することを検討してください。
-
DBA_ADVISOR_TASKS
およびUSER_ADVISOR_TASKS
ビューには、作成されたSQLパフォーマンス・アナライザのタスクに関する記述情報が表示されます。 -
DBA_ADVISOR_EXECUTIONS
およびUSER_ADVISOR_EXECUTIONS
ビューには、タスク実行に関する情報が表示されます。SQLパフォーマンス・アナライザでは、3つ以上の実行が作成され、SQLワークロードに対するデータベース変更によって発生するSQLパフォーマンスへの影響が分析されます。最初の実行では、変更前バージョンのパフォーマンス・データが収集されます。2番目の実行では、変更後バージョンのパフォーマンス・データが収集されます。3番目の実行では、比較分析が実行されます。 -
DBA_ADVISOR_FINDINGS
およびUSER_ADVISOR_FINDINGS
ビューには、SQLパフォーマンス・アナライザの結果が表示されます。SQLパフォーマンス・アナライザでは、次のタイプの結果が生成されます。-
問題(パフォーマンスの低下など)
-
兆候(実行計画の構造が変化した場合など)
-
エラー(オブジェクトまたはビューが存在しない場合など)
-
情報メッセージ(変更前バージョンの実行計画の構造がSQLチューニング・セットに格納されているものと異なる場合など)
-
-
DBA_ADVISOR_SQLPLANS
およびUSER_ADVISOR_SQLPLANS
ビューには、すべての実行計画のリストが表示されます。 -
DBA_ADVISOR_SQLSTATS
およびUSER_ADVISOR_SQLSTATS
ビューには、すべてのSQLコンパイルおよび実行統計のリストが表示されます。 -
V$ADVISOR_PROGRESS
ビューには、SQLパフォーマンス・アナライザの操作の進行状況が表示されます。このビューを使用して、完了しているSQL文の数およびSQL試行で実行待機中のSQL文の数を監視します。SOFAR
列は、これまでに処理されたSQL文の数を示し、TOTAL WORK
列は、タスクの実行によって処理されるSQL文の合計を示します。
DBAビューにアクセスするには、SELECT_CATALOG_ROLE
ロールが必要です。
関連項目:
-
DBA_ADVISOR_TASKS
、DBA_ADVISOR_EXECUTIONS
およびDBA_ADVISOR_SQLPLANS
ビューの詳細は、『Oracle Databaseリファレンス』を参照してください