はじめに
statspackのインストールと使い方。
Enterprise Edition、Diagnostics Packライセンスがあればstatspackを使用せず、AWRを使用する。
ただし、そのような恵まれたプロジェクトに入ることは少ないため、statspackは未だに現役かつ重要。
以下、Oracle12c R2で試していますが、操作自体は他のバージョンでも同じ（はず）。
statspackのインストール方法
$ sqlplus / as sysdba
statspack 用の表領域作成
SQL> create tablespace stats datafile '/oracle/app/oracle/oradata/testdb/stats.dbf' size 300m autoextend on next 30m segment space management auto;
※データファイル（stats.dbf）のパスは環境に合わせて修正してください。
インストールスクリプトの実行
SQL> @?/rdbms/admin/spcreate.sql
perfstat_passwordに値を入力してください: perfstat ★任意の文字列を入力する
default_tablespaceに値を入力してください: stats ★作成した表領域名を入力する
temporary_tablespaceに値を入力してください: ★ temp表領域名を入力する。デフォルトで可。
statspackの操作
スナップショットレベルの変更
デフォルトは5、SQL詳細やセグメントの情報が欲しい場合は7。通常は7に変更するのを推奨。それ以上はオーバーヘッドもあるので使うことはあまりないと思う（使ったことがない）。
SQL> connect perfstat/perfstat
SQL> execute statspack.modify_statspack_parameter(i_snap_level=> 7)
スナップショットの取得
SQL> execute statspack.snap
取得済みのスナップショット確認方法
select snap_id, to_char(snap_time, 'yyyy-mm-dd hh24:mi:ss') snap_time from stats$snapshot order by snap_id;
SNAP_ID SNAP_TIME
---------- ---------------------------------------------------------
1 2018-11-23 15:54:09
1行が選択されました。
レポート出力
SQL> @?/rdbms/admin/spreport.sql
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
testdb TESTDB 1 23 11月 2018 15:5 7
4
2 23 11月 2018 16:0 7
0
Specify the Begin and End Snapshot Ids
\~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
begin_snapに値を入力してください: 1
Begin Snapshot Id specified: 1
end_snapに値を入力してください: 2
End Snapshot Id specified: 2
Specify the Report Name
\~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2. To use this name,
press <return> to continue, otherwise enter an alternative.
report_nameに値を入力してください:
★入力せずENTERすると、sp_1_2.lst（開始IDと終了ID）でレポート作成される。
過去のSQLの実行計画を確認する
SQL> @?/rdbms/admin/sprepsql
Specify the Begin and End Snapshot Ids
\~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
begin_snapに値を入力してください: 6 ★ 開始IDを指定
end_snapに値を入力してください: 7 ★ 終了IDを指定
End Snapshot Id specified: 7
Specify the old (i.e. pre-10g) Hash Value
\~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
hash_valueに値を入力してください: 2770349870 ★OLD_HASH_VALUEを入力する。SPレポートやv$sql等から取得する。
Hash Value specified is: 2770349870
出力例は以下のとおり。
STATSPACK SQL report for Old Hash Value: 2770349870 Module: JDBC Thin Client
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ----------------
TESTDB 2774447833 testdb 1 12.2.0.1.0 NO oracle12cr2test1
Start Id Start Time End Id End Time Duration(mins)
--------- ------------------- --------- ------------------- --------------
9 23-11月-18 17:48:23 10 23-11月-18 17:50:25 2.03
SQL Statistics
\~~~~~~~~~~~~~~
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in
milliseconds (ms) for Per Execute
% Snap
Statement Total Per Execute Total
--------------- --------------- ------
Buffer Gets: 14,462
14.0 1.68
Disk Reads: 0
0.0
Rows processed: 467
0.5
CPU Time(s/ms): 0
.1
Elapsed Time(s/ms): 0
.3
Sorts: 0
.0
Parse Calls: 1,030
1.0
Invalidations: 0
Version count: 1
Sharable Mem(K): 27
Executions: 1,030
SQL Text
\~~~~~~~~
insert into log_tbl(id, msg1, msg2, insert_time) values(logkey_s
eq.NEXTVAL, :1 , :2 , current_timestamp)
Known Optimizer Plan(s) for this Old Hash Value
\~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows all known Optimizer Plans for this database instance, and the Snap Id's
they were first found in the shared pool. A Plan Hash Value will appear
multiple times if the cost has changed
-> ordered by Snap Id
First First Last Plan
Snap Id Snap Time Active Time Hash Value Cost
--------- --------------- --------------- ------------ ----------
10 23-11月-18 17:5 23-11月-18 17:4 526403976 1
0 9
Plans in shared pool between Begin and End Snap Ids
\~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows the Execution Plans found in the shared pool between the begin and end
snapshots specified. The values for Rows, Bytes and Cost shown below are those
which existed at the time the first-ever snapshot captured this plan - these
values often change over time, and so may not be indicative of current values
-> Rows indicates Cardinality, PHV is Plan Hash Value
-> ordered by Plan Hash Value
--------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
--------------------------------------------------------------------------------
|INSERT STATEMENT |----- 526403976 -----| | | 1 |
|LOAD TABLE CONVENTIONAL |LOG_TBL | | | |
| SEQUENCE |LOGKEY_SEQ | | | |
--------------------------------------------------------------------------------
End of Report
自動でスナップショットを取得する（1時間ごと）
デフォルトでは1時間おきに実行される。
短くても30分、通常はデフォルトの1時間とすることがお勧め。
5分、10分ぐらいにすると余計な負荷がかかるので止めましょう。短い時間で取得しなければ調査できないケースは、statspackではなくv$session等を駆使して調べるべし。
SQL> @?/rdbms/admin/spauto
ジョブの実行間隔を変更する（以下は30分ごと）
ジョブIDを取得
select job, what from dba_jobs;
ジョブIDと実行間隔を指定する。
SQL> execute dbms_job.interval([ジョブID], 'sysdate+(1/48)');
ジョブを削除する場合
SQL> execute dbms_job.remove([ジョブID]);
スナップショットを削除する（全件削除）
全件削除する。
SQL> @?/rdbms/admin/sptrunc.sql
スナップショットを削除する（指定削除）
IDの期間指定で削除する。
SQL> @?/rdbms/admin/sppurge.sql
直近7日分だけ残して削除する。
SQL> execute statspack.purge(7);
期間（日付）指定で削除する。
execute statspack.purge(to_date('2018-11-01'), to_date('2018-12-01'));
ベースラインを設定する
今後の比較のために残しておきたいスナップショットは、ベースラインとしてマークすることで削除できないようになります。
正常動作しているときのスナップショットをベースラインとして残しておけば、性能劣化した場合に比較することができます。
まず、スナップショットのIDを以下のSQLで確認。
select snap_id, to_char(snap_time, 'yyyy-mm-dd hh24:mi:ss') snap_time from stats$snapshot order by snap_id;
SNAP_ID SNAP_TIME
---------- ---------------------------------------------------------
109 2018-11-24 04:23:21
110 2018-11-24 04:24:00
115 2018-11-24 04:25:11
116 2018-11-24 04:28:00
117 2018-11-24 04:29:20
125 2018-11-24 04:32:40
126 2018-11-24 04:34:53
127 2018-11-24 04:39:57
135 2018-11-24 06:46:38
136 2018-11-24 06:49:15
137 2018-11-24 06:56:19
126から127をベースラインに設定する。
SQL> execute statspack.make_baseline(126, 127);
snapshotテーブルのbaselineカラムの値を確認すると"Y"になっている。
select snap_id, to_char(snap_time, 'yyyy-mm-dd hh24:mi:ss') snap_time, baseline from stats$snapshot
SNAP_ID SNAP_TIME BAS
---------- --------------------------------------------------------- ---
110 2018-11-24 04:24:00
115 2018-11-24 04:25:11
116 2018-11-24 04:28:00
117 2018-11-24 04:29:20
125 2018-11-24 04:32:40
126 2018-11-24 04:34:53 Y
127 2018-11-24 04:39:57 Y
135 2018-11-24 06:46:38
136 2018-11-24 06:49:15
137 2018-11-24 06:56:19
138 2018-11-24 07:00:58
ベースラインを解除するには以下を実行します。
SQL> execute statspack.clear_baseline(126, 127);
statspackのアンインストール
connect / as sysdba
SQL> @?/rdbms/admin/spdrop.sql
最後に
プロダクション環境であれば、スナップショットレポートはcronで自動的に生成するようにスクリプトを組んだ方が良いです。また、スナップショットが溜まるとストレージを（多少）圧迫するので、こちらも自動で削除するようにしましょう。
参考
第1回 StatspackとDiagnostics Packの概要と使用方法
https://www.oracle.com/technetwork/jp/articles/index-349908-ja.html
http://otn.oracle.co.jp/skillup/stats_diag/1/
データベース・パフォーマンス・チューニング・ガイドおよびリファレンス
http://otndnld.oracle.co.jp/document/oracle9i/920/generic/server/J06248-02.pdf
9i用。10gからはstatspackの記載が削除されているのでこちらを参照するしかない。
英語だが以下にも最新のドキュメントが格納されている。
$ORACLE_HOME/rdbms/admin/spdoc.txt