Statspackのインストール方法と設定内容について

はじめに

Statspackは、ある2時点で取得したOracleが行った処理の内部統計情報(スナップショット)の差分を元に、その間のパフォーマンス統計データをレポートとして出力できるツールです。

Enterprise Edition(EE) + Diagnostics Pack、Tuning Packオプションが利用できる環境であれば、Enterprise ManagerやAWSのレポートなどで、詳細なパフォーマンス情報および改善の為のアドバイスを得る事ができますが、これらのツールを利用できないStandard Edition(SE2)環境などでは、Statspackレポートが、Oracleのパフォーマンス状況を調査できる、(ほぼ)唯一のツールになります。

よって、SE2環境(特に本番環境)では、Statspackを必ずインストールし、設定しておく事をお勧めします。
本記事では、Statspackのインストール方法、設定方法についてまず記述します。

Statspackのインストール

Oracleをインストールし、データベースを作成しただけではStatspackは利用できないため、追加でインストールが必要です。
以下の手順でインストールを行います。

Statspack用の表領域作成

Statspackのスナップショットで取得した統計情報等を格納する表領域を作成します。
SYSAUX表領域に格納する事もできますが、環境によっては(SQLなどのパフォーマンスがあまりよろしくない環境など)、データ量が多くなり、表領域を圧迫するため、Statspack用の表領域を作成するのが一般的です。
以下の例では、PERFSTATという名前の表領域を作成しています。

SQL> create tablespace perfstat datafile ‘/ORADATA/ORCL/ORCLPDB/PERFSTAT01.DBF’ size 1000M autoextend on next 100M maxsize unlimited;

Statspackのインストール

SQL*PlusでSYSユーザーでログインし、以下のスクリプトを実行します。

SQL> @?/rdbms/admin/spcreate

このスクリプトを実行すると、プロンプトでいくつか入力を求められるので、以下のように入力します。

• Statspack用のOracleユーザー「PERFSTAT」のパスワードを入力します。

Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

perfstat_passwordに値を入力してください: ********

• PERFSTATユーザーのデフォルト表領域名を入力します。ここでは、あらかじめStatspack用に作成した表領域「PERFSTAT」を指定します。

Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.

default_tablespaceに値を入力してください: PERFSTAT

Using tablespace PERFSTAT as PERFSTAT default tablespace.

• PERFSTATユーザーの一時表領域名を入力します。この環境の一時表領域名は「TEMP」なので、「TEMP」を指定します。

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.

上記3つについて入力し、Enterキーを押下すると、PERFSTATユーザーとStatspack用のスキーマオブジェクトが作成され、Statspackがインストールされます。

Statspackのスナップショット

Statspackでレポートを出力する為には、最低2つのスナップショットの取得(取得時点のデータベースの統計情報)が必要です。

レポートでは、任意の2つの時点のスナップショットを指定し、それらの差分から該当時間帯のパフォーマンス統計情報を出力します。

運用時には、定期的な間隔でスナップショットを取得しておき、パフォーマンス問題調査時等に、確認したい時間帯のスナップショットを2つ選択してレポートを出力し、調査に用いるといった運用が一般的です。
なお、Statspack関連の操作は、基本的に「PERFSTAT」ユーザーにログインして行います。

※情報取得には、初期化パラメータstatistics_level = TYPICAL または ALL 、かつtimed_statistics = TRUEである事が必要です。

スナップショットのsnap_level

スナップショット取得時に収集する情報のレベル(i_snap_level)を指定する事が可能です。
レベルが高くなるほど、より詳細なパフォーマンス統計情報が取得可能となります。
レベルによる取得データの違いについて、以下の表に示します。

レベル/取得データ	基本統計	アドバイス	SQL統計	SQL詳細	セグメント	ラッチ
Level 0	〇	〇
Level 5	〇	〇	〇
Level 6	〇	〇	〇	〇
Level 7	〇	〇	〇	〇	〇
Level 10	〇	〇	〇	〇	〇	〇

スナップショットのデフォルトのスナップレベルは5ですが、パフォーマンス調査時には、SQL詳細情報やセグメント情報が必要となる事が多い為、スナップレベルを7にして運用する事をお勧めします。

