STS(SQL Tuning Set) をデータベース間で移行してみる(Oracle Database)

STS(SQL Tuning Set) は Oracle Database の SQL に関する情報を保持するオブジェクトです。SQLテキスト/SQL実行計画/SQL実行統計等 を格納できます。本記事ではこの STS をデータベース間で移行してみますやで。
彡(^)(^)

# 1. 検証環境

下記のOCI環境で検証しています。EE以上であれば本記事および関連記事の実行は可能です。

> STS移行元：OCI BaseDB 12.2 EEEP
> STS移行先：OCI BaseDB 19c EEEP

# 2. 前提条件

以下の前提条件を満たす環境で検証します。

> (1). Export/Import に使用するディレクトリ・オブジェクトは作成済みであること
> (2). 各種プロシージャや Export/Import などの権限は付与されたユーザーで作業すること
> (3). STS(SQL Tuning Set)はキャプチャ済みであること

STS のキャプチャは下記記事を参照して下さい。

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

# 3. 参考ドキュメント

以下のドキュメントを参照しています。

> Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 19c
> 169 DBMS_SQLTUNE
> https://docs.oracle.com/cd/F19136_01/arpls/DBMS_SQLTUNE.html#GUID-821462BF-1695-41CF-AFF7-FD23E9999C6A

# 4. STSステージング表の作成(DBMS_SQLTUNE.CREATE_STGTAB_SQLSETプロシージャ)

まず STS を移行するためのステージング表を DBMS_SQLTUNE.CREATE_STGTAB_SQLSETプロシージャ で作成します。

```sql
BEGIN
  DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
      'TBL_STSSTG' -- table_name
    , 'AYSHIBAT'   -- schema_name
  );
END;
/
```

この時点でステージング表は空(0件)となります。

```sql
SELECT COUNT(*) FROM AYSHIBAT.TBL_STSSTG;

  COUNT(*)
----------
         0
```

# 5. STSステージング表にSTSの内容を格納(DBMS_SQLTUNE.PACK_STGTAB_SQLSETプロシージャ)

次に DBMS_SQLTUNE.PACK_STGTAB_SQLSETプロシージャを実行してステージング表に STS を格納します。

```sql
BEGIN
  DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
      'STS_1'      -- sqlset_name
    , 'AYSHIBAT'   -- sqlset_owner
    , 'TBL_STSSTG' -- staging_table_name
    , 'AYSHIBAT'   -- staging_schema_owner
  );
END;
/
```

実行後にステージング表にレコードが格納されていれば成功です。

```sql
SELECT COUNT(*) FROM AYSHIBAT.TBL_STSSTG;

  COUNT(*)
----------
         5
```

# 6. ステージング表を Export/Import(expdp/impdp) で別データベースに移行

ステージング表を Export/Import(expdp/impdp) で別データベースに移行します。下記はexpdp/impdpのコマンドと実行結果のサンプルとなります。

```shell
#移行元のexpdpコマンドサンプル
expdp AYSHIBAT/xxxxxxxxxx@aysdb122.aysoskprvsubnet.aysoskvcn01.oraclevcn.com:1521/ayspdb122.aysoskprvsubnet.aysoskvcn01.oraclevcn.com \
  dumpfile=DIR_WORK_DP:exp_tbl_stsstg.dmp \
  logfile=DIR_WORK_DP:exp_tbl_stsstg.log \
  tables=TBL_STSSTG \
  job_name="EXP_TBL_STSSTG_JOB" \
  logtime=ALL
```

```shell
#移行先のimpdpコマンドサンプル
impdp AYSHIBAT/xxxxxxxxxx@aysdb19cep.aysoskprvsubnet.aysoskvcn01.oraclevcn.com:1521/ays19ceppdb.aysoskprvsubnet.aysoskvcn01.oraclevcn.com \
  dumpfile=DIR_WORK_DP:exp_tbl_stsstg.dmp \
  logfile=DIR_WORK_DP:imp_tbl_stsstg.log \
  tables=TBL_STSSTG \
  job_name="IMP_TBL_STSSTG_JOB" \
  logtime=ALL
```

```
#expdp実行結果サンプル
Export: Release 12.2.0.1.0 - Production on Wed May 31 12:27:13 2023

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production
31-MAY-23 12:27:15.171: Starting "AYSHIBAT"."EXP_TBL_STSSTG_JOB":  AYSHIBAT/********@aysdb122.aysoskprvsubnet.aysoskvcn01.oraclevcn.com:1521/ayspdb122.aysoskprvsubnet.aysoskvcn01.oraclevcn.com dumpfile=DIR_WORK_DP:exp_tbl_stsstg.dmp logfile=DIR_WORK_DP:exp_tbl_stsstg.log tables=TBL_STSSTG job_name=EXP_TBL_STSSTG_JOB logtime=ALL
31-MAY-23 12:27:19.476: Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
31-MAY-23 12:27:19.769: Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
31-MAY-23 12:27:20.192: Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
31-MAY-23 12:27:21.666: Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
31-MAY-23 12:27:22.037: Processing object type TABLE_EXPORT/TABLE/TABLE
31-MAY-23 12:27:26.916: . . exported "AYSHIBAT"."TBL_STSSTG"                     56.20 KB       5 rows
31-MAY-23 12:27:27.810: Master table "AYSHIBAT"."EXP_TBL_STSSTG_JOB" successfully loaded/unloaded
31-MAY-23 12:27:27.930: ******************************************************************************
31-MAY-23 12:27:27.931: Dump file set for AYSHIBAT.EXP_TBL_STSSTG_JOB is:
31-MAY-23 12:27:27.933:   /home/oracle/work/exp_tbl_stsstg.dmp
31-MAY-23 12:27:27.997: Job "AYSHIBAT"."EXP_TBL_STSSTG_JOB" successfully completed at Wed May 31 12:27:27 2023 elapsed 0 00:00:13
```

