| Oracle® Database Testingガイド 12cリリース1 (12.1) B71349-07 |
|
 前 |
 次 |
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計画ベースラインの作成」を参照してください
