X

A blog about Oracle Technology Network Japan

  • August 25, 2020

津島博士のパフォーマンス講座 第77回 実行計画の比較について

Guest Author

津島博士のパフォーマンス講座 Indexページ ▶▶


皆さんこんにちは、今年も昨年以上に厳しい夏になっていますが、体調に気をつけてください。
今回は、データベースの変更(バージョンアップなど)でパフォーマンス比較として使用するSQLパフォーマンス・アナライザ(SPA:SQL Performance Analyzer)とOracle Database 19c(Oracle19c)からの実行計画の違いの原因を特定する機能の二つの実行計画の比較について説明しますので、参考にしてください。

 

1. SQLパフォーマンス・アナライザ

SQLの実行計画に影響を与える変更は、SQLパフォーマンスにも深刻な影響を与える可能性があります。DBAは、変更によって劣化したSQL文を特定して、修正するために多くの時間と労力を費やします。そのため、Oracle Database 11gからのSPAにより、実行計画に影響を与える変更を事前に予測・防止できるようになりました(ただし、Real Application Testingオプションが必要です)。また、SPAで問題のSQL文を特定してから、手動でSQLチューニング・アドバイザやSQL Plan Management(SPM)などを行うような使い方も可能なため、SQLチューニングも簡単に行えます。

(1)一般的な使用シーン
まずは、SPAの一般的な使用シーンから説明します。
SPAは、SQL文の実行計画や実行統計に影響を与えるシステム変更を分析することに使用できます。一般的なシステム変更としては、以下のようなものがあり、変更前と変更後を比較することで、事前にSQLパフォーマンスの影響を確認することができます。

  • データベースのアップグレード
    データベースのアップグレードは、SQLパフォーマンスに影響を与えるオプティマイザを更新するので、特定のSQL文のパフォーマンスが劣化する可能性があります。
  • データベースの初期化パラメータの変更
    データベースの初期化パラメータを変更すると、予期しない影響が生じることがあります。
  • スキーマの変更
    索引の変更や新しい索引の作成などの変更は、ほぼ必然的にSQLのパフォーマンスに影響を与えます。
  • オプティマイザ統計情報の更新
    新しいオプティマイザ統計を収集することは、オプティマイザのコスト計算と生成される実行計画に大きな影響を与えます。
  • オペレーティング・システムおよびハードウェアの変更
    新しいオペレーティング・システム、CPUやメモリの追加、Oracle Real Application Clusters環境への移行などの変更は、SQLパフォーマンスに大きな影響を与える可能性があります。
  • チューニング推奨事項の実装
    アドバイザ(ADDM、SQLチューニング・アドバイザ、SQLアクセス・アドバイザなど)からの推奨事項は、実装する前に効果を検証する必要があるかもしれません(遅くなるSQL文が存在するかもしれません)。

(2)SPAの使用方法
次に、SPAの使用方法について説明します。
SPAは、データベースの変更前後で、SQLチューニング・セット(STS)内のSQL文を比較して、SQLパフォーマンスに与える影響を評価します。そのシステム変更の評価は、以下のような手順で行います(以下のように、DBMS_SQLPAパッケージを使用して手動で実行するか、Enterprise Managerを使用して実行することができます)。

…<SQLワークロードの取得(SQLチューニング・セットに格納)>…

/* ① SPAタスクの作成(SQLチューニング・セットの設定) */
VARIABLE v_task VARCHAR2(64);
EXEC :v_task :=  DBMS_SQLPA.create_analysis_task(sqlset_name => 'spa_test_sqlset');
/* ② 変更前のSQL実行 */
BEGIN
  DBMS_SQLPA.execute_analysis_task(task_name      => :v_task,
                                   execution_type => 'test execute',
                                   execution_name => 'before_change');
END;
/

…<システム変更の実施>…

/* ③ 変更後のSQL実行 */
BEGIN
  DBMS_SQLPA.execute_analysis_task(task_name      => :v_task,
                                   execution_type => 'test execute',
                                   execution_name => 'after_change');
END;
/
/* ④ SQLパフォーマンスの比較 */
BEGIN
  DBMS_SQLPA.execute_analysis_task(task_name        => :v_task,
                                   execution_type   => 'compare performance', 
                                   execution_params => dbms_advisor.arglist('execution_name1','before_change', 
                                                                            'execution_name2','after_change'));
END;
/
/* ⑤ レポートの出力 */
SET LINESIZE 1000 PAGESIZE 0
SET LONG 1000000 LONGCHUNKSIZE 1000000
SELECT DBMS_SQLPA.report_analysis_task(:v_task, 'TEXT', 'SUMMARY') FROM DUAL;

※コードがうまく表示されない方はこちらから.txtをダウンロードしてご確認ください。

 

