再現ケースを簡単に作成！SQLテスト・ケース・ビルダーの使用方法

SQLテストケースビルダーの使用方法

特定のSQL文で意図した実行計画にならないことやエラーが発生するような問題が発生した際に、本番環境でトライ&エラーで回避策を検討することは非常に難しいものです。

このようなケースでは、可能な限り本番環境に合わせたテストを行うために表、索引、制約などの定義情報、データ、実行SQL文などの「再現ケース」を検証環境に作成する必要がありますが、個別に取得するとかなりの手間になり、かつ、抜けや漏れが起こる可能性が高まります。

今回はOracle Database 11gR1から追加された、再現ケースに必要な情報を一括で容易に取得できる「SQLテスト・ケース・ビルダー」の使用方法を紹介します。

SQLテスト・ケース・ビルダーの機能

SQLテスト・ケース・ビルダーとは、SQLの実行でエラーが発生する、意図しない実行計画が生成されるといった問題が発生した際に、別の環境で問題を再現させるのに必要な情報を一括で収集してくれるツールです。※SEでも利用可能

再現ケースのエクスポートは DBMS_SQLDIAG.EXPORT_SQL_TESTCASE プロシージャで行い、別環境に DBMS_SQLDIAG.IMPORT_SQL_TESTCASE を使用してインポートします。具体的には次のような情報が取得されます。

SQL文
表の定義
索引の定義
実データ(含めるかはオプションで選択可能)
PL/SQLファンクション／プロシージャ／パッケージ
統計情報
初期化パラメータ設定

DBMS_SQLDIAG.EXPORT_SQL_TESTCASEを使用した情報の取得方法はいくつかありますが、今回はSQL_ID/PLAN_HASH_VALUEを指定してエクスポートする方法を解説します。

再現ケースのエクスポート

値の偏りが大きくヒストグラム統計がないために、1行の結果を得るためにINDEX SCANではなくFULL SCANが選択されてしまっているSQLがあります。今回はこのSQLを対象に再現ケースのEXPORTを行います。

 SQL> select count(*) from t where c1 = 3;
　
   COUNT(*)
 ----------
          1
　
　
 実行計画
 ----------------------------------------------------------
 Plan hash value: 2966233522
　
 ---------------------------------------------------------------------------
 | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------
 |   0 | SELECT STATEMENT   |      |     1 |     3 |   483   (2)| 00:00:01 |
 |   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
 |*  2 |   TABLE ACCESS FULL| T    |   333K|   976K|   483   (2)| 00:00:01 |
 ---------------------------------------------------------------------------

SQLが実行されると共有プール上に情報がキャッシュされるため、V$SQLから問題が発生したSQLのSQL_IDとPLAN_HASH_VALUEを確認します。

 SQL> conn /as sysdba
 接続されました。
　
 SQL> SELECT
   2      sql_id
   3      ,plan_hash_value
   4      ,sql_text
   5  FROM
   6      V$SQL
   7  WHERE
   8      sql_text like '%from t where c1 = 3%';
　
 SQL_ID        PLAN_HASH_VALUE SQL_TEXT
 ------------- --------------- ------------------------------------
 2kyz4nhsb019j      2966233522 select count(*) from t where c1 = 3

再現ケースを格納するディレクトリを作成し、DBMS_SQLDIAG.EXPORT_SQL_TESTCASEを使用して再現ケースをエクスポートします。

 SQL> CREATE OR REPLACE DIRECTORY testcase_dir AS 'C:\TEMP\TESTCASE';
　
 ディレクトリが作成されました。
　
 SQL>   DECLARE
   2      V_TESTCASE CLOB;
   3    BEGIN
   4      DBMS_SQLDIAG.EXPORT_SQL_TESTCASE(
   5        DIRECTORY  => 'TESTCASE_DIR'       --再現ケース出力先
   6        ,sql_id => '2kyz4nhsb019j'         --V$SQLで確認したSQL_ID
   7        ,plan_hash_value => 2966233522     --V$SQLで確認したPLAN_HASH_VALUE
   8        ,exportData => TRUE                --データを含めるか
   9        ,exportPkgbody=>TRUE               --依存パッケージを含めるか
  10        ,testcase_name => 'badplan_tc'     --再現ケースの名前
  11        ,testcase   => V_TESTCASE);
  12    end;
  13    /
　
 PL/SQLプロシージャが正常に完了しました。

