概要
Oracle DatabaseのSQL Tuning Set(STS)とは、データベースで実行された複数のSQLの下記のような情報を含むデータベースオブジェクトです。
- SQL文
- 実行計画
- 実行コンテキスト（スキーマ、アプリケーション・モジュール名など）
- 実行統計（実行時間、CPU時間、バッファ読み取り量、ディスク読み取り量など）
あるOracleデータベースでSTSに取得されたSQLの情報をファイルに出力して、別のOracleデータベースに移動（コピー）させることができます。つまり、実環境のデータベースで取得したSQL群の情報を含むSTSを、開発・テスト環境にコピーさせ、開発・テスト環境のOracleデータベースで利用することができます。Oracle Databaseのテストのための機能：Real Application Testing(RAT)のSQL Performance Analyzer(SPA)は、STSを移動できることが前提の機能です。
本記事では、STSをデータベース間でコピーする手順を紹介します。データベースをアップグレードさせてテストするシナリオを想定して、Oracle Databaseの11g R2で取得したSTSをOracle Databaseの19cにコピーします。紹介するのはOracle Enterprise Managerの画面からの実行手順ではなく、SQLの実行環境からコマンド(SQL,PL/SQL)でSTSへSQL群の情報を取得する方法です。
STSの取得方法に関しては下記の記事が参考になります。本記事の手順は、下記の記事に続けて実施したときのものです。
Oracle DatabaseのSQL Tuning Set(STS)の活用：STSへSQL実行時の情報を記録する
2019年12月ごろの情報・環境を元に本記事は記載しています。
1. 本記事の作業の前提
本記事で紹介している手順は、STS取得元となるデータベースをOracle Databaseの11g R2(11.2.0.4)、STSのコピー先となるデータベースを19cで確認しています。実行するには下記が必要です。
- STS取得元となるOracle Databaseインスタンス（本記事では11g R2）
- STSコピー先となるOracle Databaseインスタンス（本記事では19c）
- SQLの実行環境(SQL*PlusやSQL Developer)
2. STSをデータベース間で移動（コピー）させる作業の流れ
STSをデータベース間でコピーする作業の流れは次のようになります。
(STS取得元での作業)
STEP0. 移動（コピー）するSTSにSQL群を取得する
STEP1. STS用のステージング表を作成する
STEP2. ステージング表にSTSを出力する
STEP3. Datapumpでステージング表をダンプファイルに出力する
(ファイルのコピー)
STEP4. STS取得元サーバーからSTSコピー先へダンプファイルをコピーする
(STSコピー先での作業)
STEP5. Datapumpでダンプファイルからステージング表に読み込む
STEP6. ステージング表からSTSを生成する
STSの情報をファイルに出力するために、まずSTSの情報を格納するための「ステージング表」にSTSの内容を出力させます。ステージング表のデータを、Datapumpを使ってデータベース間で移動させます。
本記事では、STEP1.～3.とSTEP5.～6.の手順を紹介します。STEP4.のファイルのコピーはftpやscpなどで実行してください。
3. STSをダンプファイルに出力する
最初にコピーするSTSの情報を確認しておきます。ここでは、SOEというユーザーがSTSを所有しています。SOEユーザーで下記の作業は実行しています。SOEユーザーには、「Oracle DatabaseのSQL Tuning Set(STS)の活用：STSへSQL実行時の情報を記録する」この記事で付与しているADVISOR権限、ADMINISTER ANY SQL TUNING SET権限、SELECT_CATALOG_ROLE権限に加えて、CREATE ANY DIRECTORY権限を付与し表領域USERSに対する領域割り当てを無制限にしています。
SQL> GRANT CREATE ANY DIRECTORY TO SOE;
権限付与が成功しました。
SQL> ALTER USER SOE QUOTA UNLIMITED ON USERS;
ユーザーが変更されました。
SOEユーザーで下記のSQLを実行し、所有しているSTSの情報を確認します。
col owner FORMAT a10
col name FORMAT a30
SELECT
owner,
name,
statement_count
FROM
dba_sqlset;
(実行結果)
OWNER NAME STATEMENT_COUNT
---------- ------------------------------ ---------------
SOE STS01 60
SOEユーザーが保有するSTS01というSTSに60個のSQLの情報が記録されています。このSTSを別のデータベースへコピーすることにします。
3.1. STS用のステージング表を作成する
まずSTSを出力させるためのステージング表を作成します。下記のようなPL/SQLを実行します。ここの例では、STSのオーナーであるSOEユーザーで実行しています。
BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
table_name => 'STS_STGTAB', -- 作成するステージング表の名前
schema_name=> 'SOE', -- 作成するステージング表のオーナー
tablespace_name => 'USERS' -- ステージング表を作成する表領域
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
これで、SOEユーザーが保有するステージング表STS_STGTABが作成されます。表が作成されたことを確認します。
DESC SOE.STS_STGTAB
(実行結果)
名前 Nullかどうか タイプ
--------------------------- -------- --------------
ID NUMBER
NAME VARCHAR2(30)
OWNER VARCHAR2(30)
DESCRIPTION VARCHAR2(256)
SQL_ID VARCHAR2(13)
SQL_SEQ NUMBER
FORCE_MATCHING_SIGNATURE NUMBER
（以下略）
ステージング表(ここの例では名前をSTS_STGTABとしました)が作成されています。もちろんまだ中身は空です。
3.2. ステージング表にSTSを出力する
作成したステージング表(ここの例ではSOEユーザーのSTS_STGTABという表)に、コピーする対象のSTSを出力します。下記のようなPL/SQLを実行します。
BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
sqlset_name => 'STS01', --STSの名前
sqlset_owner => 'SOE', --STSのオーナー
staging_table_name => 'STS_STGTAB', --作成済みのステージング表の名前
staging_schema_owner => 'SOE' --ステージング表のオーナー
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
出力するSTSの名前とオーナー、ステージング表の名前とオーナーをそれぞれ指定するだけです。ステージング表に何か出力されているか確認します。
SELECT COUNT(*) FROM SOE.STS_STGTAB
(実行結果)
COUNT(*)
----------
837
ステージング表(ここの例ではSTS_STGTAB)に出力されたようです。このステージング表をDatapumpでファイルに出力して移動します。
3.3. Datapumpでステージング表をダンプファイルに出力する
ステージング表をDatapumpでファイルに出力する方法は、通常のDatapumpの使い方と違いはありません。ここので実行例を紹介します。まずダンプファイルを出力するディレクトリオブジェクトを作成します。/tmpに作成することにしました。ここの例では、Datapumpはステージング表のオーナーであるSOEユーザーで実行しています。
CREATE or REPLACE DIRECTORY "STS_DIR" AS '/tmp/';
(実行結果)
Directory "STS_DIR"は作成されました。
ディレクトリオブジェクトが作成されていることを確認します。
SELECT directory_name, directory_path FROM DBA_DIRECTORIES where directory_name = 'STS_DIR';
(実行結果)
DIRECTORY_NAME
------------------------------
DIRECTORY_PATH
----------------------------------------------------------------------------
STS_DIR
/tmp/
Datapumpのexpdpを実行します。データベースサーバーのシェルから下記のように実行します。
[oracle@suga_carp11g ~]$ expdp soe/(パスワードを指定) directory=STS_DIR dumpfile=STS01.dmp LOGFILE=STS01.log tables=SOE.STS_STGTAB
xport: Release 11.2.0.4.0 - Production on 火 12月 24 08:31:07 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
接続先: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters, Automatic Storage Management and Real Application Testing options
"SOE"."SYS_EXPORT_TABLE_01"を起動しています: soe/******** directory=STS_DIR dumpfile=STS01.dmp LOGFILE=STS01.log tables=SOE.STS_STGTAB
BLOCKSメソッドを使用して見積り中です...
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
BLOCKSメソッドを使用した見積り合計: 1.187 MB
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
(略)
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
. . "SOE"."STS_STGTAB" 547.1 KB 837行がエクスポートされました
マスター表"SOE"."SYS_EXPORT_TABLE_01"は正常にロード/アンロードされました
******************************************************************************
SOE.SYS_EXPORT_TABLE_01に設定されたダンプ・ファイルは次のとおりです:
/tmp/STS01.dmp
ジョブ"SOE"."SYS_EXPORT_TABLE_01"が火 12月 24 08:31:28 2019 elapsed 0 00:00:20で正常に完了しました
[oracle@suga_carp11g ~]$ ls -al /tmp/STS01.dmp
-rw-r----- 1 oracle asmadmin 794624 Dec 24 08:31 /tmp/STS01.dmp
Datapumpによりステージング表のデータがダンプファイルに出力されていることが確認できます。ここまでが、本記事でのSTS取得元での作業です。
ダンプファイル(ここの例では/tmp/STS01.dmp)を、STSのコピー先となるデータベースサーバーにコピーしてください。
4. ダンプファイルに出力されたSTSを読み込む
ここからがSTSコピー先での作業です。事前にSTSとステージング表のオーナーとなるユーザーを作成しておいてください。ここの例では、STS取得元と同じ名前(SOE)でユーザーを作成しています。
SOEユーザーにはアプリケーションを実行するための権限、ロール以外に、ADVISOR権限、ADMINISTER ANY SQL TUNING SET権限、SELECT_CATALOG_ROLE権限、CREATE ANY DIRECTORY権限を付与しています。また表領域USERSに対する領域割り当てを無制限にしています。下記が実行例です。
SQL> GRANT ADVISOR TO SOE;
権限付与が成功しました。
SQL> GRANT ADMINISTER ANY SQL TUNING SET TO SOE;
権限付与が成功しました。
SQL> GRANT SELECT_CATALOG_ROLE TO SOE;
権限付与が成功しました。
SQL> GRANT CREATE ANY DIRECTORY TO SOE;
権限付与が成功しました。
SQL> ALTER USER SOE QUOTA UNLIMITED ON USERS;
ユーザーが変更されました。
コピーするSTSの中のSQLが参照している表などのオブジェクトを事前に作成しておく必要はありません。ただしコピーしたSTSを使ってテストなどを実行する際には、それらのオブジェクトは必要になります。
4.1. SYSAUX表領域の空き領域の確認
STSはSYSAUX表領域に格納されます。STSをコピーするデータベースのSYSAUX表領域の空き領域を確認してください。空きが足りないようならば拡張する必要があります。このあたりは、STS取得の際の留意点と同じです。下記の記事も参考にしてください。
Oracle DatabaseのSQL Tuning Set(STS)の活用：STSへSQL実行時の情報を記録する ⇒ 2. SYSAUX表領域の空き領域の確認
SYSAUXの空き領域は下記のようなSQLで確認できます。
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 350.00 331.31 18.69
4.2. Datapumpでダンプファイルからステージング表に読み込む
STS取得元からコピーしたダンプファイルを、ステージング表に読み込みます。Datapumpを実行することでステージング表も作成されます（なのでステージング表を事前に作成してくてもよいです）。
通常のDatapumpの使い方と違いはありません。ここので実行例を紹介します。まずダンプファイルは/tmpに配置しています。あわせてディレクトリオブジェクトを/tmpに作成しています。
CREATE or REPLACE DIRECTORY "STS_DIR" AS '/tmp/';
(実行結果)
Directory "STS_DIR"は作成されました。
ディレクトリオブジェクトが作成されたことを確認します。
SELECT directory_name, directory_path FROM DBA_DIRECTORIES where directory_name = 'STS_DIR';
(実行結果)
DIRECTORY_NAME
------------------------------
DIRECTORY_PATH
----------------------------------------------------------------------------
STS_DIR
/tmp/
Datapumpのimpdpを実行します。ここの例では、Datapumpはステージング表のオーナーであるSOEユーザーで実行しています。データベースは19cで、マルチテナントのCDB構成になっており、STSを読み込む先のPDBへの接続文字列を指定しています。
[oracle@suga_carp19g ~]$ impdp soe/(パスワード)@(対象PDBへの接続文字列) directory=STS_DIR dumpfile=STS01.dmp LOGFILE=STS01.log tables=SOE.STS_STGTAB
Import: Release 19.0.0.0.0 - Production on 火 12月 24 08:42:13 2019
Version 19.5.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
接続先: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
マスター表"SOE"."SYS_IMPORT_TABLE_01"は正常にロード/アンロードされました
"SOE"."SYS_IMPORT_TABLE_01"を起動しています: soe/********@sales directory=STS_DIR dumpfile=STS01.dmp LOGFILE=STS01.log tables=SOE.STS_STGTAB
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
. . "SOE"."STS_STGTAB" 547.1 KB 837行がインポートされました
ジョブ"SOE"."SYS_IMPORT_TABLE_01"が火 12月 24 08:42:25 2019 elapsed 0 00:00:09で正常に完了しました
ステージング表が作成され、データが読み込まれたことを確認します。
SELECT COUNT(*) FROM SOE.STS_STGTAB
(実行結果)
COUNT(*)
----------
837
4.3. ステージング表からSTSを生成する
ダンプファイルから読み込んだステージング表を元に、STSを生成します。下記のようなPL/SQLを実行します。
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (
sqlset_owner => 'SOE', -- 新たに作成するSTSのオーナー
replace => true,
staging_table_name => 'STS_STGTAB', -- STSのステージング表の名前
staging_schema_owner => 'SOE'); -- ステージング表のオーナー
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
ステージング表と新たに作成するSTSのオーナーを指定するだけです。STS名は指定する必要はありません。ステージング表に複数のSTSの情報がある場合は、STS名を指定して、特定のSTSだけ生成させることができます。
引数のreplaceにtrueを指定することで、ステージング表にあるSTSと同じ名前のSTSが既に存在していても上書きします。falseを指定しステージング表が存在した場合は、エラーとなります。
STSがステージング表から生成されたことを確認しましょう。次のようなSQLを実行します。
col owner FORMAT a10
col name FORMAT a30
SELECT
owner,
name,
statement_count
FROM
dba_sqlset;
(実行結果)
OWNER NAME STATEMENT_COUNT
---------- ------------------------------ ---------------
SOE STS01 60
SYS SYS_AUTO_STS 0
STS取得元と同じ名前、SQL数でSTSが生成されていることが確認できます。
（SYS_AUTO_STSという名前のSTSは19cの新機能用に作成されているSTSのようです。）
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
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行が選択されました。
STSに取得されたSQL群の情報があることが確認できます。SQL_IDを指定して、実行計画も確認してみましょう。
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 |
----------------------------------------------------------------------------------------------------------
28行が選択されました。
STSの取得元と同じようにSQL,実行計画の情報が参照できます。
5. 参考情報
STSの取得方法に関しては下記の記事が参考になります。本記事の手順は、下記の記事に続けて実施したときのものです。
Oracle DatabaseのSQL Tuning Set(STS)の活用：STSへSQL実行時の情報を記録する