```shell
#impdp実行結果サンプル
Import: Release 19.0.0.0.0 - Production on Wed May 31 12:41:27 2023
Version 19.19.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
31-MAY-23 12:41:34.944: Master table "AYSHIBAT"."IMP_TBL_STSSTG_JOB" successfully loaded/unloaded
31-MAY-23 12:41:35.255: Starting "AYSHIBAT"."IMP_TBL_STSSTG_JOB":  AYSHIBAT/********@aysdb19cep.aysoskprvsubnet.aysoskvcn01.oraclevcn.com:1521/ays19ceppdb.aysoskprvsubnet.aysoskvcn01.oraclevcn.com dumpfile=DIR_WORK_DP:exp_tbl_stsstg.dmp logfile=DIR_WORK_DP:imp_tbl_stsstg.log tables=TBL_STSSTG job_name=IMP_TBL_STSSTG_JOB logtime=ALL
31-MAY-23 12:41:35.352: Processing object type TABLE_EXPORT/TABLE/TABLE
31-MAY-23 12:41:36.065: Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
31-MAY-23 12:41:45.967: . . imported "AYSHIBAT"."TBL_STSSTG"                     56.20 KB       5 rows
31-MAY-23 12:41:46.041: Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
31-MAY-23 12:41:46.202: Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
31-MAY-23 12:41:46.353: Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
31-MAY-23 12:41:54.587: Job "AYSHIBAT"."IMP_TBL_STSSTG_JOB" successfully completed at Wed May 31 12:41:54 2023 elapsed 0 00:00:23
```

# 7. STSステージング表の展開(DBMS_SQLTUNE.UNPACK_STGTAB_SQLSETプロシージャ)
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSETプロシージャを実行してSTSステージング表を展開します。sqlset_name に '%'(ワイルドカード) を指定すると、ステージング表に格納されている全ての STS が展開されます。

```sql
BEGIN
  DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (
     '%'          -- sqlset_name
   , 'AYSHIBAT'   -- sqlset_owner
   , TRUE         -- replace
   , 'TBL_STSSTG' -- staging_table_name
   , 'AYSHIBAT'   -- staging_schema_owner
  );
END;
/
```

展開後に DBA_SQLSETディクショナリ を参照して STS が格納されていれば展開は成功です。

```sql
OWNER                NAME                 STATEMENT_COUNT CREATED              DESCRIPTION
-------------------- -------------------- --------------- -------------------- --------------------
AYSHIBAT             STS_1                              2 2023/05/31 06:06:28
```

DBMS_SQLTUNE.SELECT_SQLSETファンクション や DBMS_XPLAN.DISPLAY_SQLSETファンクション で STS に格納されている実行統計や実行計画を確認できます。

```sql
-- STS実行統計の確認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 PARSING_SCHEMA_NAME
     , MODULE
     , SQL_ID
     , PLAN_HASH_VALUE
     , SQL_TEXT, EXECUTIONS
     , ELAPSED_TIME
     , CPU_TIME
     , BUFFER_GETS
     , DISK_READS
  FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('STS_1'))
 ORDER BY 1, 2, 3, 4;
```

```sql
-- STS実行計画の確認SQLサンプル
SET PAGESIZE 300;
SET LINESIZE 170;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQLSET('STS_1', '4pxvc59brszvb', NULL));
```

```sql
-- STS実行統計の確認結果サンプル
PARSING_SCHEMA_ MODULE     SQL_ID          PLAN_HASH_VALUE SQL_TEXT                       EXECUTIONS ELAPSED_TIME   CPU_TIME BUFFER_GETS DISK_READS
--------------- ---------- --------------- --------------- ------------------------------ ---------- ------------ ---------- ----------- ----------
AYSHIBAT        SQL*Plus   4pxvc59brszvb         123745559 SELECT * FROM TEST1 WHERE C1 =         27         2753       2587         108          0
AYSHIBAT        SQL*Plus   8ccf9wzug01xg        3713220770 SELECT * FROM TABLE(DBMS_XPLAN          2        73469      68614         863          3
```

```sql
-- STS実行計画の確認結果サンプル
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Tuning Set Name: STS_1
SQL Tuning Set Owner: AYSHIBAT
SQL_ID: 4pxvc59brszvb
SQL Text: SELECT * FROM TEST1 WHERE C1 = :B1
--------------------------------------------------------------------------------

Plan hash value: 123745559

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1    |     1 |    23 |     3   (0)| 00:00:01 |
|   2 |   INDEX UNIQUE SCAN         | TEST1_PK |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------


16 rows selected.
```

# 8. まとめ
STS をデータベース間で移行することができました。STS は RAT(Real Application Testing) の SPA(SQL Perfomanct Analyzer) を活用する為に欠かせないオブジェクトです。また STS単体でも活用可能です。

RAT の SPA と STS および STS単体 でも活用して下さいね。彡(^)(^)

> データベースアップグレードに有効 - Real Application Testing入門- 
> https://speakerdeck.com/oracle4engineer/oracle-database-real-application-testingru-men