理系学生日記

おまえはいつまで学生気分なのか

Oracle の統計情報更新ジョブの起動時刻を移動させる

Oracle は 10 g 以降、基本的には CBO (Cost Base Optimizer) で実行計画を定めるようになる。では、何を基準としてコストを図るのかというとそれは統計情報で、Oracle のデフォルトだと、統計情報収集ジョブが日夜統計情報を収集・更新するという動作になっている。
ところが、この統計情報収集ジョブがサーバの I/O 負荷等を増加させ、本来高速で処理すべきオンライン処理等を遅延させる原因になることがある。処理高速化のために統計情報を取得しているのに、その統計情報取得のために処理が遅延するみたいな話になると、もはや何のためにこれやってるんだっけという状況になるし目もあてられない。

Oracle のジョブ実行

Oracle 上で登録されているジョブは、DBA_SCHEDULER_JOBS というテーブルに保存されていて、そこを参照すると、何ていうジョブがどういうスケジュールで動くのかというのが把握できるようになっている。

SQL> select JOB_NAME ,SCHEDULE_NAME from DBA_SCHEDULER_JOBS;

JOB_NAME                       SCHEDULE_NAME
------------------------------ ----------------------------------------
AUTO_SPACE_ADVISOR_JOB         MAINTENANCE_WINDOW_GROUP
GATHER_STATS_JOB               MAINTENANCE_WINDOW_GROUP
FGR$AUTOPURGE_JOB
PURGE_LOG                      DAILY_PURGE_SCHEDULE

統計情報の収集ジョブは "GATHER_STATS_JOB" という名前なので、ここでいうとスケジュールは MAINTENANCE_WINDOW_GROUP というウィンドウグループで設定されていることがわかる。

ウィンドウグループとは何なのかというと、ウィンドウをグループ化したもの。ではウィンドウとは何かというと、ざっくり言えば個々のスケジュールに相当する。どの頻度で(毎時なのか日次なのか月次なのか)何時何分に起動するのか、何時間の処理を許すのかといったものを定めるのがそのウィンドウになる。ウィンドウグループはそれをグループ化したものになる。
例えば、

  • 平日はオンラインサービスが終了する 22 時に起動を開始させて、朝からはサービスが始まるから 8 時間で実行を打ち切ろう
  • 週末はメンテナンス時間だから朝 6 時に開始すればいいだろ

というように平日と週末でスケジュールを分けたいときは、それぞれを「スケジュールウィンドウ」として登録し、平日用と休日用のスケジュールウィンドウをまとめたスケジュールウィンドウグループを構成、それを各ジョブに割り当てれば良い。

MAINTENANCE_WINDOW_GROUP は実際にそういう風にデフォルトで構成されていて、それは DBA_SCHEDULER_WINGROUP_MEMBERS を参照すれば分かる。

SQL> select WINDOW_GROUP_NAME ,WINDOW_NAME from DBA_SCHEDULER_WINGROUP_MEMBERS;

WINDOW_GROUP_NAME              WINDOW_NAME
------------------------------ --------------------
MAINTENANCE_WINDOW_GROUP       WEEKNIGHT_WINDOW
MAINTENANCE_WINDOW_GROUP       WEEKEND_WINDOW

WEEKNIGHT_WINDOW、WEEKEND_WINDOW がそれぞれ平日用、週末用のスケジュールになっている。個々のスケジュールを参照するには、DBA_SCHEDULER_WINDOWS を見る。

SQL> select WINDOW_NAME, REPEAT_INTERVAL, DURATION from DBA_SCHEDULER_WINDOWS;

WINDOW_NAME          REPEAT_INTERVAL                                                             DURATION
-------------------- --------------------------------------------------------------------------- --------------------
WEEKNIGHT_WINDOW     freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0       +000 08:00:00
WEEKEND_WINDOW       freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0                         +002 00:00:00

REPEAT_INTERVAL が起動時刻や頻度を設定する文字列でフォーマットは分かりづらいがドキュメント化されている。例えば上記設定においては、WEEKNIGHT_WINDOW は、月曜から金曜の 22:00:00 に日次起動される。
DURATION はウィンドウの幅を規定するもので、上記設定を例にとると、WEEKNIGHT_WINDOW の場合は、8 時間の実行が許されている。つまり、ウィンドウは 22 時から 6 時までの幅という設定になっている。ジョブの実行がこの幅を越える場合の振舞いは、主にジョブの stop_on_window_close 属性によって決まり、これが TRUE だと当該ジョブはその時点で停止する。統計情報更新ジョブの stop_on_window_close は TRUE 設定がデフォルト。

WEEKEND_WINDOW は、REPEAT_INTERVAL の設定上は土曜の 0 時起動の設定だが、金曜の 22 時〜土曜の 6 時は WEEKNIGHT_WINDOW のウィンドウが存在している。Oracle が開けるウィンドウは 1 つだけのため、WEEKEND_WINDOW のウィンドウが実際に開くのは土曜の朝 6 時、つまり土曜日の起動時刻は実際のところ 朝 6 時となることには注意が必要。
なんで初期設定がこうなっているのかは良くわからない。

時刻変更

これらの設定を変更するためにはどうすれば良いかなのだけれど、Oracle がプロシージャを用意しているので、それらプロシージャを叩く必要がある。
単純にスケジュールを変更するためには、DBMS_SCHEDULER.SET_ATTRIBUTE を実行すれば良い。

SQL> begin
DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW','repeat_interval','freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=5;byminute=0;bysecond=0');
end;
/

これは単純に時間を変更するだけだが、ウィンドウの新規作成だったりも DBMS_SCHEDULER を介すれば、作成・変更・削除できる。
このへんは、

あたりに詳しい。

注意しないといけないのは、

  • 統計情報更新の時刻をずらすことで、例えばこれまでは最新の統計情報を元にして動作していた夜間バッチが、古い統計情報を元にした実行計画となってしまったり、
  • WEEKNIGHT_WINDOW を直接変更すると、WEEKNIGHT_WINDOW を適用しているジョブ全ての起動時刻が変更される

といった影響になる。

そもそも

統計情報更新の負荷がどれくらいあるのかは、環境に依存する。
Oracle は全表に対する DML の発行を逐次記録しており、最後の統計情報更新後に行われた変更が表全体の 10 % 以上に達した表を統計情報収集対象とみなすという振舞いをする。さらに統計情報を収集する際も、必ずしも当該オブジェクト(表) をフルスキャンするわけでもなく、Oracle がサンプル数を計算し、それに伴って表を走査するため、負荷想定が立てにくい。

そもそも「本当に日次での実行が必要なのか?」という問題もあって、真剣に "最適な統計情報収集頻度と時刻はなんなのか" を突き詰めるのは不可能に近く、例え算出できたとしても、それが長期間に渡り最適であるという保証もない。どこかで妥協が必要だと思う。