kagamihogeの日記

kagamihogeの日記です。

statspackさわる

DB Oracle

とりあえず、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スナップショット採取とレポート作成をシェルスクリプトにまとめたもの。

参考URL