STATSPACKのインストールと使用
STATSPACK関連ドキュメント
公式
- ORACLE_HOME/rdbms/admin/spdoc.txt (一応11g向けにメンテナンスされている)
- Oracle 9i データベース・パフォーマンス・チューニング・ガイドおよびリファレンス (10gから公式の説明はなくなった)
表領域を作成
- STATSPACKが作成するテーブルとかを把握したいのでとりあえず専用表領域を作る。インストーラが推奨として選ぶのは SYSAUX だが。
- 当初から100MB程度は容量をとる。200MB+収集回数で計算するぐらいでよい。
CREATE SMALLFILE TABLESPACE STATSPACK DATAFILE '/oradata/TESTDB01/dbf/statspack.01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
STATSPACKを使うケース
使わなくてよいケース
下記の条件を全て満たす場合はSTATSPACKは不要だろう(10gおよび11gに対して)。要するにAWRをばりばり使えるかなり豪華な構成の場合だけ。ライセンスに関する部分なので詳細は都度各リリースのマニュアルにある『ライセンス情報』を参照。
- Enterprise Edition を使用する
- Diagnostics Pack を購入する
最初から入れておく
運用開始後は変更がなにかと大変になる。最初から入れて試験期間も通して常に運用して実績を確保してしまうのがよい。
Statspackのインストールをしていないシステムも実はあるのではないでしょうか。既に運用を開始しているシステムの場合、性能を分析するためにまずStatspackをインストールしなければならないのですが、システム管理者やお客様などにStatspackをインストールする許可を得なければならなくなります。また、設計書にもその変更内容を記載しなければなりません。Statspackのインストール作業そのものはとても簡単なのですが、いろいろと面倒なことが多く、肝心の性能分析がすぐに始められないなどの問題が生じます。よって、Statspackは忘れずに運用開始前からインストールしておきましょう。
インストール
sqlplus / as sysdba SQL> @?/rdbms/admin/spcreate.sql Choose the PERFSTAT user's password ----------------------------------- Not specifying a password will result in the installation FAILING perfstat_passwordに値を入力してください: PERFSTAT PERFSTAT Choose the Default tablespace for the PERFSTAT user --------------------------------------------------- Below is the list of online tablespaces in this database which can store user data. Specifying the SYSTEM tablespace for the user's default tablespace will result in the installation FAILING, as using SYSTEM for performance data is not supported. Choose the PERFSTAT users's default tablespace. This is the tablespace in which the STATSPACK tables and indexes will be created. TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE ------------------------------ --------- ---------------------------- STATSPACK PERMANENT SYSAUX PERMANENT * Pressing <return> will result in STATSPACK's recommended default tablespace (identified by *) being used. default_tablespaceに値を入力してください: STATSPACK Using tablespace STATSPACK as PERFSTAT default tablespace. Choose the Temporary tablespace for the PERFSTAT user ----------------------------------------------------- Below is the list of online tablespaces in this database which can store temporary data (e.g. for sort workareas). Specifying the SYSTEM tablespace for the user's temporary tablespace will result in the installation FAILING, as using SYSTEM for workareas is not supported. Choose the PERFSTAT user's Temporary tablespace. TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE ------------------------------ --------- -------------------------- TEMP TEMPORARY * Pressing <return> will result in the database's default Temporary tablespace (identified by *) being used. temporary_tablespaceに値を入力してください: TEMP Using tablespace TEMP as PERFSTAT temporary tablespace. ... Creating PERFSTAT user ・・・(中略)・・・ Creating Package STATSPACK... パッケージが作成されました。 エラーはありません。 Creating Package Body STATSPACK... パッケージ本体が作成されました。 エラーはありません。 NOTE: SPCPKG complete. Please check spcpkg.lis for any errors.
確認してみると、STATS$ で始まるテーブルが70程度 STATSPACK 表領域に作成されていた。
アンインストール
SYSで実施する。インストール中にPERFSTATにユーザが切り替わるので、インストール失敗でアンインストールする場合はSYSで再接続すること。
SQL> @?/rdbms/admin/spdrop.sql Dropping old versions (if any) ・・・(中略)・・・ NOTE: SPDUSR complete. Please check spdusr.lis for any errors.
スナップショットをとってみる
レベル指定なしでとる(LEVEL 5になる)
SQL> execute statspack.snap PL/SQLプロシージャが正常に完了しました。
レベル指定でとる(LEVEL 7)
SQL> execute statspack.snap(i_snap_level => 7) PL/SQLプロシージャが正常に完了しました。
レベルと取得できる情報
一覧
| i_snap_level | 基本統計 | アドバイス | SQL統計 | SQL詳細 | セグメント | 親・子ラッチ |
|---|---|---|---|---|---|---|
| Level 0 | ○ | ○ | ||||
| Level 5 | ○ | ○ | ○ | |||
| Level 6 | ○ | ○ | ○ | ○ | ||
| Level 7 | ○ | ○ | ○ | ○ | ○ | |
| Level 10 | ○ | ○ | ○ | ○ | ○ | ○ |
初期化パラメータSTATISTICS_LEVEL=TYPICALまたはALL(BASICでない)かつTIMED_STATISTICS=TRUEが条件
詳細
『プロとしてのSQLチューニング入門(Oracle 現場主義)』より引用。
- Level0以上
- 待機統計
- システム・イベント
- システム統計
- ロールバック・セグメント・データ
- 行キャッシュ
- SGA
- バックグラウンド・イベント
- セッション・イベント
- ロック統計
- バッファ・プール統計
- 親ラッチ統計
- Level5以上
- Level6以上
- Level7以上
- 使用頻度の高いセグメントに関するパフォーマンス・データ
- RAC固有のセグメント・レベルの統計
- Level10以上
- 親ラッチおよび子ラッチの情報
レベル7までのオーバーヘッドは微々たるもので、全体のパフォーマンスに影響を及ぼすことはありません。そのため、インストール時に設定されているデフォルトのスナップショットのレベルは5ですが、Oracle 9i R2以降ではレベル7での取得をおすすめします。
レベル10で収集されるデータは、多くのリソースを消費するため、スナップショット作成の時間が長くなる原因になる可能性があります。そのため、特に必要がない限り、このレベルの使用はおすすめできません。
取得に要した時間
STATS$SNAPSHOT 表の SNAPSHOT_EXEC_TIME_S 列に秒単位(0.01秒まで保持)で取得に要した時間が記録されている。長過ぎる場合にはレベルを下げる事も検討する。
STATSPACKで取得したスナップショットを削除する
期間指定で削除する場合
スナップショットの一覧と削除範囲(下限(lo) - 上限(hi)指定)を求めるプロンプトが表示されるのでそれに沿って削除する。
SQL> @?/rdbms/admin/sppurge.sql Specify the Lo Snap Id and Hi Snap Id range to purge ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ losnapidに値を入力してください: 1 Using 1 for lower bound. hisnapidに値を入力してください: 3 Using 3 for upper bound. Deleting snapshots 1 - 3. Number of Snapshots purged: 3 ~~~~~~~~~~~~~~~~~~~~~~~~~~~ Purge of specified Snapshot range complete.
全件をTRUNCATEで落とす場合(高速)
本当に削除してよいか確認プロンプトが表示されるので [ENTER] で実行(普通[ENTER]はキャンセル扱いだろ(汗))
SQL> @?/rdbms/admin/sptrunc.sql Warning ~~~~~~~ Running sptrunc.sql removes ALL data from Statspack tables. You may wish to export the data before continuing. About to Truncate Statspack Tables ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ If would like to exit WITHOUT truncating the tables, enter any text at the begin_or_exit prompt (e.g. 'exit'), otherwise if you would like to begin the truncate operation, press <return> begin_or_exitに値を入力してください: Entered at the 'begin_or_exit' prompt ... Starting truncate operation ・・・(中略)・・・ ... Truncate operation complete
レポートを出力させてみる
spreport.sql を実行して比較したいスナップショットを 2 つ指定する(その間のスナップショット全てが考慮されるわけではなさそう)。スナップショットの番号以外にファイル名を求められ、そのファイルにもレポートが出力される。
SQL> @?/rdbms/admin/spreport.sql
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ------------------ ----- --------------------
TESTDB01 TESTDB01 1 08 10月 2011 17:04 7
2 08 10月 2011 17:05 7
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に値を入力してください: snap1-2.txt
Using the report name snap1-2.txt
STATSPACK report for
・・・(レポート)・・・
End of Report ( snap1-2.txt )
SQLの実行詳細・実行計画を表示する
spreport.sql で出力したレポート内のSQL情報からハッシュ値(Old Hash Value)を抜き出して、sprepsql.sql で詳細出力する。
spreport.sql で出力したレポートからの抜粋
CPU時間でソートして上位のSQLとして報告されたSQL。ハッシュ値は 1429894190
SQL ordered by Elapsed time for DB: TESTDB01 Instance: TESTDB01 Snaps: 2 -3
-> Total DB Time (s): 18
-> Captured SQL accounts for 94.0% of Total DB Time
-> SQL reported below exceeded 1.0% of Total DB Time
Elapsed Elap per CPU Old
Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
12.20 1 12.20 66.8 10.66 8 1429894190
Module: SQL Developer
SELECT OBJECT_TYPE, OBJECT_NAME, TABLESPACE_NAME FROM DB
A_OBJECTS O LEFT OUTER JOIN DBA_SEGMENTS S ON O.OWNER = S.
OWNER AND O.OBJECT_NAME = S.SEGMENT_NAME AND O.OBJEC
T_TYPE IN ('TABLE', 'INDEX') WHERE O.OWNER = 'PERFSTAT' ORDER
sprepsql.sql で詳細レポート
sprepsql.sql を実行すると spreport.sql を実行した場合と同様に比較するスナップショットを 2 つ指定するよう求められる。spreport.sql で指定したものを指定し、さらにハッシュ値が求められるので、解析対象のSQLのハッシュ値を入力する。spreport.sql と同様にファイル名を求められ、そのファイルにもレポートが出力される。
SQL> @?/rdbms/admin/sprepsql.sql
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ------------------ ----- --------------------
TESTDB01 TESTDB01 1 08 10月 2011 17:04 7
2 08 10月 2011 17:05 7
3 08 10月 2011 17:16 7
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
begin_snapに値を入力してください: 2
Begin Snapshot Id specified: 2
end_snapに値を入力してください: 3
End Snapshot Id specified: 3
Specify the old (i.e. pre-10g) Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
hash_valueに値を入力してください: 1429894190
Hash Value specified is: 1429894190
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_2_3_1429894190. To use this name,
press <return> to continue, otherwise enter an alternative.
report_nameに値を入力してください: 1429894190.txt
Using the report name 1429894190.txt
STATSPACK SQL report for Old Hash Value: 1429894190 Module: SQL Developer
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ----------------
TESTDB01 2628464059 TESTDB01 1 11.2.0.1.0 NO oracle.localdoma
in
Start Id Start Time End Id End Time Duration(mins)
--------- ------------------- --------- ------------------- --------------
2 08-10月-11 17:05:04 3 08-10月-11 17:16:02 10.97
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: 2,781,504 2,781,504.0 77.68
Disk Reads: 8 8.0 1.19
Rows processed: 50 50.0
CPU Time(s/ms): 11 10,661.4
Elapsed Time(s/ms): 12 12,195.6
Sorts: 1 1.0
Parse Calls: 1 1.0
Invalidations: 0
Version count: 1
Sharable Mem(K): 324
Executions: 1
SQL Text
~~~~~~~~
SELECT OBJECT_TYPE, OBJECT_NAME, TABLESPACE_NAME FROM DB
A_OBJECTS O LEFT OUTER JOIN DBA_SEGMENTS S ON O.OWNER = S.
OWNER AND O.OBJECT_NAME = S.SEGMENT_NAME AND O.OBJEC
T_TYPE IN ('TABLE', 'INDEX') WHERE O.OWNER = 'PERFSTAT' ORDER
BY OBJECT_TYPE, OBJECT_NAME
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
--------- ---------------- ---------------- ------------ ----------
3 08-10月-11 17:16 08-10月-11 17:15 3548308502 1973217
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 |
--------------------------------------------------------------------------------
|SELECT STATEMENT |----- 3548308502 ----| | |1973217 |
|SORT ORDER BY | | 1M| 158M|1973217 |
| NESTED LOOPS OUTER | | 1M| 158M|1935327 |
| VIEW |DBA_OBJECTS | 1K| 150K| 64 |
| UNION-ALL | | | | |
| TABLE ACCESS BY INDEX ROWID |SUM$ | 1 | 11 | 1 |
| INDEX UNIQUE SCAN |I_SUM$_1 | 1 | | 0 |
| | | | | |
| ...... | | | | |
| | | | | |
| INDEX RANGE SCAN |I_FILE#_BLOCK# | 1 | | 1 |
--------------------------------------------------------------------------------
End of Report
STATSPACK の内情
V$xxx は STATS$xxx にコピーされる
例えば、STATS$SGAはV$SGAの情報をコピーしている。ただし、当然と言えば当然だが SNAP_ID、DBID、INSTANCE_NUMBERなどスナップショット特有の列が追加されている。
スナップショットの取得履歴は STATS$SNAPSHOT で確認できる
spreport.sql などでも表示されるが、直接確認する場合には STATS$SNAPSHOT テーブルを確認すれば良い。SNAP_IDや取得日時(SNAP_TIME)が直接確認できる。
