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 テスト・ケースは他の環境にインポートできます。
インポートが終了すると、SQL の実行に必要なオブジェクト、データ、統計情報がインポートされたことが確認できます。ここでは簡易的に USER_TABLES を確認し統計情報がインポートされていることを確認しました。 SQL テスト・ケース・ビルダーは SQL に関する問題に対して、再現ケースを簡単にパッケージ化することができます。結果不正などの問題に遭遇した場合には使ってみることを検討ください。 (オラクル事業部 グローバル・アライアンス&サポート・サービス担当 河﨑)
再現を確認するために必要な情報はすべてエクスポートされたファイルにパッケージされていますので、開発環境等で再現性を確認する際にも、さらにテスト・ケースを最小化する際にも活用することができます。
準備としてはインポート先の任意のディレクトリにエクスポートしたファイルを展開し、ディレクトリ・オブジェクトを作成してください。DBMS_SQLDIAG.IMPORT_SQL_TESTCASEプロシージャで
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> 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行が選択されました。
まとめ