| Oracle® Database Real Application Testingユーザーズ・ガイド 11gリリース2(11.2) B56321-06 |
|
 前 |
 次 |
変更後のSQLのパフォーマンス・データが構築されたら、SQLパフォーマンス・アナライザを使用した比較分析を実行して、変更前と変更後のSQL試行で収集されたパフォーマンス・データを比較できます。比較分析が完了したら、レポートを生成して、システム変更によりパフォーマンスが改善されたSQL文、パフォーマンスの変化がなかったSQL文またはパフォーマンスが低下したSQL文を特定できます。SQLパフォーマンス・アナライザ・レポートでは、各SQL文のパフォーマンスの変化に関して、2つの主要な影響の大きさを計算します。
ワークロードに対する影響
これは、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パフォーマンスを分析する方法について説明します。
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パフォーマンス・アナライザ・レポートの確認」の説明に従って、レポートを確認します。
SQLパフォーマンス・アナライザのタスクが完了すると、変更前と変更後のSQLパフォーマンスを比較するSQLパフォーマンス・アナライザ・レポートに結果のデータが生成されます。
図6-1に、SQLパフォーマンス・アナライザ・レポートのサンプルを示します。このサンプル・レポートでは、経過時間比較メトリックを使用して、変更前と変更後のSQLワークロードの実行を比較します。
|
ヒント: SQLパフォーマンス・アナライザ・レポートを確認する前に、変更前と変更後のバージョンのパフォーマンス・データを比較します。詳細は、「Oracle Enterprise Managerを使用したSQL試行の比較」を参照してください。 |
SQLパフォーマンス・アナライザ・レポートを生成および確認するには、次の手順を実行します。
「ソフトウェアとサポート」ページの「Real Application Testing」で、「SQLパフォーマンス・アナライザ」をクリックします。
「SQLパフォーマンス・アナライザ」ページが表示されます。既存のSQLパフォーマンス・アナライザのリストが表示されます。
「SQLパフォーマンス・アナライザのタスク」で、SQLパフォーマンス・アナライザ・レポートを表示するタスクを選択し、「最新レポートの表示」をクリックします。
「SQLパフォーマンス・アナライザのタスク・レポート」ページが表示されます。
パフォーマンス分析に関する一般情報を確認します。詳細は、「SQLパフォーマンス・アナライザ・レポートの確認: 一般情報」を参照してください。
一般的な統計を確認します。「SQLパフォーマンス・アナライザ・レポートの確認: グローバル統計」を参照してください。
オプションで、詳細な統計を確認します。詳細は、「SQLパフォーマンス・アナライザ・レポートの確認: グローバル統計の詳細」を参照してください。
アクティブ・レポートを生成するには、「保存」をクリックしてレポートを生成し保存するか、「メール」をクリックしてレポートを生成しHTML添付ファイルとしてメールで送信します。
アクティブ・レポートには、各カテゴリ(改善された計画、低下した計画および変更された計画)の上位SQL文と、変更前と変更後の統計、実行計画およびタスク・サマリーに関する情報が含まれます。
詳細は、「SQLパフォーマンス・アナライザのアクティブ・レポートについて」を参照してください。
「一般情報」セクションには、SQLパフォーマンス・アナライザによって実行されたワークロードの比較に関する基本情報およびメタデータが含まれています。
一般情報を確認するには、次の手順を実行します。
「SQLパフォーマンス・アナライザのタスク・レポート」ページで、ページの上部にあるサマリーを確認します。
このサマリーには次の情報が含まれています。
SQLチューニング・セットの名前および所有者
チューニング・セットのSQL文の合計と、エラーが発生したSQL文の数、サポートされていないSQL文の数、またはタイムアウトになったSQL文の数
SQL試行および使用される比較メトリックの名前
オプションで、「SQLチューニング・セット名」の横にあるリンクをクリックします。
「SQLチューニング・セット」ページが表示されます。
このページには、SQLチューニング・セット内のすべてのSQL文に関する情報(SQL IDやSQLテキストなど)が含まれています。
エラーが検出された場合、「エラーのあるSQL文」の横にあるリンクをクリックします。
エラー表には、特定のSQLワークロードの実行中に発生したすべてのエラーがレポートされます。エラーは、SQLチューニング・セット内のすべてのSQLの実行に共通する場合はSQLチューニング・セット・レベル、SQL文または実行計画に固有の場合は実行レベルでレポートされます。
グローバル統計を確認します。詳細は、「SQLパフォーマンス・アナライザ・レポートの確認: グローバル統計」を参照してください。
「グローバル統計」セクションにより、SQLワークロードの全体的なパフォーマンスについての統計がレポートされます。このセクションによりSQLワークロードの全体的なパフォーマンスに対するシステム変更の影響をレポートされるため、SQLパフォーマンス・アナライザ分析の非常に重要な部分を占めます。このセクションの情報を使用して、ワークロード・パフォーマンスの傾向を理解し、システム変更によりワークロード・パフォーマンスがどのように影響を受けるかを判断します。
グローバル統計を確認するには、次の手順を実行します。
「予測されるワークロードの経過時間」サブセクション内のグラフを確認します。
|
注意: サブセクションの名前は、選択された比較メトリックによって異なります。 |
グラフでは、X軸に2つの試行、Y軸に経過時間(秒)が示されます。
グラフの1つ目(左)の棒は、各SQL文の実行頻度によって考慮されたシステム変更前のすべてのSQL文の累計経過時間を示しています。2つ目(左)の棒は、各SQL文の実行頻度によって考慮されたシステム変更後のすべてのSQL文の累計経過時間を示しています。全体的な影響は、改善の影響と低下の影響とで異なり、パーセントで示されます。
「SQLパフォーマンス・アナライザ・レポートの確認: グローバル統計」の説明に従って、あらゆる影響統計のリンクをクリックして詳細を確認できます。
|
注意: 全体的な影響の割合は、改善の影響と低下の影響の合計と比較して1%ほどずれることがあります。この相違は、数値の丸めによって起こるもの、あるいはSQLやワークロードの時間制限を推奨値である1%に設定した場合に起こるものと考えられます。したがって、影響の少ないSQL文を除外することにより、影響の大きいSQL分を重点的に分析することができます。 |
「SQL文の数」サブセクション内のグラフを確認します。
グラフのX軸には、システム変更後にパフォーマンスが改善されたSQL文、パフォーマンスが低下したSQL文またはパフォーマンスの変更がなかったSQL文の数が示されます。Y軸には、SQL文の数が示されます。また、グラフには、SQL文に対して実行計画が変更されたかどうかも示されます。
このグラフにより、SQL文の相対的なパフォーマンスを迅速に比較できます。「SQLパフォーマンス・アナライザ・レポートの確認: グローバル統計の詳細」の説明に従って、グラフ内のいずれかのバーをクリックしてSQL文の詳細を確認できます。実際のSQL文の数が100を超えている場合でも、最大100個のSQL文のみ表示されます。
SQLパフォーマンス・アナライザ・レポートを使用して、SQLワークロードの比較の詳細な統計を取得できます。詳細なグラフによって、レポートに表示されるSQL文のパフォーマンスにドリルダウンできます。このセクションの情報を使用して、特定のSQL文のパフォーマンスが低下した原因を調査します。
|
注意: SQL文の実際の数が100を超える場合でも、最大で上位100個のSQL文だけがレポートに表示されます。 |
グローバル統計の詳細を確認するには、次の手順を実行します。
「予測されるワークロードの経過時間」サブセクションで、詳細を確認するSQL文の影響のパーセントをクリックします。SQL文の詳細を確認するには、パフォーマンスの状態に応じて次の操作を実行します。
改善された場合は、「改善の影響」のパーセントをクリックします。
低下した場合は、「低下の影響」のパーセントをクリックします。
改善されたか、または低下した場合は、「全体の影響」のパーセントをクリックします。
詳細な統計を含む表が表示されます。選択したSQL文のタイプに応じて、次の列が含まれます。
「SQL ID」
この列には、SQL文のIDが示されます。
「ワークロードに対する最終的な影響(%)」
この列には、SQLワークロードのパフォーマンスに対するシステム変更による影響が示されます。
経過時間
この列には、SQL文の実行にかかる合計時間(秒)が示されます。
SQLに対する最終的な影響(%)
この列には、特定のSQL文のパフォーマンスに対する変更による局所的な影響が示されます。
新規計画
この列には、SQLの実行計画が変更されたかどうかが示されます。
特定のSQL文の詳細を表示するには、対象のSQL文の「SQL ID」リンクをクリックします。
「SQLの詳細」ページが表示されます。
このページを使用して、SQLテキストにアクセスし、SQL文に関する低レベルの詳細(実行統計、実行計画など)を取得できます。
SQLパフォーマンス・アナライザのアクティブ・レポートは、ウェブホスト型のインタラクティブ・ユーザー・インタフェースを使用してレポート対象のすべてのデータを表示するHTMLファイルです。アクティブ・レポートには、Oracle Enterprise Managerで利用可能なSQLパフォーマンス・アナライザ・レポートと同様に、各カテゴリ(改善された計画、低下した計画および変更された計画)の上位SQL文と、変更前と変更後の統計、実行計画およびタスク・サマリーに関する情報が含まれます。
SQLパフォーマンス・アナライザのアクティブ・レポートは、従来のHTMLレポートやテキスト・レポートよりも便利で、Oracle Enterprise Managerと似たユーザー・インタフェースを備えながら、データベースを使用できないときやデータベースを削除した後でも参照できます。そのため、アクティブ・レポートは、従来のレポートと動的なOracle Enterprise Manager分析の利点を生かすと同時に、両者の短所を解消しています。また、アクティブ・レポートでは比較分析に関する詳しい情報が提供され、より多くのインタラクティブなユーザー・オプションを使用できます。HTMLレポートやテキスト・レポートのかわりにアクティブ・レポートを使用することを強くお薦めします。
アクティブ・レポートのユーザー・インタフェース・コンポーネントは、Oracle Enterprise Managerに表示されるコンポーネントとよく似ています。ユーザー・インタフェース・コンポーネントの説明は、「Oracle Enterprise Managerを使用したSQLパフォーマンス・アナライザ・レポートの確認」の関連する項を参照してください。
SQLパフォーマンス・アナライザ・レポートを確認したら、SQLパフォーマンスの比較後に特定されるパフォーマンスが低下したSQL文をチューニングする必要があります。パフォーマンスが低下したSQL文が多数表示される場合は、根本原因を特定し、システム・レベルの変更を行って問題を修正する必要があります。パフォーマンスが低下した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チューニング・セットを手動で転送し、リモート・データベースで適切な手順を完了する必要があります。 |
|
関連項目:
|
SQL計画ベースラインを作成すれば、既知の性能特性に基づいて実行計画を使用することによって、オプティマイザのパフォーマンス低下を避けることができます。計画を変更したためにパフォーマンスの低下をきたした場合は、SQL計画ベースラインを作成してこれを使用し、新たにパフォーマンスが低下した実行計画をオプティマイザが選択するのを防ぐことができます。
SQL計画ベースラインを作成するには、次の手順を実行します。
「SQLパフォーマンス・アナライザのタスク結果」ページの「推奨」で、「SQL計画ベースラインの作成」をクリックします。
「SQL計画ベースラインの作成」ページが表示されます。「低下したSQL文」セクションに、新しいSQL計画ベースラインに関連付けるパフォーマンスが低下したSQL文が示されます。
「ジョブ・パラメータ」で、次のようにジョブのパラメータを指定します。
「ジョブ名」フィールドに、ジョブの名前を入力します。
「説明」フィールドに、オプションでジョブの説明を入力します。
「スケジュール」で、次の項目を選択します。
即時: ジョブを即時開始します。
後で: 「タイムゾーン」、「日付」および「時間」フィールドを使用して指定した時間に開始するようにジョブをスケジュールします。
「OK」をクリックします。
「SQLパフォーマンス・アナライザのタスク結果」ページが表示されます。ジョブが正常に実行されたことを示すメッセージが表示されます。
|
関連項目:
|
SQLチューニング・アドバイザは、パフォーマンスが低下したSQL文を詳細に分析し、問題の根本的な原因を解決しようとします。
SQLチューニング・アドバイザを実行するには、次の手順を実行します。
「SQLパフォーマンス・アナライザのタスク結果」ページの「推奨」で、「SQLチューニング・アドバイザの実行」をクリックします。
「SQLチューニング・タスクのスケジュール」ページが表示されます。
「チューニング・タスク名」フィールドに、SQLチューニング・タスクの名前を入力します。
「チューニング・タスクの説明」フィールドに、オプションでSQLチューニング・タスクの名前を入力します。
「スケジュール」で、次の項目を選択します。
即時: ジョブを即時開始します。
後で: 「タイムゾーン」、「日付」および「時間」フィールドを使用して指定した時間に開始するようにジョブをスケジュールします。
「OK」をクリックします。
「SQLパフォーマンス・アナライザのタスク結果」ページが表示されます。SQLチューニング・レポートへのリンクが「推奨」の下に表示されます。
SQLチューニング・レポートを表示するには、「SQLチューニング・レポート」リンクをクリックします。
「SQLチューニング結果」ページが表示されます。
|
関連項目:
|
APIを使用してSQL試行を比較するには、次の手順を実行します。
変更後の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パフォーマンス・アナライザ・レポートを確認します。詳細は、「APIを使用したSQLパフォーマンス・アナライザ・レポートの確認」を参照してください。
|
関連項目:
|
SQLパフォーマンス・アナライザ・レポートは、次のセクションに分類されます。
この項では、サンプル・レポートを使用して、SQLパフォーマンス・アナライザ・レポートを確認する方法について説明します。このサンプル・レポートでは、buffer_getsを比較メトリックとして使用して、変更前と変更後のSQLワークロードの実行を比較します。
一般情報セクションには、SQLパフォーマンス・アナライザのタスク、使用されたSQLチューニング・セット、変更前と変更後の実行に関する基本情報およびメタデータが含まれています。例6-1に、サンプル・レポートの一般情報セクションを示します。
例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 : 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-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)が比較されたことが示されています。
結果のサマリー・セクションには、SQLパフォーマンス・アナライザのタスクの結果の概要が示されます。結果のサマリー・セクションは、次のサブセクションに分類されます。
全体的なパフォーマンス統計のサブセクションには、SQLワークロードの全体的なパフォーマンスについての統計が表示されます。このセクションによりSQLワークロードの全体的なパフォーマンスに対するシステム変更の影響が示されるため、SQLパフォーマンス・アナライザ分析の非常に重要な部分を占めます。このセクションの情報を使用して、ワークロード・パフォーマンスの変化を理解し、システム変更後にワークロード・パフォーマンスが向上するか低下するかを判断します。
例6-2に、サンプル・レポートの全体的なパフォーマンス統計サブセクションを示します。
例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 . . . ---------------------------------------------------------------------------------------------
この例では、低下の影響が-10.08%であったにもかかわらず、SQLワークロードの全体的なパフォーマンスは47.94%改善されたことを示しています。つまり、この例のパフォーマンスの低下がすべて改善された場合、変更による全体の影響は、58.02%ということになります。システム変更後、101のSQL文のうち2つは実行速度が速くなりましたが、1つは遅くなりました。98の文のパフォーマンスには変化がありませんでした。
パフォーマンス統計サブセクションでは、システム変更によって最も影響を受けるSQL文が強調表示されます。ワークロード内のSQL文ごとに、次の条件に基づいて変更前と変更後のパフォーマンス・データが比較されます。
各SQL文の実行頻度(重要性)
SQLワークロード全体に対する各SQL文へのシステム変更の影響
各SQL文へのシステム変更の影響
各SQL文の実行計画の構造が変更されたかどうか
例6-3に、サンプル・レポートの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 | ---------------------------------------------------------------------------------------------
これらのSQL文は、SQLワークロードに対する最終的な影響の絶対値で降順にソートされています。つまり、ソート順序は、影響の良し悪しとは関係がありません。
エラー・サブセクションには、実行中に発生したすべてのエラーがレポートされます。エラーは、SQLチューニング・セット内のすべての実行に共通する場合はSQLチューニング・セット・レベル、SQL文または実行計画に固有の場合は実行レベルでレポートされます。
例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文の概要(SQL文の情報および実行の詳細)が表示されます。
例6-5に、サンプル・レポートのSQLの詳細サブセクションを示します。
例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 . . .
.
.
.
---------------------------------------------------------------------------------------------
例6-5には、パフォーマンスが低下したSQL文(IDは2wtgxbjz6u2by、対応するオブジェクトIDは204)の概要が示されています。
実行統計サブセクションでは、変更前と変更後の実行からの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 | ------------------------------------------------------------------------------------------------------
SQLパフォーマンス・アナライザAPIを使用して、2つのSQLチューニング・セットを比較できます。たとえば、データベース・リプレイの使用時に、本番システムでワークロードの取得中に1つのSQLチューニング・セットを取得し、テスト・システムでワークロードのリプレイ中にもう1つ別のSQLチューニング・セットを取得することがあります。その後、SQL文を再実行しなくても、SQLパフォーマンス・アナライザを使用してそれらのSQLチューニング・セットを比較できます。これは、システム変更の前後にワークロードを実行する別のユーティリティ(カスタム・スクリプトなど)がすでに存在する場合に便利です。
SQLチューニング・セットを比較する場合、SQLパフォーマンス・アナライザはSQLチューニング・セットで取得した実行時統計を使用して比較分析を行い、一方のSQLチューニング・セットに存在し、もう一方のチューニング・セットには存在しない新しいSQL文や欠落しているSQL文をレポートします。2つのSQLチューニング・セット間の実行計画における変更もレポートされます。両方のSQLチューニング・セットのSQL文ごとに、検出されたパフォーマンスの改善と低下が、SQL文単位(実行ごとの平均統計値に基づいて計算)とワークロード全体(累積統計値に基づいて計算)でレポートされます。
DBMS_SQLPAパッケージを使用して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計画ベースラインの作成」を参照)。
SQLパフォーマンス・アナライザ・レポートを確認したら、SQLパフォーマンスの比較後に特定されるパフォーマンスが低下したSQL文をチューニングする必要があります。パフォーマンスが低下したSQL文が多数表示される場合は、根本原因を特定し、システム・レベルの変更を行って問題を修正する必要があります。パフォーマンスが低下したSQL文が少数の場合は、SQLチューニング・アドバイザを使用して、部分的な解決を検討するか、SQL計画ベースラインを作成して、今後は変更前の実行計画を選択するようにオプティマイザに指示します。
APIを使用して、SQLパフォーマンス・アナライザからレポートされた、パフォーマンスが低下した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チューニング・アドバイザを実行する必要があります。詳細は、「APIを使用した、リモート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ワークロードをリモートで実行することを選択した場合は、SQLパフォーマンス・アナライザ・タスクが存在するシステムではなく、リモート・データベースでSQL試行によって識別されたパフォーマンスの低下をチューニングする必要があります。
パフォーマンスが低下した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チューニング・アドバイザを実行するためのもう1つの方法として、計画の変更によってパフォーマンスが低下したSQL文のSQL計画ベースラインを作成することがあります。これにより、このようなSQL文に対しては、今後変更前の実行計画を使用するように、オプティマイザに指示されます。
変更前の計画に対してSQL計画ベースラインを作成するには、まずパフォーマンスが低下したSQL文だけのSQLチューニング・セットのサブセットを作成します。次に、DBMS_SPMパッケージのLOAD_PLANS_FROM_SQLSETファンクションを使用して、計画をロードすることによって、このSQL文のサブセットのSQL計画ベースラインを作成します(次の例を参照)。
DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'regressed_sql');
END;
/
|
関連項目:
|
次のビューを問い合せて、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ロールが必要です。
|
関連項目:
|
