Oracle DatabaseのSQL Tuning Set(STS)の活用：STSへSQL実行時の情報を記録する

#### 概要
Oracle DatabaseのSQL Tuning Set(STS)とは、データベースで実行された複数のSQLの下記のような情報を含むデータベースオブジェクトです。
- SQL文
- 実行計画
- 実行コンテキスト（スキーマ、アプリケーション・モジュール名など）
- 実行統計（実行時間、CPU時間、バッファ読み取り量、ディスク読み取り量など）

データベース上で実行されている各SQLのカタログの情報を、データベースの中に記録するようなイメージです。STSだけでも利用できますし、下記のようなOracle Databaseの高度な機能と組み合わせて利用することもあります。また、STSはあるデータベースで記録したものを別のデータベースで移動させることができます。
- データベース上でのテストを実行する機能 ⇒ Real Application Testing(RAT)のSQL Performance Analyzer(SPA)
- チューニングのアドバイスを生成する機能 ⇒ SQL Tuning Advisor
- 実行計画を固定化するための機能 ⇒ SQL Plan Management

本記事では、Oracle Databaseへ実行されインスタンスのメモリ中にあるSQL群をSTSに記録する手順に関して説明します。紹介するのはOracle Enterprise Managerの画面からの実行手順ではなく、SQLの実行環境からコマンド(SQL,PL/SQL)でSTSへSQL群の情報を取得する方法です。
2019年12月ごろの情報・環境を元に本記事は記載しています。

#### 0. STSに必要なOracle Databaseのエディション
STSはOracle Database Enterprise Editionの機能です。オプションのライセンスは不要です。Oracle Database Standard Edition 2では利用できません。

