■Oracle_STATSPACKについて

●参考サイト
http://otn.oracle.co.jp/skillup/stats_diag/2/index.html
http://www9.plala.or.jp/okuman/oracle/statspack.html

0.事前準備
(1)STATSPACK取得用表領域作成
SQL> create tablespace STATSPACK datafile '/u01/oradata/ORACLE/STATSPACK.dbf' size 200M;

1.インストール手順

(1) SQL*PLUSにて、SYS 又は INTERNAL USERとしてDBに接続する。
SQL> conn as sydba

(2) 以下のスクリプトを実行する。
　　SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql

(3) PERFSTAT ユーザのPASSWDを指定する。
***************************************************
Specify PERFSTAT password
perfstat_passwordに値を入力してください:
***************************************************

(4) STATS PACKの使用する表と索引を格納する表領域を指定する。→（自動作成）
***************************************************
Specify PERFSTAT user's default tablespace
default_tablespace に値を入力してください: STATSPACK
***************************************************

(5) STATS PACKの使用する一時表領域を指定する。→（自動作成）
***************************************************
Specify PERFSTAT user's temporary tablespace.
temporary_tablespace に値を入力してください: TEMP
***************************************************

※インストール後、エラーが発生していないかどうか以下のログを確認。
（カレントディレクトリに出力される）
・spcpkg.lis
・spctab.lis
・spcusr.lis

2.統計情報の採取方法

(1)STATS PACK用のユーザー（デフォルトならperfstat)でDBに接続する。
　% sqlplus perfstat/*******

(2)「スナップショット」を採取する。
　SQL> execute statspack.snap (i_snap_level => 7)

　統計情報を取得したい間隔にて再度実行する。

●cronによるスナップショット自動取得
　11月26日7時45分に実行する。
******************************************************************************************
%contab -l
45 7 11 26 * /usr/bin/csh statspack.sh
%cat statspack.sh
setenv ORACLE_BASE /opt/oracle
setenv ORACLE_HOME ${ORACLE_BASE}/product/9.2.0
setenv ORACLE_SID ORACLE
setenv ORACLE_DOC ${ORACLE_HOME}/doc
setenv PATH ${ORACLE_HOME}/bin:/usr/ccs/bin:/usr/bin:/etc:/usr/openwin/bin:${PATH}
setenv NLS_LANG Japanese_Japan.JA16SJIS

/usr/bin/echo 'execute statspack.snap (i_snap_level => 7)' | /opt/oracle/product/9.2.0/bin/sqlplus perfstat/orapasswd
******************************************************************************************
※cronで実行する場合、環境変数が設定されていないので、コマンドはフルパスで定義する。
※実行結果はmailにて送信される。
　<エラーのみ送信したい場合>
　* * * * * command 1> /dev/null
　<何が起ころうとも送信しない場合>
　* * * * * command >/dev/null 2>&1

3.統計情報の出力方法

(1)STATS PACK用のユーザーでDBに接続する。
　　% sqlplus perfstat/******

(2)以下のスクリプトを実行する。
　　SQL> @$ORACLE_HOME/rdbms/admin/spreport.sql
******************************************************************************
Snap Snap
Instance DB Name Id Snap Started Level Comment
------------ ------------ ----- ----------------- ----- ----------------------
ORACLE ORACLE 1 05 Nov 2007 17:28 7
2 05 Nov 2007 17:40 7
******************************************************************************
(3) 開始スナップショットIDを入力する。
　　Enter beginning Snap Id: 1

(4) 終了スナップショットIDを入力する。
　　Enter ending Snap Id: 2

(5) レポートのファイル名を指定する。 デフォルトは st_{beginning Snap Id}_{ending Snap Id}.log となる。

(6) カレントディレクトリに指定したファイル名でレポートが出力される。


4.古い統計情報を削除する方法

(1)STATS PACK用のユーザーでDBに接続する。
　　% sqlplus perfstat/******

(2)以下のスクリプトを実行する。
　　SQL> @$ORACLE_HOME/rdbms/admin/sppurge.sql

(3)削除開始SNAPIDを指定
***********************************************
losnapidに値を入力してください: 1
Using 1 for lower bound.
***********************************************

(4)削除終了SnapIDを指定
***********************************************
hisnapidに値を入力してください: 2
Using 2 for upper bound.
***********************************************

(5)削除が実行される。
******************************************************************************
Deleting snapshots 1 - 2.

Purge of specified Snapshot range complete. If you wish to ROLLBACK
the purge, it is still possible to do so. Exitting from SQL*Plus will
automatically commit the purge.
******************************************************************************


3.実行計画の取得
STATSPACKにて取得したSQL文のhash_value値より、同SQL文の自行計画を取得する。

(1)STATS PACK用のユーザーでDBに接続する。
　　% sqlplus perfstat/******

(2)以下のスクリプトを実行する。
SQL> @$ORACLE_HOME/rdbms/admin/sprepsql.sql

(3)STATSPACK取得情報が表示される。
******************************************************************************
Snap Snap
Instance DB Name Id Snap Started Level Comment
------------ ------------ ----- ----------------- ----- ----------------------
NTTETF NTTETF 3 06 Nov 2007 10:02 6
4 06 Nov 2007 10:04 6
5 06 Nov 2007 10:17 6
6 06 Nov 2007 10:20 6
7 06 Nov 2007 10:23 6
8 07 Nov 2007 12:16 7
9 07 Nov 2007 12:17 7
******************************************************************************

(4)実行計画を取得したいSQLが含まれるスナップショットを選択する。
******************************************************************************
begin_snapに値を入力してください: 8
Begin Snapshot Id specified: 8
******************************************************************************
******************************************************************************
end_snapに値を入力してください: 9
End Snapshot Id specified: 9
******************************************************************************

(4)実行計画を取得したいSQLのHash Value値を入力する。
******************************************************************************
hash_valueに値を入力してください: 4242159307
Hash Value specified is: 4242159307
******************************************************************************

(5)出力するレポートの名前を入力する。
******************************************************************************
The default report file name is sp_8_9_4242159307. To use this name,
press to continue, otherwise enter an alternative.
report_nameに値を入力してください: sql
******************************************************************************