Oracle Real Application TestingのSQL Performance Analyzerの簡単な動作確認の手順：COMPARE_RESULTSETの動作確認


#### 概要
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テスト](https://qiita.com/ts_carp/items/a52c7a4063f9e6226dd6)

#### 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というユーザーを作成します。

```sql
CREATE USER apps IDENTIFIED BY apps;

(実行結果)
User APPSは作成されました。

GRANT DBA TO apps;

(実行結果)
Grantが正常に実行されました。
```
作成したappsユーザーでテスト用の表test_selectを作成し、データを3件入力します。

```sql
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へロードさせます。

```sql
SELECT /* simple_test */ * FROM apps.test_select;

(実行結果)
        ID VAL  
---------- -----
         1 TEST1
         2 TEST2
         3 TEST3
```
実行したSELECT文だけをSTSにロードします。まずSTSを作成します。名前はSPA_TESTとしています。

```sql
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プロシージャが正常に完了しました。
```
実際に作成されたか確認します。

```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 */```で始まるテキストでしたから、それを条件に指定してロードします。
 

```sql
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プロシージャが正常に完了しました。
```
実際にロードされたことを確認してみます。

```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件の中身も確認してみます。

```sql
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を利用します。

```sql
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タスクが作成されたことを確認します。

```sql
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を指定して、実際に実行させます。

```sql
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回目とは変わります。

```sql
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回目で実施することも対象のデータベースも同じですが、検索件数が変わることになります。

```sql
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回の試行の結果を比較します。

```sql
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プロシージャが正常に完了しました。
```

比較した結果をレポートに出力させます。データベースサーバー上のSQL*PLUSで実行して、サーバーの/tmpにspa_basic.htmlというファイル名で出力させています。ここまでの実行結果と異なり、サーバー上のSQL*PLUSを利用しているので、下記の出力形式が異なる点はご了承ください。

```sql
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.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/368742/c2a22de6-aa33-1121-46b3-7053905bcf91.png)

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タスクを削除します。

```sql
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タスクを作成するところから実行していきます。

```sql
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タスクが作成されました。作成されたことを確認します。

```sql
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に設定します。**この設定は、このタスクの間は変更しなければそのままの設定になります。

```sql
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件変更します。これで、検索件数には影響しませんが検索結果が異なることになります。

```sql
UPDATE test_select SET id = id+10 WHERE id=1;

(実行結果)
1行更新しました。

COMMIT;

(実行結果)
コミットが完了しました。
```

##### 5.4. 2回目の試行
2回目の試行を実行します。パラメータCOMPARE_RESULTSETは1回目の試行でTRUEに設定されているので、改めて設定しなくてもいいです。

```sql
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回の試行の比較を実施し、レポートを作成します。

```sql
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
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個別のレポートの箇所です。

![2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/368742/c183373f-3882-5074-3947-9d2433a4666f.png)

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での変更点](https://docs.oracle.com/cd/F19136_01/ratug/release-changes.html#GUID-DD057827-7B02-432A-A399-F7CF56823A45)

この機能は、2回の試行でそれぞれの検索結果に対してハッシュ値をつけておいて、それを比較することで、検索結果の違いを検出しています。

上記のような動作であるため、この機能COMPARE_RESULTSETが有効に動作するための条件は、下記のようになります。

1. 2回の試行がTEST EXECUTEであること。ここでデータベースリンク先のTEST EXECUTEでも構いません。CONVERT SQLSETやEXPLAIN PLANでは動作しません。
2. 2回の試行を実行するデータベースが18c以上であること。データベースリンク先でのTEST EXECUTEでも動作しますが18c以上でなければなりません。