概要
Oracle DatabaseのReal Application Testing(RAT)は、Oracle Databaseに対するテストを効率よく実施するためのOracle Databaseの機能です。本記事では、RATの主要機能であるSQL Performance Analyzer(SPA)のもっとも簡単な動作確認の手順を説明します。また、Oracle Database 18cからのSPAの機能である、検索結果の比較の機能(COMPARE_RESULTSET)の動作を確認してみます。本記事の手順を元に、いろいろ条件を変えてSPAの動作を確認することが出来ると思います。
RATのSPAに関しては下記の記事が参考になります。機能の概要ふくめ、ザっとでもあらかじめ読んでいただくと本記事の内容が理解しやすくなります。
Oracle Real Application TestingのSQL Performance Analyzerの活用：データベースのアップグレード（バージョンアップ）でのSQLテスト
1. 本記事の作業の前提
本記事で紹介しているシナリオでは、Oracle Databaseの1つのインスタンスで、SQL Tuning Set(STS)の取得とSPAの実施をします。記事中は19cの環境で実行していますが、異なるリリースでも同様に動作します。ただし、検索結果の比較の機能(COMPARE_RESULTSET)は18c以降のリリースでないと動作しません。
実行するには下記が必要です。
- RATがインストールされているOracle Databaseインスタンス
- SQLの実行環境(SQL*PlusやSQL Developer)
2. 本記事の作業の流れ
まずテスト用のスキーマ、データを作成し、簡単なSELECT文を1つだけ含むSTSを作成します。そのSTSを使ってSPAタスクの1回目の試行を実行します。それからテスト用の表のデータに変更を加えます。続けてSPAタスクの2回目の試行を実行し、2回の試行の比較レポートを作成します。
同じデータベースで1つのSELECT文を実行させているだけなので、2回の試行の間で実行計画の違いや実行時の統計情報に大きな差はでません。2回の試行の間でデータに変更を加えることで、検索件数に違いがでたり、検索結果に違いがでるので、それが比較レポートでどのように表示されるのか確認します。
SPAタスクは2回実施しています。1回目のタスクは、テスト対象のSELECT文が2回の試行で検索件数が異なるようにします。2回目のタスクでは、件数が同じでも検索結果が異なるようにします。
3. SQL Performance Analyzerの実行前の準備
SPAを実行する前に、テスト用のスキーマを作成し、1つの簡単なSELECT文を含むSTSを作成します。
3.1. テスト用のスキーマの作成
SPAを実行するテスト用にappsというユーザーを作成します。
CREATE USER apps IDENTIFIED BY apps;
(実行結果)
User APPSは作成されました。
GRANT DBA TO apps;
(実行結果)
Grantが正常に実行されました。
作成したappsユーザーでテスト用の表test_selectを作成し、データを3件入力します。
CREATE TABLE test_select ( id NUMBER(2), val VARCHAR2(5));
(実行結果)
Table TEST_SELECTは作成されました。
INSERT INTO test_select VALUES( 1, 'TEST1');
(実行結果)
1行挿入しました。
INSERT INTO test_select VALUES( 2, 'TEST2');
(実行結果)
1行挿入しました。
INSERT INTO test_select VALUES( 3, 'TEST3');
(実行結果)
1行挿入しました。
COMMIT;
(実行結果)
コミットが完了しました。
3.2. テスト用のSQL Tuning Set(STS)の作成
SPAのテストで利用するSQLを実行します。これをメモリからSTSへロードさせます。
SELECT /* simple_test */ * FROM apps.test_select;
(実行結果)
ID VAL
---------- -----
1 TEST1
2 TEST2
3 TEST3
実行したSELECT文だけをSTSにロードします。まずSTSを作成します。名前はSPA_TESTとしています。
variable sts_name varchar2(20)
EXEC :sts_name := 'SPA_TEST';
BEGIN
dbms_sqltune.create_sqlset(
sqlset_name => :sts_name,
sqlset_owner =>'APPS'
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
PL/SQLプロシージャが正常に完了しました。
実際に作成されたか確認します。
col owner FORMAT a10
col name FORMAT a30
SELECT
owner,
name,
statement_count
FROM
dba_sqlset;
(実行結果)
OWNER NAME STATEMENT_COUNT
---------- ------------------------------ ---------------
APPS SPA_TEST 0
SYS SYS_AUTO_STS 0
SPA_TESTというSTSがAPPSをオーナーとして作成されています。中身は0件です。もう一つのSTS(SYS_AUTO_STS)は19c以降のリリースで内部的に作成されるもので、ここでは関係ありません。
SPAで利用するため、先に実行したSELECT文1つだけをSTSにロードします。先ほどのSELECT文にはSELECT /* simple_test */で始まるテキストでしたから、それを条件に指定してロードします。
variable sts_name varchar2(20)
EXEC :sts_name := 'SPA_TEST';
DECLARE
sqlset_cur dbms_sqltune.sqlset_cursor;
BEGIN
OPEN sqlset_cur FOR
SELECT VALUE(P)
FROM TABLE(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
basic_filter => 'UPPER(PARSING_SCHEMA_NAME) = ''APPS'' AND UPPER(SQL_TEXT) LIKE ''SELECT /% SIMPLE_TEST%'''
)
) P;
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name=> :sts_name,
populate_cursor=> sqlset_cur,
load_option => 'INSERT',
sqlset_owner=>'APPS');
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
PL/SQLプロシージャが正常に完了しました。
実際にロードされたことを確認してみます。
col owner FORMAT a10
col name FORMAT a30
SELECT
owner,
name,
statement_count
FROM
dba_sqlset;
(実行結果)
OWNER NAME STATEMENT_COUNT
---------- ------------------------------ ---------------
APPS SPA_TEST 1
SYS SYS_AUTO_STS 0
SPA_TESTというSTSに1件ロードされています。1件の中身も確認してみます。
SET LINESIZE 170;
COL SQL_ID FOR A15
COL SQL_TEXT FOR A30 TRUNC
COL PARSING_SCHEMA_NAME FOR A15
COL MODULE FOR A10 TRUNC
SELECT
sql_id,
module,
sql_text,
executions,
elapsed_time,
cpu_time,
buffer_gets,
disk_reads
FROM
TABLE ( dbms_sqltune.select_sqlset(sqlset_owner => 'APPS', sqlset_name => 'SPA_TEST') )
ORDER BY
1;
(実行結果)
SQL_ID MODULE SQL_TEXT EXECUTIONS ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS
--------------- ---------- ------------------------------ ---------- ------------ ---------- ----------- ----------
g3dw118j6305c SQL Develo SELECT /* simple_test */ * FRO 1 8686 4954 74 4
どうやらうまく取得できたようです。SELECT /* simple_test */で始まるSQL分が1つ取得できています。この記事作成においては、該当のSQLをSQL Developerで実行したので、そのことも記録されています。
4. SQL Performance Analyzerのタスクの実行（検索件数の違いの検出）
準備したテスト用のスキーマ、STSを使ってSPAを実行していきます。
4.1. SPAタスクの作成
SPAの実行にあたりSPAタスクを作成します。SQLPA_TEST_APIという名前でSPAタスクを作成しています。STSはあらかじめ準備したSPA_TESTを利用します。
variable sts_name varchar2(20)
EXEC :sts_name := 'SPA_TEST';
variable spa_task VARCHAR2(64)
EXEC :spa_task := 'SQLPA_TEST_API';
variable stmt_task VARCHAR2(64)
BEGIN
:stmt_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
task_name => :spa_task,
sqlset_name => :sts_name,
sqlset_owner => 'APPS'
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
PL/SQLプロシージャが正常に完了しました。
PL/SQLプロシージャが正常に完了しました。
SPAタスクが作成されたことを確認します。
col owner FORMAT a10
col task_name FORMAT a20
SELECT
owner,
task_name,
status
FROM
dba_advisor_tasks
WHERE
advisor_id = 8;
(実行結果)
OWNER TASK_NAME STATUS
---------- -------------------- -----------
APPS SQLPA_TEST_API INITIAL
SYS SYS_AI_VERIFY_TASK INITIAL
SQLPA_TEST_APIというタスクが作成されていることが確認できます。もう一つのSYS_AI_VERIFY_TASKというタスクは19c以降で内部的に作成されるもので、ここでは関係ありません。
4.2. 1回目の試行
SPAタスクの1回目の試行を実行します。STSに記録されたSQL(今回は1つしかありません)を実行します。パラメータでtest executeを指定して、実際に実行させます。
variable spa_task VARCHAR2(64)
EXEC :spa_task := 'SQLPA_TEST_API';
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => :spa_task,
execution_type => 'test execute',
execution_name => 'first trial'
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
PL/SQLプロシージャが正常に完了しました。
4.3. 検索対象のデータ変更
2回目の試行の前に、テストのSELECT文が対象としている表に行を追加します。これで2回目の試行では検索件数が1回目とは変わります。
INSERT INTO test_select VALUES( 11, 'TEST1');
(実行結果)
1行挿入しました。
INSERT INTO test_select VALUES( 12, 'TEST2');
(実行結果)
1行挿入しました。
INSERT INTO test_select VALUES( 13, 'TEST3');
(実行結果)
1行挿入しました。
COMMIT;
(実行結果)
コミットが完了しました。
4.4. 2回目の試行
SPAタスクの2回目の試行を実行します。1回目と2回目で実施することも対象のデータベースも同じですが、検索件数が変わることになります。
variable spa_task VARCHAR2(64)
EXEC :spa_task := 'SQLPA_TEST_API';
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => :spa_task,
execution_type => 'test execute',
execution_name => 'second trial'
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
PL/SQLプロシージャが正常に完了しました。
4.5. 2回の試行の比較とレポート生成
2回の試行の結果を比較します。
variable spa_task VARCHAR2(64)
EXEC :spa_task := 'SQLPA_TEST_API';
BEGIN
DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(
task_name => :spa_task,
parameter => 'comparison_metric',
value => 'buffer_gets'
);
END;
/
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => :spa_task,
execution_type => 'compare performance',
execution_name => 'compared result1',
execution_params => dbms_advisor.arglist(
'execution_name1',
'first trial',
'execution_name2',
'second trial',
'workload_impact_threshold',
0,
'sql_impact_threshold',
0)
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
PL/SQLプロシージャが正常に完了しました。
PL/SQLプロシージャが正常に完了しました。
比較した結果をレポートに出力させます。データベースサーバー上のSQLPLUSで実行して、サーバーの/tmpにspa_basic.htmlというファイル名で出力させています。ここまでの実行結果と異なり、サーバー上のSQLPLUSを利用しているので、下記の出力形式が異なる点はご了承ください。
SQL> variable spa_task VARCHAR2(64)
SQL> EXEC :spa_task := 'SQLPA_TEST_API';
PL/SQL procedure successfully completed.
SQL> set pagesize 0
SQL> set linesize 150
SQL> set long 1000000
SQL> set trimspool on
SQL> var rep clob
SQL> col rep for a150
SQL>
SQL> BEGIN
2 :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK(
3 task_name => :spa_task,
4 type => 'HTML',
5 level => 'ALL',
6 section => 'ALL',
7 execution_name => 'compared result1'
8 );
9 END;
10 /
PL/SQL procedure successfully completed.
SQL> spool /tmp/spa_basic.html
SQL> print :rep
(略)
SQL> spool off
4.6. 比較レポートの確認
このSPAタスクの例で出力されるレポートの抜粋は下記のようになります。抜粋したのは、SQL個別のレポートの箇所です。
1番上の赤枠で囲ったところを確認すると、テスト対象としたSELECT文であることが確認できます。残りの2つの赤枠を確認すると2回の試行で、検索件数が異なったことがわかります。特に下記の記述で該当のSQLに関して検索件数が異なることをレポートしています。
Findings:
The number of returned rows in execution 'first trial' is different than in execution 'second trial'.
5. SQL Performance Analyzerのタスクの実行（検索結果の違いの検出 COMPARE_RESULTSETの動作確認）
1回目のSPAタスクは、テスト対象のSELECT文の2回の試行で検索件数が違うようにしました。次ののタスクは、件数が同じでも検索結果が違うようにします。2つのSPAタスクで実行している内容は同じで、違いは2つの試行の間で実施するデータの変更のみです。
5.1. SPAタスクの作成
SPAタスクを作成するところから実行しますが、まずはテスト用のデータを元の状態(3件のみデータ入力されている状態)に戻し、また先ほどのSPAタスクを削除します。
DELETE test_select WHERE id > 11;
(実行結果)
3行削除されました。
SELECT * FROM apps.test_select;
(実行結果)
ID VAL
---------- -----
1 TEST1
2 TEST2
3 TEST3
variable spa_task VARCHAR2(64)
EXEC :spa_task := 'SQLPA_TEST_API';
BEGIN
DBMS_SQLPA.DROP_ANALYSIS_TASK(
task_name => :spa_task
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
PL/SQLプロシージャが正常に完了しました。
これで準備が整いました。SPAタスクを作成するところから実行していきます。
variable sts_name varchar2(20)
EXEC :sts_name := 'SPA_TEST';
variable spa_task VARCHAR2(64)
EXEC :spa_task := 'SQLPA_TEST_API';
variable stmt_task VARCHAR2(64)
BEGIN
:stmt_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
task_name => :spa_task,
sqlset_name => :sts_name,
sqlset_owner => 'APPS'
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
PL/SQLプロシージャが正常に完了しました。
PL/SQLプロシージャが正常に完了しました。
SPAタスクが作成されました。作成されたことを確認します。
col owner FORMAT a10
col task_name FORMAT a20
SELECT
owner,
task_name,
status
FROM
dba_advisor_tasks
WHERE
advisor_id = 8;
(実行結果)
OWNER TASK_NAME STATUS
---------- -------------------- -----------
APPS SQLPA_TEST_API INITIAL
SYS SYS_AI_VERIFY_TASK INITIAL
SPAタスクSQLPA_TEST_APIが作成されています。
5.2. 1回目の試行
1回目の試行を実行します。ここで、**試行の前にパラメータCOMPARE_RESULTSETをTRUEに設定します。**この設定は、このタスクの間は変更しなければそのままの設定になります。
variable spa_task VARCHAR2(64)
EXEC :spa_task := 'SQLPA_TEST_API';
BEGIN
DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(
task_name => :spa_task,
parameter => 'COMPARE_RESULTSET',
value => 'TRUE'
);
END;
/
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => :spa_task,
execution_type => 'test execute',
execution_name => 'first trial'
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
PL/SQLプロシージャが正常に完了しました。
PL/SQLプロシージャが正常に完了しました。
5.3. 検索対象のデータ変更
2回目の試行の前に、検索対象となるデータを1件変更します。これで、検索件数には影響しませんが検索結果が異なることになります。
UPDATE test_select SET id = id+10 WHERE id=1;
(実行結果)
1行更新しました。
COMMIT;
(実行結果)
コミットが完了しました。
5.4. 2回目の試行
2回目の試行を実行します。パラメータCOMPARE_RESULTSETは1回目の試行でTRUEに設定されているので、改めて設定しなくてもいいです。
variable spa_task VARCHAR2(64)
EXEC :spa_task := 'SQLPA_TEST_API';
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => :spa_task,
execution_type => 'test execute',
execution_name => 'second trial'
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
PL/SQLプロシージャが正常に完了しました。
5.5. 2回の試行の比較とレポート生成
2回の試行の比較を実施し、レポートを作成します。
variable spa_task VARCHAR2(64)
EXEC :spa_task := 'SQLPA_TEST_API';
BEGIN
DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(
task_name => :spa_task,
parameter => 'comparison_metric',
value => 'buffer_gets'
);
END;
/
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => :spa_task,
execution_type => 'compare performance',
execution_name => 'compared result1',
execution_params => dbms_advisor.arglist(
'execution_name1',
'first trial',
'execution_name2',
'second trial',
'workload_impact_threshold',
0,
'sql_impact_threshold',
0)
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
PL/SQLプロシージャが正常に完了しました。
PL/SQLプロシージャが正常に完了しました。
1回目のタスクと同じように、サーバー上のSQL*Plusからレポート作成のPL/SQLを実行して、/tmp/spa_basic.htmlにレポートを出力させます。
SQL> variable spa_task VARCHAR2(64)
SQL> EXEC :spa_task := 'SQLPA_TEST_API';
PL/SQL procedure successfully completed.
SQL> set pagesize 0
SQL> set linesize 150
SQL> set long 1000000
SQL> set trimspool on
SQL> var rep clob
SQL> col rep for a150
SQL>
SQL> BEGIN
2 :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK(
3 task_name => :spa_task,
4 type => 'HTML',
5 level => 'ALL',
6 section => 'ALL',
7 execution_name => 'compared result1'
8 );
9 END;
10 /
PL/SQL procedure successfully completed.
SQL> spool /tmp/spa_basic.html
SQL> print :rep
(略)
SQL> spool off
5.6. 比較レポートの確認
2つ目のSPAタスクの例で出力されるレポートの抜粋は下記のようになります。抜粋したのは、SQL個別のレポートの箇所です。
1番上の赤枠でSQL文を確認できます。真ん中の赤枠から、今回のタスクでは2回の試行で検索件数は同じ3件であったことが確認できます。COMPARE_RESULTSETをTRUEに設定したポイントは3つ目の赤枠で、2回の試行の検索結果は異なるものであったことが、下記の文章で報告されています。
Findings:
The result set in execution 'first trial' is different than in execution'second trial'.
6. SPAのCOMPARE_RESULTSETに関して
SPAのCOMPARE_RESULTSETはOracle Database 18cからの機能です。マニュアルの下記の記載の、「SQLパフォーマンス・アナライザの使用によるSQL結果セットの検証」が該当します。
Oracle Database Testingガイド 19c - Oracle Database Release 18c、Version 18.1での変更点
この機能は、2回の試行でそれぞれの検索結果に対してハッシュ値をつけておいて、それを比較することで、検索結果の違いを検出しています。
上記のような動作であるため、この機能COMPARE_RESULTSETが有効に動作するための条件は、下記のようになります。
- 2回の試行がTEST EXECUTEであること。ここでデータベースリンク先のTEST EXECUTEでも構いません。CONVERT SQLSETやEXPLAIN PLANでは動作しません。
- 2回の試行を実行するデータベースが18c以上であること。データベースリンク先でのTEST EXECUTEでも動作しますが18c以上でなければなりません。