目次
おさらい～SPAの手順概要～
こんにちは、まっつんです！
前回の Oracle Real Application Testing を使ってみよう！その1 はいかがでしたか？
今回は実際にRATの機能の1つ、SQL Performance Analyzer (SPA) の手順を追っていきたいと思います。
RATの全体的な説明は前回の記事で紹介していますので、あわせてご覧ください
その前に前回にもお話ししたSPAの「主な手順概要」をおさらいしましょう。
主な手順概要
[SPA手順1] SQLチューニング・セット(STS)作成
– 本番環境でSQLワークロード(指定期間に実行されたSQL群の情報)を取得、SQL Tuning Set (STS) に格納
[SPA手順2] SQLチューニング・セット(STS)移動
– STSを本番環境からテスト環境にExport/Importで移動
[SPA手順3] SQLテスト
– システム変更の前にSQLを実行し、変更前のベースラインを取得 (本番で取得済のものを利用することで省略も可能)
– システム変更を実施し、同様にSQLを実行
[SPA手順4] レポート生成
– 比較レポート生成、STS全体とSQL単位についてそれぞれのパフォーマンス情報を比較
SPA実行の流れ
SPAでは、主に取得したSQLワークロードの保存・取り出しからのSQL再現までを「SQLチューニングセット(STS)」を中心にオペレーションしていく流れとなります。
STSって？
■ 以下の方法で格納されたSQLワークロード情報
A. 一定時間(指定可能)ごとにカーソルキャッシュから情報を収集
B. AWRスナップショット(過去情報)から収集
■ STSに含まれるデータ
・SQL文
・実行コンテキスト(スキーマ、アプリケーション・モジュール名、バインド値など)
・実行統計
・実行計画
■ STSに格納するSQLは一定条件でフィルタすることも可能
■ キャプチャ時1コアを占有
■ STSはSYSAUX表領域に格納される
※ 多くのSQLを格納するとSYSAUXが肥大化するため、事前にサイジングを行う必要があります)
※ 目安として、1SQLあたり5～20KB程度を使用します。
※ V$SYSAUX_OCCUPANTSのビューからSTSを含む領域の使用量を確認することが可能です
事前準備
今回は「本番環境」「テスト環境」の2つの環境を使用して検証していきたいと思います。
また、上の「SPA実行の流れ」図にもあります、パターンA「STSに取得されている実行結果をSPA1回目の実行結果とする場合」で実施しますので、テスト環境は既にOracleバージョンアップ後の19c環境を用意してあります。
この検証ではどちらもCDB構成のデータベースを使用していますが、非CDBでも同様に実施可能です。適宜読み替えていただければと思います。
また、「本番環境」「テスト環境」はともに論理構成は同じ(※)ものであることが前提です。今回は検証用に、Oracle Cloud の Oracle Base Database Service を使用しています。
※論理構成が同じとは、スキーマ・テーブル等構成・データが等しく、同じSQLで同じ結果が得られる環境という意味です。
シナリオ
– 本番環境 12c を 19c へバージョンアップすることを計画している。
– バージョンアップに伴い、オプティマイザ統計情報は取りなおす予定だが、いくつかのクリティカルなSQLの実行計画が変化しないか懸念している。
– リソースとスケジュールの関係上、今回の検証には 19c のテスト環境だけ用意できており、検証はなるべくこの環境のみで完結させたい。
必要環境
– Oracle Databaseインスタンス「本番環境」 … Oracle Database Enterprise Edition 12c (Oracle Base Databse Service)を使用します。
– Oracle Databaseインスタンス「テスト環境」 … Oracle Database Enterprise Edition 19c (Oracle Base Databse Service)を使用します。
– SQLの実行環境(SQL*PlusやSQL Developer)
– 被検対象のスキーマ、SQL
[SPA手順1] SQLチューニング・セット(STS)作成
① STS作成
本番環境で、STS所有者としてSOEという名前のユーザーを作成します。
ここでは話を簡単にするためテスト対象となるSQLの所有者と同一にしていますが、実際に運用する際は被検対象のSQL所有者とSTS所有者は分けた方がよいでしょう。
いろいろやりたいので、コマンド例ではDBA権限なども付与していますが、必要最低権限は赤字の箇所になります。
conn / as sysdba alter session set container=pdb1; create user SOE identified by soe; grant connect, resource to SOE; grant dba to SOE; grant advisor to SOE; grant administer any sql tuning set to SOE; grant select_catalog_role to SOE;
SOEユーザーの準備ができたら本番データベース(ここでは接続名”PDB1″)へ接続し、”STS01″という名前の空のSTSを作成します。
conn soe@pdb1 begin DBMS_SQLTUNE.CREATE_SQLSET( sqlset_owner => 'SOE', -- STSの所有者名、現行のスキーマ所有の場合はNULL sqlset_name => 'STS01', -- STS名 description => 'for STS test1' -- SQLチューニング・セットの説明 ); end; /
STSが作成されたかどうかは以下のSQLで確認できます。
set lin 200
col owner for a10
col name for a12
col description for a30
select owner, name, statement_count, description from dba_sqlset;
OWNER NAME STATEMENT_COUNT DESCRIPTION
---------- ---------- --------------- ------------------------------
SOE STS01 0 for STS test1
なお、STSを削除する場合は以下のSQLを実行します。
exec DBMS_SQLTUNE.DROP_SQLSET( sqlset_owner => 'SOE', sqlset_name => 'STS01' );
② STSにSQLをロード
取得したいSQLワークロード実行後、カーソルキャッシュから取得したSQLワークロードを取得します。
ここではSOEユーザーが発行する、経過時間の長い上位1000個の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', -- SOE発行の実行計画が存在するSQLが対象
object_filter => NULL,
ranking_measure1 => 'ELAPSED_TIME', -- 経過時間でソートして
result_limit => 1000, -- 上位1000個
recursive_sql => DBMS_SQLTUNE.NO_RECURSIVE_SQL -- 再起的SQLを除外
)
) p;
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_owner => 'SOE', -- 格納先STSのオーナー
sqlset_name => 'STS01', -- 格納先STS名
populate_cursor => sqlset_cur,
load_option => 'INSERT' -- 実行のたびSTSにないSQLを追加していく
);
end;
/
DBMS_SQLTUNE.SELECT_CURSOR_CACHE では様々な種類のフィルタの掛け方があります。
詳細は以下のドキュメントを参照してください。
https://docs.oracle.com/cd/F19136_01/arpls/DBMS_SQLTUNE.html#GUID-848CFD63-C98D-4FBE-9738-23FF7A58A5A2
カーソルキャッシュに存在するSQLがうまく取得されれば、以下のSQLの”STATEMENT_COUNT“が1以上となっているはずです。
set lin 200 col owner for a10 col name for a12 col description for a30 select owner, name, statement_count, description from dba_sqlset; OWNER NAME STATEMENT_COUNT DESCRIPTION ---------- ---------- --------------- ------------------------------ SOE STS01 19 for STS test1
③ STSの内容確認
取得されたSQLの情報を詳細に見るには以下のSQLを実行します。
set lin 200 pages 50000
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,parsing_schema_name,sql_text,
executions,elapsed_time,cpu_time,buffer_gets,
disk_reads,fetches,priority
from table( dbms_sqltune.select_sqlset( sqlset_owner => 'SOE', sqlset_name => 'STS01') )
order by 1;
SQL_ID MODULE PARSING_SCHEMA_ SQL_TEXT EXECUTIONS ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS FETCHES PRIORITY
--------------- ---------- --------------- ------------------------------ ---------- ------------ ---------- ----------- ---------- ---------- ----------
1cju8dwkbd5md SQL*Plus SOE select * from test1 where job 1 685556 671797 48237 0 2
2cstc3v2j32uk SQL*Plus SOE select owner, name, statement_ 4 8372 4532 131 7 8
2s9mmb6g8kbqb SQL*Plus SOE SELECT DECODE('A','A','1','2') 1 961 693 0 0 1
57a9vjhmb3hcq SQL*Plus SOE select * from test1 where name 1 664697 650287 48237 0 2
5dqpncq5vrkbs SQL*Plus SOE select count(*) from test1 whe 1 1374981 1212250 48515 0 1
5yx2xg6bzrq5z SQL*Plus SOE select /* tst */ * from test1 1 851387 823744 48238 0 3
6ty6dd2wqd116 SQL*Plus SOE INSERT INTO TEST1 (NAME, JOB, 10000000 409431157 368197935 42906070 5 0
6u5zqzz2nm55c sqlplus@db SOE SELECT DECODE(USER, 'XS$NULL', 1 1256 831 6 0 1
7hys3h7ysgf9m sqlplus@db SOE SELECT ATTRIBUTE,SCOPE,NUMERIC 1 24977 8830 171 2 1
7u8bxnm7kugzd SQL*Plus SOE select count(*) from test1 1 2620 2367 15 0 1
9tj2bud1c7nn2 SQL*Plus SOE SELECT VALUE(P) FROM TABLE( DB 1 12692 11735 429 0 0
b5td4n3ctk713 SQL*Plus SOE select count(distinct(name)) f 1 7096878 6461631 48256 2760 1
bqf344y6nmbcg SQL*Plus SOE select 3 94736 87409 2080 1 54
cczupb6p1t5dm SQL*Plus SOE select * from user_sys_privs 1 26823 25110 89 1 2
cw6vxf0kbz3v1 sqlplus@db SOE SELECT CHAR_VALUE FROM SYSTEM. 1 3698 2156 10 0 1
d5kq7ju7ah075 SQL*Plus SOE select count(*) from test1 whe 1 716746 695840 48236 0 1
f2du4xr9d39w6 SQL*Plus SOE select count(*) from test1 whe 1 715621 702212 48236 0 1
g9dqvp38y2hhb SQL*Plus SOE select * from test1 where name 1 504254 357698 30707 0 618
gk7b08nbskz1n SQL*Plus SOE select sys_context('USERENV', 1 1131 1156 0 0 1
19行が選択されました。
さらに詳細に確認するため、例えば、上記のSQL_ID “5yx2xg6bzrq5z” の実行計画を以下のSQLで確認できます。
select * from table(
dbms_xplan.display_sqlset(sqlset_name => 'STS01', sql_id => '5yx2xg6bzrq5z', sqlset_owner => 'SOE')
);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL Tuning Set Name: STS01
SQL Tuning Set Owner: SOE
SQL_ID: 5yx2xg6bzrq5z
SQL Text: select /* tst */ * from test1 where name like 'ABCD%'
--------------------------------------------------------------------------------
Plan hash value: 4122059633
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS FULL| TEST1 | 1 | 39 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
15行が選択されました。
[SPA手順2] SQLチューニング・セット(STS)移動
④ STSのエクスポート
ここまで本番環境で取得したSTSをテスト環境へ移送するため、SOEユーザーには必要な権限を付与しておきます。
conn / as sysdba alter session set container=pdb1; grant create any directory to SOE; alter user SOE quota unlimited on users;
本番環境でSOEユーザーでSTSを格納するための「ステージング表」を作成します。
STS自体はSYSAUXに格納された特殊なデータですので、ここで移送のために一度テーブルに格納します。
conn soe/soe@pdb1
begin
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
table_name => 'STS_STGTAB',
schema_name => 'SOE',
tablespace_name => 'USERS'
);
end;
/
ステージング表にSTSを移入(パック)します。
begin
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
sqlset_name => 'STS01',
sqlset_owner => 'SOE',
staging_table_name => 'STS_STGTAB',
staging_schema_owner => 'SOE'
);
end;
/
ステージング表をダンプする出力先用のディレクトリオブジェクトを作成します。
create or replace directory STS_DIR AS '<任意のディレクトリ>';
Data Pump ユーティリティでステージング表を上記ディレクトリオブジェクト上でダンプに出力します。
expdp soe/<パスワード>@pdb1 directory=STS_DIR dumpfile=STS01.dmp logfile=STS01_exp.log tables=SOE.STS_STGTAB
上記でディレクトリオブジェクト STS_DIR で指定されたディレクトリに出力したダンプファイルは、テスト環境へコピー(scpなど)しておきます。
ここでは本番環境からテスト環境へ、同等のディレクトリへ scp しています。
⑤ STSのインポート
以降の作業はテスト環境にて実施します。テスト環境にてSTSを受け入れるための準備をします。
本番環境と同様に、SOEユーザーの作成、権限付与を済ませておきます。
conn / as sysdba alter session set container=pdb1; create user SOE identified by soe; grant connect,resource to soe; grant dba to soe; grant advisor to SOE; grant administer any sql tuning set to SOE; grant select_catalog_role to SOE; grant create any directory to SOE; alter user SOE quota unlimited on users;
ステージング表をインポートするためのディレクトリオブジェクトを作成します。
conn soe@pdb1
create or replace directory STS_DIR AS '<任意のディレクトリ>';
Data Pump ユーティリティでステージング表を上記ディレクトリオブジェクトにコピーしたダンプファイルからインポートします。
impdp soe/<パスワード>@pdb1 directory=STS_DIR dumpfile=STS01.dmp logfile=STS01_imp.log tables=SOE.STS_STGTAB
ステージング表をアンパックします。
これでSTS情報が本番環境からテスト環境へコピーされたことになります。
begin
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_owner => 'SOE',
replace => true,
staging_table_name => 'STS_STGTAB',
staging_schema_owner => 'SOE'
);
end;
/
実際にSTS情報がコピーされたか確認してみましょう。
1行目の “SYS_AUTO_STS” は19c新機能でデフォルトで存在するものなので無視してください。
set lin 200 col owner for a10 col name for a12 col description for a30 select owner, name, statement_count, description from dba_sqlset; OWNER NAME STATEMENT_COUNT DESCRIPTION ---------- ------------ --------------- ------------------------------ SYS SYS_AUTO_STS 0 System auto SQL Tuning Set <-デフォルト(19c新機能) SOE STS01 19 for STS test1
[SPA手順3] SQLテスト
⑥ SPAタスクの作成
SPAを実行するためにSPA管理者 “SPAADMIN” を作成します。
DBAロールの付与を推奨します。
conn / as sysdba alter session set container=pdb1; create user SPAADMIN identified by spaadmin; grant dba to SPAADMIN;
SPAADMINユーザーでSPAタスクを作成します。
インポートされているSTSとの紐づけもここで行われます。
conn spaadmin@pdb1
variable stmt_task varchar2(64)
begin
:stmt_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
task_name => 'SPATASK01',
sqlset_owner => 'SOE',
sqlset_name => 'STS01'
);
end;
/
作成したSPAタスクを確認します。
col owner for a10 col task_name for a20 select owner, task_name, status from dba_advisor_tasks where advisor_id=8; OWNER TASK_NAME STATUS ---------- ------------------------------ ----------- SYS SYS_AI_VERIFY_TASK INITIAL <-デフォルト(19c新機能) SPAADMIN SPATASK01 INITIAL
advisor_id=8 は「SQL Performance Analyzer」であることを示しています。
余談ですが、Oracleのアドバイザ・タスクは他にも色々ありますが、以下のSQLで知ることができます。
select ADVISOR_ID, ADVISOR_NAME from dba_advisor_usage;
ADVISOR_ID ADVISOR_NAME
---------- ------------------------
1 ADDM
2 SQL Access Advisor
3 Undo Advisor
4 SQL Tuning Advisor
5 Segment Advisor
6 SQL Workload Manager
7 Tune MView
8 SQL Performance Analyzer
9 SQL Repair Advisor
10 Compression Advisor
11 SPM Evolve Advisor
12 Statistics Advisor
⑦ 1回目のSPA実行
1回目のSPAを実行します。
ただし、今回は前述の通り「STSに取得されている実行結果をSPA1回目の実行結果とする場合」を実施しますので、実際に被検SQLが発行されることはありません。
begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'SPATASK01', execution_type => 'CONVERT SQLSET', execution_name => 'first trial' ); end; /
“execution_type” の ‘CONVERT SQLSET’ が、STSに取得されている統計情報を読み取り、タスク実行のモデルとして使用することを意味しています。
今回は割愛しますが、DBLINKを経由して実際の本番環境へSQLを発行して統計情報を取得する場合は、DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER プロシージャを使用して DBMS_SQLPA.EXECUTE_ANALYSIS_TASK のオプションを変更する必要があります。
詳細は以下を参照してください。
https://docs.oracle.com/cd/F19136_01/arpls/DBMS_SQLPA.html#GUID-7A8BB3B0-89B5-49A7-99A8-59AA0F939058
⑧ 2回目のSPA実行
2回目のSPAを実行します。
今回の場合、ここで実際にテスト環境内でSTSに記録されているSQLが発行され統計情報が収集されます。
begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'SPATASK01', execution_type => 'TEST EXECUTE', execution_name => 'second trial' ); /
“execution_type” の ‘TEST EXECUTE’ が、すべてのSQL文をテスト実行し、実行計画および実行の統計情報を収集することを意味しています。
[SPA手順4] レポート生成
⑨ 2回の試行結果から分析タスクの実行
2度実施したSPAの試行結果から、分析タスクを実行します。
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPATASK01',
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;
/
⑩ 2回の試行結果からレポート作成
分析結果からレポートを作成します。
set echo on set pages 0 set lines 150 set long 10000000 set trimspool on var rep clob col rep for a150 begin :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK( task_name => 'SPATASK01', type => 'HTML', level => 'ALL', section => 'ALL', execution_name => 'COMPARED RESULT1' ); end; / spool <任意の場所>/SPATASK01.html print :rep spool off
spool 先を指定すると任意の場所に出力することが可能です。
出力されたレポートは以下のような形式です。（実際にはもっと長いです）
レポートでは、各SQLごとに実行計画やパフォーマンスがどう変化したか、性能統計情報とともに非常に詳細に説明されています。
次回へ続く
いかがでしたでしょうか。
ここで紹介した手順はSPAの代表的な流れのみでしたが、
代表的な流れだけでも、結果のレポートではSQLごとに非常に細かく分析されています。
次回はRATのもう一つの機能、DB Replay について紹介していきたいと思います。
RATの全体的な説明が気になるかたは、前回の記事もあわせてご覧くださいね！
投稿者プロフィール
- 株式会社システムサポート インフラソリューション事業部に在籍するPlatinumホルダー。アプリケーション開発からOracleコンサルまで幅広く経験。当サイトの一部ビジュアルデザインも担当。現在ダイエット中。
Oracle Real Application Testing2023年3月13日Oracle Real Application Testing を使ってみよう！その3- Oracle Real Application Testing2023年3月10日Oracle Real Application Testing を使ってみよう！その2
- Oracle Real Application Testing2023年3月8日Oracle Real Application Testing を使ってみよう！その1
VLDBとパーティショニング2018年12月25日[超図解]パーティション化(1/5) パーティション事始め
