LoginSignup
12
16

More than 5 years have passed since last update.

@mkyz08

Oracle(12c) statspackのインストールと使い方

Last updated at Posted at 2018-11-23

はじめに

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

12
16
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
12
16