とりあえず、stats$ナントカ表とかTop 5 Timed Eventsとかのレポートを見れるようになるところまで。
なお、STATSPACK 設定手順 を見ながらやりました。
環境
手順
インストール
SQL*PlusでSYSでログインする。
$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on 金 9月 20 13:59:06 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production に接続されました。 SQL>
以下のスクリプトを実行する。Oracleインストールディレクトリ下のスクリプトを実行するので、SQL DeveloperでなくOracleインストールしたマシンにSSHとかで接続してからSQL*Plus実行することになると思う。
途中、三回入力を求められる。
@$ORACLE_HOME/rdbms/admin/spcreate.sql （中略） Choose the PERFSTAT user's password ----------------------------------- Not specifying a password will result in the installation FAILING perfstat_passwordに値を入力してください: xxxx xxxx 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 ------------------------------ --------- ---------------------------- SYSAUX PERMANENT * USERS PERMANENT Pressing <return> will result in STATSPACK's recommended default tablespace (identified by *) being used. default_tablespaceに値を入力してください: Using tablespace SYSAUX 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に値を入力してください: Using tablespace TEMP as PERFSTAT temporary tablespace. ... Creating PERFSTAT user ... Installing required packages ... Creating views ... Granting privileges （中略） Creating Package STATSPACK... パッケージが作成されました。 エラーはありません。 Creating Package Body STATSPACK... パッケージ本体が作成されました。 エラーはありません。 NOTE: SPCPKG complete. Please check spcpkg.lis for any errors.
PERFSTATというユーザが作成されるので、そのパスワードを入力する。なお、このスキーマにstats$とかが作られる。
perfstat_passwordに値を入力してください: xxxx
PERFSTATの表領域を選択する。デフォルトでよいなら空で良い。この環境の場合、SYSAUXになる。
default_tablespaceに値を入力してください:
一時表領域を選択する。デフォルトでよいなら空で良い。この環境の場合、TEMPになる。
temporary_tablespaceに値を入力してください:
この状態でPERFSTATにログインし、下記のクエリを実行してみる。中身はカラだがstatspackのテーブルが作成されていることが分かる。
select * from stats$sql_summary;
統計情報の採取方法
statspackのスナップショットを採取する。これで採取したものが、statspackの一単位となる。
statspackのユーザでOracleにログインする。デフォルトならperfstatで、パスワードは一個上のスクリプトspcreate.sqlの実行中に自分で指定したもの。
$ sqlplus perfstat/xxxx （中略） SQL>
スナップショットを採取する。
SQL> execute statspack.snap PL/SQLプロシージャが正常に完了しました。
この状態でstats$の各表を見てみると、色々な分析データが追加されていることが分かる。
select * from stats$sql_summary;
統計情報のレポート作成
スナップショットの開始と終了を指定して、サマリーのレポートを作成できる。
とりあえず、サンプルのためにスナップショットをもう一回取る。これは単に、スナップショット番号が開始と終了で1つ以上離れていないと実行できないってだけのこと。
$ sqlplus perfstat/a （中略） SQL> execute statspack.snap PL/SQLプロシージャが正常に完了しました。
レポートを作成する。
途中、三回入力を求められる。
SQL> @$ORACLE_HOME/rdbms/admin/spreport.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2706909277 XE 1 XE
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
2706909277 1 XE XE kagamihogex6
1
Using 2706909277 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------XE XE 1 20 9月 2013 14:2 5
8
2 20 9月 2013 14:3 5
9
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に値を入力してください:
Using the report name sp_1_2
STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
2706909277 XE 1 20-9月 -13 11:3 11.2.0.2.0 NO
2
Host Name Platform CPUs Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
kagamihogex61 Linux x86 64-bit 2 2 1 .9
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 1 20-9月 -13 14:28:37 25 1.6
End Snap: 2 20-9月 -13 14:39:04 26 1.6
Elapsed: 10.45 (mins) Av Act Sess: 0.0
DB time: 0.26 (mins) DB CPU: 0.25 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 92M Std Block Size: 8K
Shared Pool: 148M Log Buffer: 4,004K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 0.0 3.9 0.00 0.00
DB CPU(s): 0.0 3.8 0.00 0.00
Redo size: 1,614.6 253,093.0
Logical reads: 167.4 26,244.0
Block changes: 4.0 631.3
Physical reads: 12.4 1,946.3
Physical writes: 0.3 45.0
User calls: 119.8 18,782.0
Parses: 2.3 367.0
Hard parses: 0.4 66.5
W/A MB processed: 0.1 10.1
Logons: 0.0 3.3
Executes: 5.9 931.8
Rollbacks: 0.0 0.0
Transactions: 0.0
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.77 Optimal W/A Exec %: 100.00
Library Hit %: 84.23 Soft Parse %: 81.88
Execute to Parse %: 60.61 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 92.22 % Non-Parse CPU: 66.99
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 88.11 88.16
% SQL with executions>1: 76.55 74.95
% Memory for SQL w/exec>1: 67.69 75.89
（以下略）スナップショット開始番号を入力する。
begin_snapに値を入力してください: 1
スナップショット終了番号を入力する。
end_snapに値を入力してください: 2
出力レポートのファイル名を入力する。デフォルトなら空で良い。その場合、sp_{begin_snap}_{end_snap}.lstとなる。
report_nameに値を入力してください:
上の方に実行ログを載せているけど、STATSPACK report forの後にサマリのレポートが表示されていく。また、sp_1_2.lstにも出力されている。
レポートファイルを見ると、statspackの分析例で比較的良く見るInstance Efficiency IndicatorsとかTop 5 Timed Eventsとかがあるのが分かる。
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.77 Optimal W/A Exec %: 100.00
Library Hit %: 84.23 Soft Parse %: 81.88
Execute to Parse %: 60.61 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 92.22 % Non-Parse CPU: 66.99
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 88.11 88.16
% SQL with executions>1: 76.55 74.95
% Memory for SQL w/exec>1: 67.69 75.89
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 7 48.6
control file parallel write 219 5 22 33.4
direct path read 135 1 6 5.3
db file async I/O submit 5 1 109 3.7
log file parallel write 18 0 26 3.2
おまけ
Linux - Oracleのstatspack取るだけのシェルスクリプト - Qiita [キータ]
このエントリで書いたstatspackスナップショット採取とレポート作成をシェルスクリプトにまとめたもの。