スナップレベルを変更するには、SQL*PlusでPERFSTATユーザーにログインし、以下のプロシージャを実行します。

 SQL> exec statspack.modify_statspack_parameter(i_snap_level=>7)

このコマンド実行後に取得されたスナップショットは、全てレベル7の情報が取得されるようになります。

なお、レベル10については、情報取得にサーバーリソースを大量に消費する為、Oracleサポートから指示があった時にのみ指定し、通常運用時には使わないようにします。

スナップショットの手動取得

SQL*PlusでPERFSTATユーザーにログインし、statspack.snapプロシージャを実行する事で、手動でスナップショットを取得する事ができます。

SQL> exec statspack.snap

上記は、手動でスナップショットを取得していますが、運用時には、OracleのJOBおよびスケジューラーで定期的にスナップショットを取得するよう設定します。

なお、スナップショットの取得間隔は、1時間程度にするのが一般的です。あまりに短い間隔だと、Oracleへの負荷が高くなる可能性があり、逆に間隔が長いと、本当に問題があるSQLを検知しにくくなります。

定常的には、1時間間隔でスナップショットを取得しておき、パフォーマンス悪化時に、もう少し短い間隔でスナップショットを取得して原因を特定したいなどの場合は、必要に応じて手動でスナップショットを取得するようにします。

※ただし、スナップショットの取得には、それなりに負荷がかかるため、負荷が落ち着いているタイミングで取得するようにします。

スナップショットを自動で定期的に取得する

Statspackのスナップショットを自動で取得するには、SQL*PlusでSYSユーザーにログインし、以下のスクリプトを実行します。

SQL> @?/rdbms/admin/spauto

このスクリプトを実行すると、毎時0分(1時間毎に)にスナップショットが自動で取得されるようになります。

なお、このスクリプトを実行すると、OracleのJOBとして登録され、画面に登録したJOBのJOBIDが表示されます。

※スナップショットの取得間隔の変更や、JOBを削除する際には、このJOBIDが必要となります。

後から確認する場合は、以下のSQLを実行し、whatの値が「statspack.snap;」となっているJOB(JOBID)を確認します。

SQL> select job,what,next_date,next_sec from user_jobs order by job;

spautoスクリプトで作成したJOBのスナップショット取得間隔を変更する

Statspackのスナップショットの取得間隔を変更するには、SQL*PlusでSYSユーザーにログインし、以下のプロシージャを実行します。※この例は30分間隔に変更する例

SQL> exec dbms_job.interval([JOBID], ‘SYSDATE-1/48’)

※上記プロシージャ実行時に引数に渡すJOBIDは、spautoスクリプトで作成されたOracleのJOBのJOBID

spautoスクリプトで作成したJOBを削除する

スナップショット取得JOBを削除する場合は、以下のプロシージャを実行します。

SQL> exec dbms_job.remove([JOBID])

※上記プロシージャ実行時に引数に渡すJOBIDは、spautoスクリプトで作成されたOracleのJOBのJOBID

spautoスクリプト以外で、スナップショットを自動で定期的に取得する

spauto.sqlで設定する方法は、簡単なのが良い点ですが、スナップショットの実行時間が毎時0分になっているため、AWRが動作している場合(AWRの機能を利用していなくても、AWR用のスナップショットが毎時0分に取得されている)、スナップショット取得処理がバッティングしてしまい、負荷が集中してしまう恐れがあります。

その場合、毎時0分以外の時間にスナップショットを取得するようにします。

以下は、毎時15分にスナップショットレベル7でスナップショットを取得し、14日分のスナップショットを保持する例です。