SPAの施行タイプには、以下の三つのモードがあります(上記の例は、'TEST EXECUTE'で行っています)。

  • 実行計画だけを取得する(EXPLAIN PLAN)
    テスト・データを使用できないときに、オプティマイザ統計だけで実行計画の変化までを分析します。
  • SQLを実行して性能も取得する(TEST EXECUTE)
    テスト環境でテスト・データが使用できるときに行います。
  • STSの実行統計と計画を変換する(Convert SQLSET)
    SQLの実行を省略したいときに、STSの実行統計と実行計画などを変更前のテスト結果として使用します。ただし、動作環境の違い(同時実行など)による性能差に注意が必要です。

SPAには、テスト環境が用意できない場合でもテストを実施できるSPA Quick Checkもあり、本番環境で簡単に素早くテスト(初期化パラメータの影響、保留オプティマイザ統計の影響、SQLプロファイルの影響などの確認)ができるようになっています。

(3)SQLパフォーマンスの比較
次に、SQLパフォーマンスの比較方法について説明します。
SPAは、変更前と変更後のSQL試行で収集されたパフォーマンス・データを比較し、SQL文の実行計画やパフォーマンスの変化を特定するレポートを作成します。以下のように、execution_paramsパラメータにcomparison_metricパラメータを設定して、パフォーマンスの分析に使用する実行統計の式が指定できます。

/* SQLパフォーマンスの比較 */
BEGIN
  DBMS_SQLPA.execute_analysis_task(task_name        => :v_task,
                                   execution_type   => 'compare performance',
                                   execution_params => dbms_advisor.arglist('execution_name1','before_change',
                                                                            'execution_name2','after_change',
                                                                            'comparison_metric','buffer_gets'));
END;
/

※コードがうまく表示されない方はこちらから.txtをダウンロードしてご確認ください。

 

指定可能な値は、elapsed_time(デフォルト)、cpu_time、buffer_gets、disk_reads、direct_writes、optimizer_cost、io_interconnect_bytesの各メトリックまたはこれらの組合せです。一般的なパフォーマンスの比較には、buffer_gets(論理リード)を使用すると判断しやすいと思います(elapsed_timeやdisk_readsで比較した場合、バッファ・キャッシュの状況やDBインスタンスの利用状況などによって誤差が生じるため、適切な判断がしにくいことがあります)。
SPAレポートの出力例が以下になります(sectionパラメータがSUMMARYのため「結果の詳細」は出力されていません)。これで実行計画が変化したSQLやパフォーマンス劣化のSQLなどが判断できます。

※コードはこちらから.txtをダウンロードしてご確認ください。

 

(4)SQLチューニング・セット
最後に、SQLワークロードの取得に使用するSQLチューニング・セット(STS)について説明します。
STSは、チューニング・ツール(SQLチューニング・アドバイザ、SPMなど)への入力やデータベース間でのSQL転送などに使用できる便利なデータベース・オブジェクト(1つ以上のSQL文とその実行統計や実行コンテキスト)で、SYSAUX表領域に格納されています。STSの使用可能なソースには、AWR、共有SQL領域、SQLトレース・ファイル、他のSTSなどがあり、以下のように行うことでSTSを作成します(以下の例は、SELECT_CURSOR_CACHEテーブル・ファンクションを使用して、SPA_TESTスキーマによってSQL解析された"my_obj"が含むSQL文のカーソルを取得し、LOAD_SQLSETプロシージャを使用してSTSにロードします)。

/* STSの作成 */
EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => 'spa_test_sqlset');
/* STSへのロード */
DECLARE
  l_cursor  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPEN l_cursor FOR
     SELECT VALUE(a) FROM TABLE(
              DBMS_SQLTUNE.select_cursor_cache(
                basic_filter   => 'sql_text LIKE ''%my_obj%'' and parsing_schema_name = ''SPA_TEST''',
                attribute_list => 'ALL')
            ) a;
  DBMS_SQLTUNE.load_sqlset(sqlset_name => 'spa_test_sqlset', populate_cursor => l_cursor);
END;
/

※コードがうまく表示されない方はこちらから.txtをダウンロードしてご確認ください。

 

以下のように、DBA_SQLSET_STATEMENTSビューでSTSに関連付けられているSQL文を確認できます。

SQL> select SQL_TEXT from DBA_SQLSET_STATEMENTS where SQLSET_NAME='spa_test_sqlset';

SQL_TEXT
--------------------------------------------------------------------------------
SELECT object_name FROM my_objects WHERE object_id = 100

※コードがうまく表示されない方はこちらから.txtをダウンロードしてご確認ください。

 