DBMS_SQLDIAG.EXPORT_SQL_TESTCASEが正常に実行できると、DIRECTORYオプションで指定した箇所に次のようなファイルが作成されます。<testcase_name>dpexp.logを確認し、エラーが発生していない（データや定義が正常にEXPORTできている）ことを確認します。

 C:\ora>dir C:\TEMP\TESTCASE
  C:\TEMP\TESTCASE のディレクトリ
 2016/08/05  13:16              .
 2016/08/05  13:16              ..
 2016/08/05  13:15        11,796,480 BADPLAN_TCDPEXP.DMP
 2016/08/05  13:15             1,247 badplan_tcdpexp.log
 2016/08/05  13:14             4,299 badplan_tcdpexp.sql
 2016/08/05  13:16             4,089 badplan_tcdpimp.sql
 2016/08/05  13:16             2,153 badplan_tcmain.xml
 2016/08/05  13:14               424 badplan_tcol.xml
 2016/08/05  13:14               402 badplan_tcprmimp.sql
 2016/08/05  13:14             2,783 badplan_tcREADME.txt
 2016/08/05  13:16             1,034 badplan_tcsmrpt.html
 2016/08/05  13:14               199 badplan_tcsql.xml
 2016/08/05  13:16               847 badplan_tcssimp.sql
 2016/08/05  13:14                67 badplan_tcts.xml
 2016/08/05  13:16             2,300 badplan_tcxpl.txt
 2016/08/05  13:16               442 badplan_tcxplf.sql
 2016/08/05  13:16               695 badplan_tcxplo.sql
 2016/08/05  13:16               416 badplan_tcxpls.sql

これで再現ケースのEXPORTは完了です。

再現ケースのインポート

C:\TEMP\TESTCASE配下に出力されたファイル群を検証用環境の任意のディレクトリに配置します。配置先のディレクトリに対してCREATE DIRECTORYコマンドでディレクトリを作成します。

 SQL> CREATE OR REPLACE DIRECTORY tc_imp_dir AS 'C:\TEMP\TESTCASE\IMPORT';

<testcace_name>_README.txtに記載のとおり、再現ケース実行用のユーザを作成してDBA権限を与えます。ユーザ名はEXPORT元と同一である必要はありません。作成したユーザに接続後、DBMS_SQLDIAG.IMPORT_SQL_TESTCASEを実行すると再現ケースがIMPORTされます。なお、オブジェクトはIMPORTしたユーザのデフォルト表領域に作成されます。

 SQL> CREATE USER tc IDENTIFIED BY tc DEFAULT TABLESPACE users;
　
 ユーザーが作成されました。
　
 SQL> GRANT dba TO tc;
　
 権限付与が成功しました。
　
 SQL> conn tc/tc
 接続されました。
　
 SQL> BEGIN
   2      DBMS_SQLDIAG.IMPORT_SQL_TESTCASE(
   3      directory => 'TC_IMP_DIR'         --再現ケース配置先ディレクトリ
   4     ,importdata => true                --データをIMPORTするか
   5     ,importpkgbody => true             --依存パッケージをIMPORTするか
   6     ,filename => 'badplan_tcmain.xml'  --testcase_namemain.xmlを指定
   7     );
   8  END;
   9  /
　
 PL/SQLプロシージャが正常に完了しました。
　
SQL> SELECT segment_name, segment_type, tablespace_name FROM USER_SEGMENTS;
　
SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ -------------------------
T                              TABLE              USERS
I_T                            INDEX              USERS