begin
  dbms_scheduler.create_program(
    program_name => 'PERFSTAT.SNAP_PROGRAM',
    program_type => 'PLSQL_BLOCK',
    program_action => 'BEGIN' ||
      '  PERFSTAT.STATSPACK.PURGE(I_PURGE_BEFORE_DATE=>SYSDATE-14);' ||
      '  PERFSTAT.STATSPACK.SNAP(I_SNAP_LEVEL => 7);' ||
      'END;',
    enabled => FALSE
  ); 

  dbms_scheduler.enable('PERFSTAT.SNAP_PROGRAM');

  dbms_scheduler.create_schedule(
    schedule_name => 'PERFSTAT.SNAP_SCHEDULE',
    start_date => systimestamp,
    repeat_interval => 'FREQ=HOURLY;BYMINUTE=15'
  );

  dbms_scheduler.create_job(
    job_name => 'PERFSTAT.SNAP_JOB',
    program_name => 'PERFSTAT.SNAP_PROGRAM',
    schedule_name => 'PERFSTAT.SNAP_SCHEDULE',
    enabled => FALSE,
    auto_drop => FALSE
  );

  dbms_scheduler.enable('PERFSTAT.SNAP_JOB');
end;
/

JOB削除の際は、以下を実行します。

begin
  dbms_scheduler.drop_job( job_name => 'PERFSTAT.SNAP_JOB' );
  dbms_scheduler.drop_program( program_name => 'PERFSTAT.SNAP_PROGRAM' );
  dbms_scheduler.drop_schedule( schedule_name => 'PERFSTAT.SNAP_SCHEDULE' );
end;
/

取得したスナップショットを定期的に削除する

Statspackのスナップショットは、取得する毎にstats$で始まる専用テーブルにデータが追加されていきます。

その為、スナップショットを取得したままにしておくと、表領域を圧迫し、場合によっては、Oracleデータの追加、更新ができなくなる可能性があります。

それを防ぐために、運用時には、定期的にスナップショットを削除するようにします。

※先程紹介したスクリプトで、STATSPACK.PURGE(I_PURGE_BEFORE_DATE=>SYSDATE-14)としている箇所が該当部分)

ただし、この削除方法だけでは、全てのテーブルのデータが削除されない仕様(負荷低減の為)となっているため、定期的に削除をしていても表領域の利用率が増えていく場合があります。

その為、以下のようなジョブを、負荷が小さい時間帯に、定期的に実行する必要があります。
※PURGE時のオプションに「i_extended_purge=>TRUE」を追加する事で、全てのデータが削除される。

begin
  dbms_scheduler.create_program(
    program_name => 'PERFSTAT.PURGE_SNAP_PROGRAM',
    program_type => 'PLSQL_BLOCK',
    program_action => 'BEGIN' ||
      ' PERFSTAT.STATSPACK.PURGE(I_PURGE_BEFORE_DATE=>SYSDATE-14, i_extended_purge=>TRUE);' ||
      'END;',
    enabled => FALSE
  );

  dbms_scheduler.enable('PERFSTAT.PURGE_SNAP_PROGRAM');

  -- この例では毎日0:30にデータ削除。※負荷がかかるので、システム負荷が少ない時間帯を指定する。
  dbms_scheduler.create_schedule(
    schedule_name => 'PERFSTAT.PURGE_SNAP_SCHEDULE',
    start_date => systimestamp,
    repeat_interval => 'FREQ=DAILY;BYHOUR=0;BYMINUTE=30'
  );

  dbms_scheduler.create_job(
    job_name => 'PERFSTAT.PURGE_SNAP_JOB',
    program_name => 'PERFSTAT.PURGE_SNAP_PROGRAM',
    schedule_name => 'PERFSTAT.PURGE_SNAP_SCHEDULE',
    enabled => FALSE,
    auto_drop => FALSE
  );

  dbms_scheduler.enable('PERFSTAT.PURGE_SNAP_JOB');
end;
/

取得スナップショットの一覧表示

SQL*PlusでPERFSTATユーザーにログインし、以下のSQLを実行する事で取得したスナップショットの一覧情報が確認できます。
Statspackレポートを出力する際に、レポート出力期間の指定に利用します(レポート出力時にも同様の情報がリストされます)。

SQL> select snap_id,to_char(snap_time,'YYYY/MM/DD HH24:MI:SS') as snap_time,snap_level
  2 from stats$snapshot
  3 order by snap_time
  4 /

   SNAP_ID SNAP_TIME                              SNAP_LEVEL
---------- -------------------------------------- ----------
         1 2020/05/01 17:22:13                             7
         2 2020/05/01 17:26:09                             7

Statspackレポート

レポートを出力するには、SQL*PlusでPERFSTATユーザーにログインし、以下のスクリプトを実行します。