[Oracle Database データベース・ライセンス情報ユーザー・マニュアル 12cリリース2 (12.2)](https://docs.oracle.com/cd/E82638_01/dblic/Licensing-Information.html#GUID-0F9EB85D-4610-4EDF-89C2-4916A0E7AC87)
管理性 - 表1-6 の「SQLチューニング・セット」が参考になります。

以前はSTSの利用にオプションのTuning PackまたはReal Application Testingが必要でした。そのことに伴う注意事項を本記事の「注意事項：Enterprise Editionを利用しているが、STS取得時にORA-13718でエラーとなってしまう場合」に記載しています。

#### 1. 本記事の作業の前提
本記事で紹介している手順は、Oracle Databaseの11g R2(11.2.0.4)で確認しています。実行するには下記が必要です。
- Oracle Databaseインスタンス
- SQLの実行環境(SQL*PlusやSQL Developer)

本記事の中で手順として説明はしていませんが、作業の前あるいは平行して対象のデータベースに対して、特定のユーザーで何かSQLを実行しておきます。簡単なSQLを手動で実行するのでも構いませんし、アプリケーション、ツールを動作させて複数のSQLを実行しておいても構いません。

そのデータベースインスタンスのメモリにある実行したSQLの情報を取得してSTSに格納させるイメージです。今回は「SQLを発行したユーザー」を条件に指定してメモリ中にあるSQLを取得します。取得するSQLの条件は、必要に応じてさまざまな条件が考えられます。

#### 2. SYSAUX表領域の空き領域の確認
STSはデータベースのSYSAUX表領域に格納されます。多数のSQLをSTSに格納する場合、SYSAUX表領域が不足しないか注意する必要があります。必要な領域はSQL文に依存しますが、1つのSQLあたり5～20KB程度が目安、と説明されている資料もあります。あくまで目安です。

要件が厳しい場合は、可能であれば、対象となるデータベース環境で、SQLの数をそこまで大きくならないようにSTSへ取得して必要となる領域がどの程度だったか確認しておきます。下記のSQLはSYSAUXの空き領域を確認しています。

```
col tablespace_name for a20
col max_extendable format 9G999G999D99
col used_mb format 9G999G999D99
col curr_mb format 9G999G999D99
col free_mb format 9G999G999D99

SELECT
    b.tablespace_name,
    max_extendable_mb max_extendable,
    b.curr_mb,
    nvl(used_mb, 0) used_mb,
    b.curr_mb - nvl(used_mb, 0) free_mb
FROM
    (
        SELECT
            tablespace_name,
            SUM(bytes) / 1024/1024 used_mb
        FROM
            dba_segments
        GROUP BY
            tablespace_name
    ) a,
    (
        SELECT
            tablespace_name,
            SUM(greatest(bytes, maxbytes)) / 1024 / 1024 max_extendable_mb,
            SUM(bytes) / 1024 / 1024 curr_mb
        FROM
            dba_data_files
      group BY
            tablespace_name
    ) b
WHERE
    a.tablespace_name (+) = b.tablespace_name
    AND a.tablespace_name = 'SYSAUX'
order BY
    tablespace_name;

(実行結果)
TABLESPACE_NAME      MAX_EXTENDABLE       CURR_MB       USED_MB       FREE_MB
-------------------- -------------- ------------- ------------- -------------
SYSAUX                    32,767.98        600.00        346.19        253.81
```

#### 3. STSの作成
まず空のSTSを作成します。ここではデータベースオブジェクトであるSTSのオーナーとSQLを発行したユーザーを同じユーザーにしてます（下記の例ではユーザー名SOEです）。そのユーザーにはアプリケーションを実行するための権限とロール以外に、ADVISOR権限、ADMINISTER ANY SQL TUNING SET権限、SELECT_CATALOG_ROLE権限を付与しています。下記が権限付与の実行例です。


```
SQL> GRANT ADVISOR TO SOE;

権限付与が成功しました。

SQL> GRANT ADMINISTER ANY SQL TUNING SET TO SOE;

権限付与が成功しました。

SQL> GRANT SELECT_CATALOG_ROLE TO SOE;

権限付与が成功しました。
```

まず対象のデータベースで作成済みのSTSを確認します。STSのオーナーとなるユーザー(ここの例ではSOE)で接続して、下記のSQLを実行します。作成済みのSTSと名前が重ならないようにするためです。

```
col owner FORMAT a10
col name FORMAT a30

SELECT
    owner,
    name,
    statement_count
FROM
    dba_sqlset;

(実行結果)
レコードが選択されませんでした。
```
ここの例での結果では、作成済みのSTSはありません。次に空のSTSを作成します。今回の例では、STSのオーナーをSOEというユーザーにし、STS名をSTS01としています。

```
BEGIN
 DBMS_SQLTUNE.CREATE_SQLSET (
   sqlset_owner =>'SOE',        -- STSのオーナーとなるユーザー名
   sqlset_name => 'STS01'       -- 作成するSTS名を指定
 );
END;
/

(実行結果)
PL/SQLプロシージャが正常に完了しました。
```
空のSTSが作成されたことを確認します。下記SQLの出力結果のSTATEMENT_COUNTで、そのSTSに格納されているSQLの数を確認できます。下記の例では、作成直後のSTSですから0になっています。

```
SELECT
    owner,
    name,
    statement_count
FROM
    dba_sqlset;

(実行結果)
OWNER      NAME                           STATEMENT_COUNT
---------- ------------------------------ ---------------
SOE        STS01                                        0
```

#### 4. STSへのSQLのロード
作成したSTSにSQLをロードします。ここでは、対象データベースで実行されメモリ中にあるSQLの中から、条件を指定してロードします。

```
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) = ''SOE'' AND plan_hash_value != 0',
     object_filter => NULL,
     ranking_measure1 => 'ELAPSED_TIME',
     result_limit => 1000,
     recursive_sql => DBMS_SQLTUNE.NO_RECURSIVE_SQL
    )
   ) P;
 DBMS_SQLTUNE.LOAD_SQLSET(
  sqlset_owner => 'SOE',  -- 対象のSTSのオーナーを指定
  sqlset_name => 'STS01', -- 対象のSTSの名前を指定
  populate_cursor=> sqlset_cur,
  load_option => 'INSERT');
END;
/

(実行結果)
PL/SQLプロシージャが正常に完了しました。
```
このPL/SQLプログラムは何度も実行して構いません。実行の際にメモリ中にあるSQLから条件にあったものをSTSにロードします。呼び出しているパッケージに関しては、下記のマニュアルが参考になります。さまざまな指定ができます。
[マニュアルOracle Database SQLチューニング・ガイド:SQLチューニング・セットのロード](https://docs.oracle.com/cd/E82638_01/tgsql/managing-sql-tuning-sets.html#GUID-4E5240CD-B021-4728-89A9-95F5B81CBC59)
[マニュアルDatabase PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス:DBMS_SQLTUNEのサブプログラム・グループ](https://docs.oracle.com/cd/E82638_01/arpls/DBMS_SQLTUNE.html#GUID-8DF08AC5-A337-46B3-A30F-6B2511C4153C)

上記の例では、DBMS_SQLTUNE.SELECT_CURSOR_CACHEパッケージで下記のように指定することで、SOEユーザーが発行しているSQLをSTSへロードする対象としています。また、plan_hash_value != 0と指定することで、「実行計画のあるSQL文だけを取得する」という指定をしています。（たとえばPL/SQLパッケージを呼び出すPL/SQLなどは、実行計画がありません。）

```
basic_filter => 'UPPER(PARSING_SCHEMA_NAME) = ''SOE'' AND plan_hash_value != 0',
```

ここではbasic_filterに2つの条件(PARSING_SCHEMA_NAMEとplan_hash_value)を指定していますが、SQL文のテキストに条件を指定したり、ほかにもさまざまな指定ができます。下記のマニュアルが参考になります。
[マニュアルDatabase PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス:SQLSET_ROWオブジェクト・タイプ ](https://docs.oracle.com/cd/E82638_01/arpls/DBMS_SQLTUNE.html#GUID-1F4AFB03-7B29-46FC-B3F2-CB01EC36326C)

さらに、下記の2つのパラメータにより、対象となるSQL群を各SQL実行の経過時間(ELAPSED_TIME)で並び直して、上から最大1000までをロードするようにしています。

```
ranking_measure1 => 'ELAPSED_TIME',
result_limit => 1000
```
下記の箇所はロードするSQLから再帰的SQLを除外するためのフィルタ(NO_RECURSIVE_SQL)を指定しています。

```
recursive_sql => DBMS_SQLTUNE.NO_RECURSIVE_SQL
```

上記の例では、DBMS_SQLTUNE.LOAD_SQLSETで下記のようにオプションを指定しています。この指定をした場合、メモリ中にあった条件にあうSQL群のうち、ロード先で指定したSTSの中にはないSQLを追加します。すでにSTSに記録されていたSQLに関して更新はしません。なんどかここの手順によるSQLのロードを実行することで、実行されるSQLをSTSに追加していくことになります。詳細は上記のマニュアルをご確認ください。

```
load_option => 'INSERT'
```

ここで紹介したパッケージとは別の方法として、DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSETパッケージを使用して、特定の期間にキャッシュを複数回ポーリングしてカーソルキャッシュからSQL群をSTSにロードする方法もあります。下記の記事が参考になります。

[Oracle Database の STS(SQL Tuning Set) を活用して、SQL の 性能統計 や 実行計画 を キャプチャする。](https://qiita.com/ora_gonsuke777/items/d2965a105feebac9f25a)

上記のようにPL/SQLを実行したのちに、STSにロードされたことを確認します。下記の例では60のSQLが記録されています。

```
SELECT
    owner,
    name,
    statement_count
FROM
    dba_sqlset;

(実行結果)
OWNER      NAME                           STATEMENT_COUNT
---------- ------------------------------ ---------------
SOE        STS01                                       60
```

#### 5. STSの中身確認
SQL群を取得したSTSの中身を確認してみます。

##### 5.1. STS全体のSQL IDとSQL文の一部、実行時の統計情報をリストで参照
まずSTSの中にあるSQL群の情報をリストで確認してみます。次のようなSQLで確認できます。STSのオーナーと名前を指定しています。

```
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 => 'SOE', sqlset_name => 'STS01') )
ORDER BY
    1;

(実行結果)
SQL_ID          MODULE     SQL_TEXT                       EXECUTIONS ELAPSED_TIME   CPU_TIME BUFFER_GETS DISK_READS
--------------- ---------- ------------------------------ ---------- ------------ ---------- ----------- ----------
0j95qvym9s35u   Update Cus SELECT CARD_DETAILS_SEQ.NEXTVA       1457        19172      18106          12          0
0y1prvxqc2ra9   Browse Pro SELECT PRODUCTS.PRODUCT_ID, PR      61244      9782185    6853279      633066          0
0zg5scs7brcfg   SQL Develo select count(1) from all_objec          1       173116      74740          36          0
(中略)
61行が選択されました。 
```

このようにして、SQL文の最初の部分や、SQLを実行したモジュールの情報とともに、SQL実行に関する統計情報（実行回数など）をリストで確認できます。さらに、ここにあるSQL_IDを指定して、SQLの実行計画を表示させることができます。

##### 5.2. SQL IDを指定してSTSに取得されている取得したSQLの実行計画の参照
STSに記録されているSQLの実行計画を表示させます。ここで実行計画を表示させたいSQLのSQL_IDを指定します。SQL_IDは前節のSTSの中にあるSQL群の情報をリストで確認した際に、参照することができます。次のようなSQLで確認できます。

```
SELECT
    *
FROM
    TABLE ( dbms_xplan.display_sqlset(sqlset_name => 'STS01', sql_id => '0y1prvxqc2ra9', sqlset_owner => 'SOE') );

(実行結果)
SQL Tuning Set Name: STS01
SQL Tuning Set Owner: SOE
SQL_ID: 0y1prvxqc2ra9
SQL Text: SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, 
          CATEGORY_ID, WEIGHT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID, 
          PRODUCT_STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL, QUANTITY_ON_HAND 
          FROM PRODUCTS, INVENTORIES WHERE PRODUCTS.PRODUCT_ID = :B2 AND 
          INVENTORIES.PRODUCT_ID = PRODUCTS.PRODUCT_ID AND ROWNUM < :B1 
--------------------------------------------------------------------------------
 
Plan hash value: 1430386540
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                        |       |       |     7 (100)|          |
|   1 |  COUNT STOPKEY                  |                        |       |       |            |          |
|   2 |   NESTED LOOPS                  |                        |    15 |  6045 |     7   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                 |                        |    15 |  6045 |     7   (0)| 00:00:01 |
|   4 |     NESTED LOOPS OUTER          |                        |     1 |   393 |     4   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION    |     1 |   177 |     2   (0)| 00:00:01 |
|   6 |       INDEX UNIQUE SCAN         | PRODUCT_INFORMATION_PK |     1 |       |     1   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID| PRODUCT_DESCRIPTIONS   |     1 |   216 |     2   (0)| 00:00:01 |
|   8 |       INDEX RANGE SCAN          | PRD_DESC_PK            |     1 |       |     1   (0)| 00:00:01 |
|   9 |     INDEX RANGE SCAN            | INVENTORY_PK           |    15 |       |     2   (0)| 00:00:01 |
|  10 |    TABLE ACCESS BY INDEX ROWID  | INVENTORIES            |    15 |   150 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
```

#### 6. STSの削除
STSを削除するには、次のように実行します。別機能でSTSを利用中のときは削除できません。（RAT機能の複数のステップの途中のときなど）

次のようなSQLでSTSを削除できます。対象のSTSのオーナーと名前を指定します。

```
BEGIN
 DBMS_SQLTUNE.DROP_SQLSET (
   sqlset_owner => 'SOE',
   sqlset_name => 'STS01'
 );
END;
/

(実行結果)
PL/SQLプロシージャが正常に完了しました。
```

#### 7. 注意事項：Enterprise Editionを利用しているが、STS取得時にORA-13718でエラーとなってしまう場合について
「0. STSに必要なOracle Databaseのエディション」で説明したように、STSはOracle DatabaseのEnterprise Editionで利用可能で、オプションは不要です。ただし、かつてはオプション（Tuning PackあるいはReal Application Testingオプション）が必要でした。その影響でEnteprise Editionのデータベースなのだけれど、STSを取得しようしてPL/SQLを実行すると、下記のようなエラーになるケースがあります。

```
ORA-13718: この機能を使用するには、Tuning PackのライセンスまたはReal Application Testingオプションが必要です。
```

このような場合、パラメータを一時的に変更することで対応できます（STSの取得ができます）。パラメータの変更はOracle Databaseを起動したままで実行できます。その方法に関して説明します。

```
SQL> show parameters CONTROL_MANAGEMENT_PACK_ACCESS

NAME                           TYPE   VALUE 
------------------------------ ------ ----- 
control_management_pack_access string NONE  
```

このようにcontrol_management_pack_accessパラメータがNONEで、RATオプションもインストールされていない場合、STS取得の際に上記のようなエラーとなります。次のようにパラメータを変更（オンラインで変更できます）することでSTS取得が可能です。

```
SQL> ALTER SYSTEM SET CONTROL_MANAGEMENT_PACK_ACCESS = 'DIAGNOSTIC+TUNING';

システムが変更されました。

SQL> show parameters CONTROL_MANAGEMENT_PACK_ACCESS

NAME                           TYPE   VALUE             
------------------------------ ------ ----------------- 
control_management_pack_access string DIAGNOSTIC+TUNING 
```

このようなパラメータ変更はSTS取得のための一時的な対応なので、STS取得後は元に戻すようにします。

#### 8. 注意事項：STSの中のSQL文と実行計画は1対1ではありません
STSの中には、実行された1つ1つのSQLのSQL文と実行計画などの情報が格納されています。ここでSQL文と実行計画は1対1とは限りません。**SQL文は異なるが実行計画が同じになるケース**もあれば、**同じSQL文で複数の実行計画が記録されるケース**もあります。
本記事の例にあるSTSとは別のSTSになりますがSTS02に関して確認してみます。ここでは確認方法の紹介の意図で記載します。

```
col owner FORMAT a10
col name FORMAT a30

SELECT
    owner,
    name,
    statement_count
FROM
    dba_sqlset;

(実行結果)
OWNER      NAME                           STATEMENT_COUNT
---------- ------------------------------ ---------------
SOE        STS02                                       19
SOE        STS01                                       60
SYS        SYS_AUTO_STS                                 0
```
DBA_SQLSETを検索して確認するとSTS02には19のSQL文の実行時の情報が格納されています。

```
SELECT
   count( distinct SQL_ID ) SQL,
   count( distinct PLAN_HASH_VALUE ) PLAN,
   count( distinct SQL_ID||PLAN_HASH_VALUE ) 組み合わせ
FROM
   DBA_SQLSET_STATEMENTS
WHERE
   SQLSET_OWNER = 'SOE' and
   SQLSET_NAME = 'STS02';

(実行結果)
       SQL       PLAN      組み合わせ
---------- ---------- ----------
        19         18         20
```
DBA_SQLSET_STATEMENTSを検索して確認するとSTS02には、SQL文の種類は19、実行計画の種類は18、組み合わせで20あることがわかります。下記の結果は、このSTS02に関して具体的にSQL_IDを指定して確認してみたものです。

```
SELECT 
   SQL_ID,
   PLAN_HASH_VALUE
FROM
   DBA_SQLSET_STATEMENTS
WHERE
   SQLSET_OWNER = 'SOE' and
   SQLSET_NAME = 'STS02' and
   SQL_ID IN ( '42gprmpmvh8px', '93ys4sq6c1yd4', '21gkdm463jwy8', '767pug2dbpqpc' );

(実行結果)
SQL_ID        PLAN_HASH_VALUE
------------- ---------------
21gkdm463jwy8      3762034736
42gprmpmvh8px      1589381018
767pug2dbpqpc      3762034736
93ys4sq6c1yd4      1589381018
```

この結果から、ここの例では、SQL_IDが21gkdm463jwy8と767pug2dbpqpcの2つのSQL文は同じ実行計画(PLAN_HASH_VALUEが3762034736)であることがわかります。同様にSQL_IDが42gprmpmvh8pxと93ys4sq6c1yd4     も同じ実行計画です。

```
SELECT 
   SQL_ID,
   PLAN_HASH_VALUE
FROM
   DBA_SQLSET_STATEMENTS
WHERE
   SQLSET_OWNER = 'SOE' and
   SQLSET_NAME = 'STS02' and
   SQL_ID = 'c13sma6rkr27c';

(実行結果)
SQL_ID        PLAN_HASH_VALUE
------------- ---------------
c13sma6rkr27c       670080582
c13sma6rkr27c      3101473678
```
この結果から、ここの例では、SQL_IDがc13sma6rkr27cとであるSQL文に対して、2つの実行計画(PLAN_HASH_VALUEが670080582と3101473678)がSTSに格納されています。

#### 9. (参考)SYSAUX上のSTSのサイズに関して
STSがSYSAUX表領域に格納されます。STSのサイズがどのくらいか、という点に関しては、下記の記事が参考になります。
[ASK TOM:where are sql tuning sets stored and how can you tell how much space a specific one or all of them are using?](https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9535437900346156553)
v$sysaux_occupantsを検索して利用しているサイズを出していますが、1つ1つのSTS毎のサイズでなく、SQL Plan Management(SPM)で利用する計画ベースラインとSQLプロファイルで利用している領域とあわせたサイズが表示されます。また、作成したSTSを削除しても、その領域が解放されたとして検索されるわけではありません。
また別の方法として、1つ1つのSTSをステージング表に出力させて、そのステージング表のサイズを検索させる方法も紹介しています。

#### 10. 最後に
本記事で紹介した、STSへのSTS群の情報のロード方法は1つのサンプルでしかありません。特にSTSへのロードの際の設定は、さまざまなバリエーションが考えられるので、本記事の設定をスタートに、さまざまな設定を試すことで、より使いこなせるようになると思います。

#### 11. 参考情報
[マニュアルOracle Database SQLチューニング・ガイド:SQLチューニング・セットのロード](https://docs.oracle.com/cd/E82638_01/tgsql/managing-sql-tuning-sets.html#GUID-4E5240CD-B021-4728-89A9-95F5B81CBC59)
[マニュアルDatabase PL/SQL Packages and Types Reference:DBMS_SQLTUNEのサブプログラム・グループ](https://docs.oracle.com/cd/E82638_01/arpls/DBMS_SQLTUNE.html#GUID-8DF08AC5-A337-46B3-A30F-6B2511C4153C)
[Oracle Database の STS(SQL Tuning Set) を活用して、SQL の 性能統計 や 実行計画 を キャプチャする。](https://qiita.com/ora_gonsuke777/items/d2965a105feebac9f25a)
[SQL Performance AnalyzerによるSQLパフォーマンス比較実行例(API)](https://qiita.com/hobata/items/74444d4548ebfbc46d4c)
[Oracle DatabaseのSQL Tuning Set(STS)の活用：STSをデータベース間で移動（コピー）する](https://qiita.com/ts_carp/items/eee8f1fa3d69c3b91f8a)