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プロシージャ で作成します。
BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
'TBL_STSSTG' -- table_name
, 'AYSHIBAT' -- schema_name
);
END;
/
この時点でステージング表は空(0件)となります。
SELECT COUNT(*) FROM AYSHIBAT.TBL_STSSTG;
COUNT(*)
----------
0
5. STSステージング表にSTSの内容を格納(DBMS_SQLTUNE.PACK_STGTAB_SQLSETプロシージャ)
次に DBMS_SQLTUNE.PACK_STGTAB_SQLSETプロシージャを実行してステージング表に STS を格納します。
BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
'STS_1' -- sqlset_name
, 'AYSHIBAT' -- sqlset_owner
, 'TBL_STSSTG' -- staging_table_name
, 'AYSHIBAT' -- staging_schema_owner
);
END;
/
実行後にステージング表にレコードが格納されていれば成功です。
SELECT COUNT(*) FROM AYSHIBAT.TBL_STSSTG;
COUNT(*)
----------
5
6. ステージング表を Export/Import(expdp/impdp) で別データベースに移行
ステージング表を Export/Import(expdp/impdp) で別データベースに移行します。下記はexpdp/impdpのコマンドと実行結果のサンプルとなります。
#移行元の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
#移行先の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
#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 が展開されます。
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 が格納されていれば展開は成功です。
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 に格納されている実行統計や実行計画を確認できます。
-- 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;
-- STS実行計画の確認SQLサンプル
SET PAGESIZE 300;
SET LINESIZE 170;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQLSET('STS_1', '4pxvc59brszvb', NULL));
-- 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
-- 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