[Oracle] SQLテスト・ケース・ビルダーの使い方

Oracle

Oracleで処理が遅いSQLを調査する必要がある場合、AWRレポートなどの取得した資料からだけでは分析が十分にできないことがあります。

そういった場合に、別環境に対象のSQLを実行するための環境を作って、再現テストを行う方針になりますが、対象のSQLを実行するためには、

  • 対象のSQL文
  • 対象のSQL文を実行するための表や索引などの定義文（DDL文）
  • 格納されているデータ
  • 統計情報

などを取得して、別環境で実行環境を整えてあげる必要があります。

Oracleでは、SQLテスト・ケース・ビルダー（DBMS_SQLDIAG.EXPORT_SQL_TESTCASE、DBMS_SQLDIAG.IMPORT_SQL_TESTCASE）というパッケージツールが用意されており、対象のSQL文を実行するための、他の情報を自動的に取得して、テストケースを作成してくれます。

テストケースを作るための準備

SQLテスト・ケース・ビルダーでテストケースを作るために、事前に準備しておくことがあります。

対象SQLのSQL_IDとPLAN_HASH_VALUEの確認

テストケースを作成する対象SQLのSQL_IDとPLAN_HASH_VALUEが必要となるため、事前にこれらの情報を取得しておきます。

本記事ではこちらのSQL文を使って説明します。

SQL> select * from emp where sal > 2000 order by sal desc;

     EMPNO ENAME      JOB	       MGR HIREDATE		     SAL       COMM	DEPTNO
---------- ---------- --------- ---------- ------------------ ---------- ---------- ----------
      7839 KING       PRESIDENT 	   17-NOV-81		    5000	    10
      7902 FORD       ANALYST	      7566 03-DEC-81		    3000	    20
      7788 SCOTT      ANALYST	      7566 19-APR-87		    3000	    20
      7566 JONES      MANAGER	      7839 02-APR-81		    2975	    20
      7698 BLAKE      MANAGER	      7839 01-MAY-81		    2850	    30
      7782 CLARK      MANAGER	      7839 09-JUN-81		    2450	    10

v$sqlからSQL_IDとPLAN_HASH_VALUEを特定しておきます。

SQL> select sql_text, sql_id, plan_hash_value from v$sql where sql_text like '%sal > 2000%';

SQL_TEXT
--------------------------------------------------------------------------------------------
SQL_ID	      PLAN_HASH_VALUE
------------- ---------------
select sql_text, sql_id, plan_hash_value from v$sql where sql_text like '%sal > 2000%'
d6gyn23u68chm	    903671040

select * from emp where sal > 2000 order by sal desc
3csxf94turf96	    150391907

今回の場合は、SQL_ID=3csxf94turf96、PLAN_HASH_VALUE=150391907となります。

テストケースを出力するディレクトリオブジェクトの作成

テストケースを出力するディレクトリを確認しておきます。
デフォルトで用意されているディレクトリでも大丈夫ですし、新しく作成しても問題ありません。

SQL> select directory_name, directory_path from dba_directories;

DIRECTORY_NAME
--------------------------------------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------------------
DATA_PUMP_DIR
/u01/app/oracle/admin/ru22db/dpdump/1688F10CF304276DE0650A0027BC8706
  :

本記事では「DATA_PUMP_DIR」を使用します。

テストケースの取得方法

では実際にSQLテスト・ケース・ビルダーを使って、テストケースを取得してみます。
dbms_sqldiag.export_sql_testcaseパッケージに、確認したSQL_IDなどを指定して実行します。

declare
  v_testcase clob;
begin
  dbms_sqldiag.export_sql_testcase(
    directory       => 'DATA_PUMP_DIR', -- 出力先
    sql_id          => '3csxf94turf96', -- v$sqlで確認したsql_id
    plan_hash_value => 150391907,       -- v$sqlで確認したplan_hash_value
    exportdata      => true,            -- データを含めるか
    exportpkgbody   => true,            -- 依存パッケージを含めるか
    testcase_name   => 'sqltc',         -- 再現ケースの名前
    testcase        => v_testcase);
end;
/

エラーなく処理が完了すると、出力先ディレクトリに以下ようなファイルが生成されます。