SQL> @?/rdbms/admin/spreport

このスクリプトを実行すると、プロンプトで入力を求められるので、以下を入力します。

・・・・
(画面に、snap_idの一覧が表示される)
・・・・
begin_snapに値を入力してください:  レポート期間の起点となるsnap_idを指定。
・・・・
end_snapに値を入力してください: レポート期間の終点となるsnap_idを指定。
・・・・
report_nameに値を入力してください:  出力するレポートファイル名を指定。

入力し、Enterキーを押下すると、レポート内容が指定したレポートファイル名に出力されます。
レポートファイル名のデフォルトは、「sp_begin_snap_idの値_end_snap_idの値.lst」です。
(例)sp_15_16.lst

※Statspackレポートの見方については、別記事に記載します。

Statspackレポートの出力結果を調整する

Statspackレポートには、メトリクス毎に、問題がありそうなSQLの情報が出力されますが、ここに載るSQLは、メトリクス毎に設定されている閾値を超えたものだけです。
メトリクスと閾値のデフォルト値を以下に記載します。

メトリクス	パラメータ名	閾値のデフォルト値
SQL実行回数	i_executions_th	100
ディスク読み込み回数	i_disk_reads_th	1,000
パース発生回数	i_parse_calls_th	1,000
バッファ読み込み回数	i_buffer_gets_th	10,000
共有メモリの合計使用量	i_sharable_mem_th	1,048,576
子カーソルの数	i_version_count_th	20

メトリクスの閾値の値は、SQL*PlusでPERFSTATユーザーにログインし、以下のプロシージャにメトリクスに対応するパラメータと閾値の値を引数として渡すことで変更が可能です。
例えば、ディスク読み込み回数の閾値を500に変更する場合は、以下を実行します。

SQL> exec statspack.modify_parameter(i_disk_reads_th => 500)

閾値の値が変更されたかは、stats$statspack_parameterを参照する事で確認可能です。ディスク読み込み回数の閾値を確認する場合は、以下のようになります。

SQL> select disk_reads_th from stats$statspack_parameter;

Statspackレポートに出力されるSQL文の行数を変更する

Statspackレポートに出力されるSQLは、デフォルトでは4行分しか表示されない為、SQL文が途切れて全文が確認できない場合があります。
この表示行数を変更するには、以下のファイル内に記載されている、num_rows_per_hashパラメータの値を変更します。

編集対象ファイル：$ORACLE_HOME/rdbms/admin/sprepcon.sql

例) SQLの表示行数を4行から100行に変更する
 (変更前) define num_rows_per_hash = 4;
 (変更後) define num_rows_per_hash = 100;

※パラメータ変更後、レポートを出力すると、変更が反映されます。

ベースラインを設定する

パフォーマンス悪化時に、比較の為にスナップショットを残しておきたい場合は、ベースラインとしてマークしておく事で、削除できないようになります。

PERFSTATユーザーで、stats$snapshotを参照して、スナップショットの一覧を確認し、以下のプロシージャを実行する事でベースラインに設定する事ができます。
※以下の例は、スナップID111と112をベースラインに設定。

SQL> exec statspack.make_baseline(111,112)

ベースラインに設定すると、stats$snapshotのbaseline列の値が「Y」になります。また、ベースラインを解除するには、以下のプロシージャを実行します。

SQL> exec statspack.clear_baseline(111,112)

スナップショットの全件削除とStatspackのアンインストール

スナップショットを全件削除する場合は、SQL*PlusでSYSユーザーにログインし、以下のスクリプトを実行します。

SQL> @?/rdbms/admin/sptrunc

Statspackをアンインストールする場合は、SYSユーザーで以下のスクリプトを実行します。

SQL> @?/rdbms/admin/spdrop

おわりに

Statspackのインストール方法、知っておくと便利な設定方法について記載してみました。
冒頭でも書きましたが、SE2環境などでは、Statspackレポートが、Oracleのパフォーマンス状況を調査できる、(ほぼ)唯一のツールになります。
よって、SE2環境(特に本番環境)では、Statspackを必ずインストールし、設定しておくようにしましょう。

Statspackレポートの見方についても、別記事で記載したいと思います。