SQL テスト・ケース・ビルダーについて

*以下は、サポート契約締結中のお客様に毎月配信しているサポートレターより一部抜粋して掲載しています。

結果不正の問題に対する再現ケースの重要性について

結果不正の問題はデータベース環境に発生する問題の中でも原因の特定が難しいものの一つです。原因はオプティマイザの不正な動作であったり、メモリーやブロックの破損に起因するケースなど、様々な要因が可能性として考えられます。
そのため、原因の特定のためには発生条件の切り分けを行っていき、再現ケースを特定していくことが重要になってきます。特にサポートセンターの環境で問題を再現させることができる再現ケースがある場合、比較的早期に調査の進展を得られる可能性が高まります。
今回の記事では再現ケースの作成を支援する機能として SQL テスト・ケース・ビルダーを紹介します。

SQL テスト・ケース・ビルダーについて

SQL テスト・ケース・ビルダーは、SQL に関する問題に対処するために SQL テスト・ケースを作成する機能を提供します。SQL テスト・ケースは、別の環境で SQL に関する問題を再現させるために必要な関連情報の集合を指します。SQL テスト・ケース・ビルダーでは、同じ実行計画で SQL を実行するために必要になる SQL文、オブジェクト定義、データ、バインド変数、システム統計などを一塊のパッケージとして、ファイルに出力することができます。DBMS_SQLDIAG という PL/SQL パッケージが、SQL テスト・ケース・ビルダーのインタフェースとして用意されており、Standard Edition でも使用できます。

SQL テスト・ケース・ビルダーによるエクスポート

SQL テスト・ケース・ビルダーで SQL テスト・ケースを生成する手順を見てみましょう。
SQL テスト・ケースのエクスポートには、DBMS_SQLDIAG.EXPORT_SQL_TESTCASE プロシージャを使用します。ここでは例として以下の SQL に問題が発生していると仮定します。

    SELECT   ch.channel_class, c.cust_city, t.calendar_quarter_desc,
              SUM(s.amount_sold) sales_amount
     FROM     sales s, times t, customers c, channels ch
     WHERE    s.time_id = t.time_id
     AND      s.cust_id = c.cust_id
     AND      s.channel_id = ch.channel_id
     AND      c.cust_state_province = 'CA'
     AND      ch.channel_desc in ('Internet','Catalog')
     AND      t.calendar_quarter_desc IN ('1999-01','1999-02')
     GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc

今回は SQL 実行済みの環境でカーソル・キャッシュに存在するカーソルから SQL テスト・ケースを抽出しエクスポートすることにします。SQL カーソルを特定するために必要な情報として、SQL 識別子(SQL_ID) と SQL ハッシュ(PLAN_HASH_VALUE) を V$SQL から確認します。

    SQL> select SQL_ID, PLAN_HASH_VALUE from V$SQL
       2  where sql_text like 'SELECT   ch.channel_class, c.cust_city%';

     SQL_ID                     PLAN_HASH_VALUE
     -------------------------- ---------------
     7jw8p57fkx8c2                    186116241

SQL テスト・ケースをエクスポートします。

    SQL> DECLARE
      2      testcase CLOB;
      3  BEGIN
      4      DBMS_SQLDIAG.EXPORT_SQL_TESTCASE (
      5      directory         => 'SQL_TESTCASE_DIR', -- ディレクトリ名
      6      sql_id            => '7jw8p57fkx8c2',    -- SQL 識別子
      7      plan_hash_value   => 186116241,          -- SQL ハッシュ
      8      exportMetadata    => true,               -- オブジェクト定義を含む
      9      exportData        => true,               -- データを含む
     10      testcase_name     => 'SQL_TESTCASE1',    -- SQL テスト・ケース名
     11      testcase          => testcase );         -- 結果出力の変数
     12  END;
     13  /

     PL/SQLプロシージャが正常に完了しました。

指定したディレクトリ配下には、引数 testcase_name に指定した値を接頭辞に持つファイル名で多数のファイルが生成されます。主なものを下記に記載しますが、サポートセンターに送付する際はすべてのファイルをまとめて圧縮して提供ください。

<testcase_name>README.txt -- インポート方法についての README ファイル
<testcase_name>main.xml -- 各ファイルのメタデータ・ファイル
<testcase_name>DPEXP.DMP -- Data Pump ダンプファイル(データ用)
<testcase_name>sql.xml -- SQL 文情報
<testcase_name>xpl.txt -- 実行計画情報

SQL テスト・ケース・ビルダーによるインポート

エクスポートされた SQL テスト・ケースは他の環境にインポートできます。
再現を確認するために必要な情報はすべてエクスポートされたファイルにパッケージされていますので、開発環境等で再現性を確認する際にも、さらにテスト・ケースを最小化する際にも活用することができます。
準備としてはインポート先の任意のディレクトリにエクスポートしたファイルを展開し、ディレクトリ・オブジェクトを作成してください。DBMS_SQLDIAG.IMPORT_SQL_TESTCASEプロシージャで main.xml ファイルを引数に指定するとこで、実行スキーマに SQL テスト・ケースに含まれるデータがインポートされます。

    SQL> BEGIN
      2      DBMS_SQLDIAG.IMPORT_SQL_TESTCASE (
      3      directory      => 'SQL_TESTCASE_DIR',      -- ディレクトリ名
      4      filename       => 'SQL_TESTCASE1main.xml', -- *main.xml ファイル名
      5      importMetadata => true,                    -- オブジェクト定義を含む
      6      importData     => true,                    -- データを含む
      7      ignoreStorage  => true );                  -- 記憶域の属性を無視する
      8  END;
      9  /

     PL/SQLプロシージャが正常に完了しました。

インポートが終了すると、SQL の実行に必要なオブジェクト、データ、統計情報がインポートされたことが確認できます。ここでは簡易的に USER_TABLES を確認し統計情報がインポートされていることを確認しました。

    SQL> select TABLE_NAME, BLOCKS, LAST_ANALYZED from USER_TABLES;

     TABLE_NAME                   BLOCKS LAST_ANA
     ------------------------ ---------- --------
     SALES                          1907 16-09-21
     CUSTOMERS                      1486 16-09-21
     CHANNELS                          4 16-09-21
     TIMES                            59 16-09-21

     6行が選択されました。

まとめ

SQL テスト・ケース・ビルダーは SQL に関する問題に対して、再現ケースを簡単にパッケージ化することができます。結果不正などの問題に遭遇した場合には使ってみることを検討ください。

(オラクル事業部 グローバル・アライアンス&サポート・サービス担当 河﨑)

SQL テスト・ケース・ビルダーについて