これで再現ケースのIMPORTが完了です。本番環境で問題が発生したSQLを実行して事象が再現することを確認したら、統計情報の再取得やヒント句の追加など期待した結果となるようにテストを行うことができます。

 ##事象再現
 SQL>  sho user
 ユーザーは"TC"です。
 SQL> set autot on
 SQL> select count(*) from t where c1 = 3;
　
   COUNT(*)
 ----------
          1
　
　
 実行計画
 ----------------------------------------------------------
 Plan hash value: 2966233522
　
 ---------------------------------------------------------------------------
 | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------
 |   0 | SELECT STATEMENT   |      |     1 |     3 |   483   (2)| 00:00:01 |
 |   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
 |*  2 |   TABLE ACCESS FULL| T    |   333K|   976K|   483   (2)| 00:00:01 |
 ---------------------------------------------------------------------------
　
　
 ##ヒストグラム統計を取得したことで見積もりが改善し、索引が使用されることを確認
 SQL>  BEGIN
   2      DBMS_STATS.GATHER_TABLE_STATS(
   3      ownname=> 'TC'
   4      ,tabname=> 'T'
   5      ,method_opt=> 'FOR ALL COLUMNS SIZE 2048');
   6   END;
   7   /
　
 PL/SQLプロシージャが正常に完了しました。
　
 SQL> select count(*) from t where c1 = 3;
　
   COUNT(*)
 ----------
          1
　
　
 実行計画
 ----------------------------------------------------------
 Plan hash value: 3571442535
　
 --------------------------------------------------------------------------
 | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 --------------------------------------------------------------------------
 |   0 | SELECT STATEMENT  |      |     1 |     3 |     3   (0)| 00:00:01 |
 |   1 |  SORT AGGREGATE   |      |     1 |     3 |            |          |
 |*  2 |   INDEX RANGE SCAN| I_T  |     1 |     3 |     3   (0)| 00:00:01 |
 --------------------------------------------------------------------------

まとめ

SQLテスト・ケース・ビルダーを利用すると、SQLの実行で発生した問題の再現ケースを少ないステップで容易にEXPORTできます。利用方法を知っておくことで検証環境でのテストを素早く行うことができ、問題を解決するまでの時間を大幅に削減できるかもしれません。

なお、Oracle Enterprise Manager Cloud Controlを利用可能な環境では、GUIでSQLテスト・ケース・ビルダーを利用することが可能ですので、より簡単に再現ケースを作成することができます。詳しくは SQLチューニング・ガイドのマニュアルをご参照ください。

筆者情報

大野高志

サービス事業部サポートセンター

2007年にアシスト入社後、Oracle Databaseのサポート業務に従事。現在はサポート業務の傍ら、未解決のトラブルを一つでも多く減らせるよう、サポートセンターに蓄積されているノウハウを社内外に伝える活動を行っている。

■本記事の内容について
　本記事に記載されている製品およびサービス、定義及び条件は、特段の記載のない限り本記事執筆時点のものであり、予告なく変更になる可能性があります。あらかじめご了承ください。

■商標に関して
　・Oracle®、Java及びMySQLは、Oracle、その子会社及び関連会社の米国及びその他の国における登録商標です。
　・Amazon Web Services、AWS、Powered by AWS ロゴ、[およびかかる資料で使用されるその他の AWS 商標] は、Amazon.com, Inc. またはその関連会社の商標です。
　　文中の社名、商品名等は各社の商標または登録商標である場合があります。

【Oracle AI Database 26ai】同時リフレッシュでON COMMITマテリアライズド・ビューのenqueue待ちにサヨナラ！OLTP／DWHスループットを実測検証

本記事では、従来のON COMMIT MViewが抱えてきたこのようなスループット低下や待機イベントのボトルネックを振り返りつつ、Oracle AI Database 26aiが提供する「同時リフレッシュ（Concurrent Refresh）」によって、OLTP／DWHそれぞれのユースケースでどのような改善が見込めるのかを検証していきます。