STATSPACKレポート取得のためのスクリプト

はじめに

Statspackレポートが必要になったときに、毎回DBに接続してspreport.sqlを
実行するのも面倒なので、取得のタイミングと合わせて1時間に1回バッチ処理で
レポートを生成させることにしました。

参考にした記事はSTATSPACKレポートを全部一気に出力するスクリプトです。

この記事を読んだおかげで、spreport.sql は内部で sprepins.sql を呼んでいること、
sprepins.sql 実行時に3つの置換変数 begin_snap、end_snap、report_name の入力が
求められることを理解できましたが、いくつか注意点が見つかりました。

注意点①：SQL*Plusはサイレントモードで起動する

まず、レポート取得用のSTATSREPORT.sql（後述）をキックするバッチファイルですが、
SQL*Plusは-sを付けてサイレントモードで起動するといいです。
こうすることで、後述するstats.sqlの中に不要な文字列が含まれるのを防ぐことができます。

oracle_statspack_report.cmd

(略)
sqlplus -s /nolog @D:\basis_scripts\conf\STATSREPORT.sql
(略)

注意点②：STATSPACKスナップショットのidは1ずつ増えるとは限らない

上記の参考記事ではSTATSPACKスナップショットのidが1ずつ増えることを
前提にしていますが、実際には連続したスナップショットでもidが2増えたり
3増えたりすることがあります。

以上を踏まえると、select文のoffset n rows fetch first n rows onlyを活用して
下記のように書くのが良いかと思います。
なお、ASCIIコードの10は改行なので、chr(10)は改行を意味しています。

STATSREPORT.sql

connect / as sysdba
whenever sqlerror exit sql.sqlcode

set echo off
set feedback off
set heading off
spool D:\oradata\STATS_REPORT\stats.sql

 -- 最新のひとつ前のidを置換変数 begin_snap に代入する

 select
 'define begin_snap='  || SNAP_ID      || chr(10)
 from stats$snapshot order by snap_id desc offset 1 rows fetch first 1 rows only; 

 -- 最新のidを置換変数 end_snap に代入する
 
 select
 'define end_snap='    || SNAP_ID			|| chr(10) ||
 'define report_name=' || 'D:\oradata\STATS_REPORT\'	|| SNAP_ID      || '.lst'  || chr(10) ||
 '@D:\oracle\product\19.3.0\dbhome_1\rdbms\admin\spreport.sql' || chr(10)
 from stats$snapshot order by snap_id desc fetch first 1 rows only;
 
spool off
@D:\oradata\STATS_REPORT\stats.sql

exit

以上を実行すると、下記のstats.sqlが実行されてレポートが取得されるという仕組みです。

stats.sql

-- 空白行はご愛嬌
define begin_snap=17843                                                         

define end_snap=17844                                                           
define report_name=D:\oradata\STATS_REPORT\17844.lst                            
@D:\oracle\product\19.3.0\dbhome_1\rdbms\admin\spreport.sql        

おわりに

なお、上記バッチファイル oracle_statspack_report.cmd は1時間に1回 JP1 でキックし、
7日分たまったら同様に JP1 で削除用ジョブをキックして消しています。

ディスク枯渇の心配もないので安心ですね。