$ pwd
/u01/app/oracle/admin/ru22db/dpdump/1688F10CF304276DE0650A0027BC8706
$ ls -l
合計 584
-rw-r--r--. 1 oracle oinstall   2783  7月  4 22:13 sqltcREADME.txt
-rw-r--r--. 1 oracle oinstall 148177  7月  4 22:14 sqltcdiag.trc
-rw-r-----. 1 oracle oinstall 385024  7月  4 22:14 sqltcdpexp.dmp
-rw-r--r--. 1 oracle oinstall   1198  7月  4 22:14 sqltcdpexp.log
-rw-r--r--. 1 oracle oinstall   4294  7月  4 22:13 sqltcdpexp.sql
-rw-r--r--. 1 oracle oinstall   3850  7月  4 22:14 sqltcdpimp.sql
-rw-r--r--. 1 oracle oinstall   2151  7月  4 22:14 sqltcmain.xml
-rw-r--r--. 1 oracle oinstall    438  7月  4 22:13 sqltcol.xml
-rw-r--r--. 1 oracle oinstall    402  7月  4 22:13 sqltcprmimp.sql
-rw-r--r--. 1 oracle oinstall   1120  7月  4 22:14 sqltcsmrpt.html
-rw-r--r--. 1 oracle oinstall   1009  7月  4 22:13 sqltcsql.xml
-rw-r--r--. 1 oracle oinstall    847  7月  4 22:14 sqltcssimp.sql
-rw-r--r--. 1 oracle oinstall     46  7月  4 22:13 sqltcts.xml
-rw-r--r--. 1 oracle oinstall    459  7月  4 22:14 sqltcxplf.sql
-rw-r--r--. 1 oracle oinstall    710  7月  4 22:14 sqltcxplo.sql
-rw-r--r--. 1 oracle oinstall    433  7月  4 22:14 sqltcxpls.sql

テストケースの実行方法

作成されたテストケースを実行するために、再現環境で以下を実行していきます。
こちらは「sqltcREADME.txt」に出力されている内容です。

grant connect, dba, resource, UNLIMITED TABLESPACE, query rewrite to 
tcb identified by tcb;

create directory TCB_IMP_DIR as '<DIRECTORY_PATH_4_TCB_IMPORT>';

conn tcb/tcb;

exec dbms_sqldiag.import_sql_testcase(directory => 'TCB_IMP_DIR' ,         -
                                      filename  => '<TCB_METADATA>main.xml');

こちらの手順でテストケースをインポートしていきます。
残念ながらマルチテナントを考慮した手順にはなっていませんので考慮が必要です。
本記事では「TCPDB」にインポートする想定で記載しています。

$ sqlplus sys/welcome1@tc.local:1521/tcpdb as sysdba

SQL> grant connect, dba, resource, UNLIMITED TABLESPACE, query rewrite to tcb identified by tcb;
Grant succeeded.

SQL> create directory TCB_IMP_DIR as '/u01/app/oracle/admin/ru22db/dpdump/1688F10CF304276DE0650A0027BC8706';
Directory created.

SQL> conn tcb/tcb@tc.local:1521/tcpdb;
Connected.

SQL> exec dbms_sqldiag.import_sql_testcase(directory => 'TCB_IMP_DIR' ,         -
                                           filename  => 'sqltcmain.xml');
PL/SQL procedure successfully completed.

ここまでテストケースのインポートが完了しましたので、対象のSQLを実行すると同じ結果が返ってきます。

SQL> select * from emp where sal > 2000 order by sal desc;

     EMPNO ENAME      JOB	       MGR HIREDATE		     SAL       COMM	DEPTNO
---------- ---------- --------- ---------- ------------------ ---------- ---------- ----------
      7839 KING       PRESIDENT 	   17-NOV-81		    5000		    10
      7902 FORD       ANALYST	      7566 03-DEC-81		    3000		    20
      7788 SCOTT      ANALYST	      7566 19-APR-87		    3000		    20
      7566 JONES      MANAGER	      7839 02-APR-81		    2975		    20
      7698 BLAKE      MANAGER	      7839 01-MAY-81		    2850		    30
      7782 CLARK      MANAGER	      7839 09-JUN-81		    2450		    10

6 rows selected.

このような流れで、テストケースを実行することができます。

まとめ

本記事では、SQLテスト・ケース・ビルダーを使用した、テストケースの作成とインポート、実行までを解説しました。
特定のSQLに対して調査を行いたい場合などに使えるツールかと思います。

コメント

タイトルとURLをコピーしました