Dさんの悩み

システムの「心臓」こと、Oracle Databaseに日々携わるたろーちゃん。
ある日、隣の部署のDさんがたろーちゃんの元に訪れました。
何かあったのでしょうか？

Dさん「たろーちゃん、ちょっと時間あるかな？」
たろー「あ、先輩。お疲れ様です。何かあったんですか？」
Dさん「この前、診てもらったシステムで Statspack を導入したんだけど、過去のスナップショットを
削除しているにも関わらず、領域が肥大化して困ってるんだ。」
※Dさんのシステムについては、以前の記事『UNDO障害』を参照してください。

たろー「Statspack ですか。 スナップショット の取得方法や削除方法の詳細を教えて下さい。」
Dさん「このシステムでは、３０分おきに スナップショット が取られていて、
午前０時１５分に直近２週間分を残して、過去の スナップショット を消すようにしてるんだ。」
たろー「はい。」
Dさん「取得方法はこんな感じだ。セグメント統計情報まで取得したいので、
デフォルトの レベル５ ではなく、レベル７ で取得している。」
　

SQL> execute statspack.snap(i_snap_level=>7);

Dさん「削除は Oracle Database に用意されている sppurge.sql を使用している。
これを使って、直近２週間分は残して削除しているんだ。」
　

variable lo_snap number;
variable hi_snap number;

begin
    select min(SNAP_ID) into :lo_snap from stats$snapshot where trunc(SNAP_TIME) <= trunc(sysdate-14) ;
    select max(SNAP_ID) into :hi_snap from stats$snapshot where trunc(SNAP_TIME) <= trunc(sysdate-14) ;
end;
/

column lo_snap_alias new_value lo_snap
select :lo_snap lo_snap_alias from dual;

column hi_snap_alias new_value hi_snap
select :hi_snap hi_snap_alias from dual;

define losnapid = &&lo_snap;
define hisnapid = &&hi_snap;

@?/rdbms/admin/sppurge.sql

Dさん「調べたところ、スナップショット 自体はちゃんと消えてるんだが、
スナップショットを格納している表領域の使用率は減らず、徐々に増えてきているんだ。」
たろー「なるほど・・・。」
Dさん「内訳を見てみると、stats$sqltext、stats$sql_planといったテーブルが
大きな割合を占めてるんだけど、これらを SHRINK してもいいものかな？」
たろー「いや、サポートされないと思うので、それは辞めたほうがいいですよ。」
Dさん「だよなぁ・・・。このままだと、表領域が溢れてしまう。なんとかならないかな？」

解決方法

たろー「先輩は、sppurge.sql の中身って見たことありますか？」
Dさん「sppurge.sql の中身？？」
たろー「そうです。」
Dさん「変数の受け渡しの為に少しだけ見たことあるが、詳しく見たことはない。
きっとPL/SQLの塊だろう？俺が見ても分からないよ。」
たろー「実はそこに鍵があるんです。」
Dさん「なんだって❓❗」

---

たろーちゃんは、sppurge.sql のバックアップを取得後、テキストエディタで sppurge.sql を開きました。
　

begin
    :lo_snap :=  &&losnapid;
    :hi_snap :=  &&hisnapid;
    :snapshots_purged := statspack.purge( i_begin_snap      => :lo_snap
                                        , i_end_snap        => :hi_snap
                                        , i_snap_range      => true
                                        , i_extended_purge  => false
                                        , i_dbid            => :dbid
                                        , i_instance_number => :inst_num);
end;

たろー「このように、sppurge.sql では statspack.purgeというPL/SQLプロシージャを呼び出して、
スナップショットを削除してるんです。」
Dさん「ほう。」
たろー「statspack.purgeプロシージャの第４引数に注目して下さい。」
　

＜再掲＞
  begin
    :lo_snap :=  &&losnapid;
    :hi_snap :=  &&hisnapid;
    :snapshots_purged := statspack.purge( i_begin_snap      => :lo_snap
                                        , i_end_snap        => :hi_snap
                                        , i_snap_range      => true
                                        , i_extended_purge  => false
                                        , i_dbid            => :dbid
                                        , i_instance_number => :inst_num);
  end;

Dさん「i_extended_purge というやつか？FALSEになっているな。」
たろー「そうです。この引数をTRUEにしてやれば、不要になった領域が解放されるんです。」
Dさん「なんだって❓❗」
たろー「折角なんで、今、直しちゃいますね。」
　

  begin
    :lo_snap :=  &&losnapid;
    :hi_snap :=  &&hisnapid;
    :snapshots_purged := statspack.purge( i_begin_snap      => :lo_snap
                                        , i_end_snap        => :hi_snap
                                        , i_snap_range      => true
                                        , i_extended_purge  => true
                                        , i_dbid            => :dbid
                                        , i_instance_number => :inst_num);
  end;

たろー「これで次回実行時に、解放可能な領域が全て解放されますよ。
但し、初回は時間が掛かると思うので、一度、業務のオフピーク時間帯に手動で実行したほうがいいと思います。
その時、30分おきに実行されているスナップショット取得の処理は停止しておいたほうがいいですよ。
解放と取得の同時実行はまずいですからね。」
Dさん「しかし大丈夫なのか？ Oracle Database に用意されているスクリプトを勝手に変更したりして。」
たろー「原則はNGです。ただ、これはMy Oracle Support のドキュメントID 1742323.1で
紹介されている拡張パージという方法なので、問題ないですよ。」
Dさん「わかった！ありがとう、たろーちゃん❗」

---

こうしてDさんのシステムでは、Statspack の領域不足に悩まされることは無くなりました。

今回の「心臓外科医の術式」いかがだったでしょうか？
削除した筈の Statspack  スナップショット の領域が解放されずに困っているDBAの方もいらっしゃるのではないでしょうか？
そんな時は今回ご紹介した方法をお試し下さい。

次回も頑張りますので、応援よろしくお願い致します。