STSは、DBMS_SQLTUNEパッケージ(Oracle Tuning Packが必要)を使用していましたが、Oracle Database 18cからTuning Packの必要ないDBMS_SQLSETパッケージが使用できるようになりました。

 

2. 実行計画の比較

ここでは、Oracle19cからの実行計画の比較機能について説明します。
実行計画の比較で難しいのは、単純な行ごとに比較しても意味がなく、より複雑な分析で論理的な違いを特定する必要があるからです。そのため、慣れていない方だと行うことが難しいので、Oracle19cから簡単に比較する機能が提供されています(以前からのDBMS_XPLAN.DIFF_PLANファンクションでも実行計画の比較ができるようですが、詳細な説明がないので、この機能を使用してください)。

(1)使用用途
実行計画の比較は、比較する実行計画に対して、作成した比較レポートで相違の原因を特定することができ、実行計画の再現性の問題を調査する際に使用できます。この比較レポートは、特に以下のようなシナリオ(様々なユースケース)で役立ちます。

  • パフォーマンスが低下している問合せの計画と古い計画を比較する場合
    実行計画の変更が発生した場合、それを古い計画(AWRに保存された計画など)と比較して、調査するときがあります。
  • SQL計画ベースラインの再現に失敗したときの新しい計画との相違点を判断する場合
    計画ベースライン内の計画が再現されない場合、生成された計画とどのように異なっているかを確認する必要があります。
  • 計画に与える影響(ヒントの追加方法など)を判断する場合
    特定のヒントの追加、パラメータの変更、索引の作成などが、計画にどのように影響するかを確認したいときがあります。
  • SQLプロファイルやSPAの異なる点を判断する場合
    新しいSQLプロファイルに基づいて生成された計画と元の計画、SPAによって示された計画が、どのように異なっているかを確認したいときがあります。

同じSQL文なのに、実行計画が変化した理由が分からない場合があります。また、実行計画の行数が多いと慣れた方でも比較するのが大変です。そのような場合に、このレポートが原因の究明に役立つかと思います。

(2)使用方法
実行計画の比較は、DBMS_XPLAN.COMPARE_PLANSファンクションに、参照プラン(reference_plan)と比較プラン・リスト(compare_plan_list)を指定して行います。参照プランが単一の計画ソース、比較プラン・リストがplan_object_listタイプによる汎用オブジェクト・リスト(複数の計画ソース)を、入力として使用できます(つまり、複数の計画ソースの実行計画を同時に比較することができます)。実行計画は、様々な場所に存在するので、以下のような計画ソースを指定することができるようになっています。オプション・バラメータがNULLのときには、複数のオブジェクトと比較することを意味します(例えば、cursor_cache_objectにchild_numberを指定しないと、指定したSQL_IDのすべての実行計画と比較します)。

計画ソース 指定方法
PLAN TABLE plan_table_object(owner, plan_table_name, statement_id, plan_id)
カーソル・キャッシュ cursor_cache_object(sql_id, child_number)
AWR awr_object(sql_id, dbid, con_dbid, plan_hash_value)
SQLチューニング・セット sqlset_object(sqlset_owner, sqlset_name, sql_id, plan_hash_value)
SQL計画管理 spm_object(sql_handle, plan_name)
SQLプロファイル sql_profile_object(profile_name)
アドバイザ advisor_object(task_name, execution_name, sql_id, plan_id)

 

以下のように実行して、比較レポートを出力します(この例は、カーソル・キャッシュの実行計画を比較して、TEXTタイプのレポートを出力しています)。

VARIABLE v_rep CLOB
BEGIN
  :v_rep := DBMS_XPLAN.COMPARE_PLANS( 
    reference_plan    => cursor_cache_object('0hxmvnfkasg6q', NULL),
    compare_plan_list => plan_object_list(cursor_cache_object('10dqxjph6bwum', NULL)),
    type              => 'TEXT',
    level             => 'TYPICAL', 
    section           => 'ALL');
END;
/

SET LINESIZE 210 SET PAGESIZE 50000
SET LONG 100000
COLUMN report FORMAT a200
SELECT :v_rep REPORT FROM DUAL;

※コードがうまく表示されない方はこちらから.txtをダウンロードしてご確認ください。

 

比較レポートの出力例が以下になります。比較プランごとに「Comparison Results」セクションが出力されます(この例は、参照プランの問合せのみが第34回で説明した「結合の排除」変換を使用していることを示しています)。

※コードがうまく表示されない方はこちらから.txtをダウンロードしてご確認ください。

 

3. おわりに

今回は、実行計画の比較について説明しましたが、少しは参考になりましたでしょうか。これからも頑張りますのでよろしくお願いします。
それでは、次回まで、ごきげんよう。    

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.