[Oracle] オプティマイザ統計情報の収集は基本的にOracle任せでOK

Oracle 10g ではオプティマイザ統計情報の収集機能がかなり強化され便利になりました。

中でも一番便利になったのは、デフォルトで１日１回オプティマイザ統計情報の自動収集が行われるようジョブがスケジューリングされるようになったことです。(※）

※　STATISTICS_LEVEL初期化パラメータが'TYPICAL'または'ALL'の場合に設定されます。（デフォルトは'TYPICAL'）

オプティマイザ統計情報の自動収集ジョブは'GATHER_STATS_JOB'というジョブ名で登録されており、'DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC'という内部プロシージャを'MAINTENANCE_WINDOW_GROUP'というウィンドウグループを使って実行するようスケジューリングされています。

SQL> SELECT SJ.OWNER, SJ.JOB_NAME, SP.PROGRAM_ACTION, SJ.SCHEDULE_NAME
  2     FROM DBA_SCHEDULER_JOBS SJ
  3     INNER JOIN DBA_SCHEDULER_PROGRAMS SP
  4             ON SJ.PROGRAM_NAME = SP.PROGRAM_NAME
  5     WHERE SJ.JOB_NAME = 'GATHER_STATS_JOB';

OWNER      JOB_NAME             PROGRAM_ACTION                                     SCHEDULE_NAME
---------- -------------------- -------------------------------------------------- ------------------------------
SYS        GATHER_STATS_JOB     dbms_stats.gather_database_stats_job_proc          MAINTENANCE_WINDOW_GROUP

この'DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC'は DBMS_STATS.GATHER_DATABASE_STATS プロシージャを 'GATHER_AUTO' オプション付きで実行するのとほぼ同じ動作をするようです。

つまり、オプティマイザ統計情報が欠落している表（※１）や失効している表（※２）を自動的に検索してオプティマイザ統計情報を収集します。

無駄なオプティマイザ統計情報を収集しなくて済み非常に効率的です。

※１　新規に作成された表やオプティマイザ統計情報を削除された表
※２　表の監視機能が有効化されている（デフォルトで有効化されている）場合に、データが１０％以上変更されるとオプティマイザ統計情報が失効していると判定されます。

DBMS_STATS.GATHER_DATABASE_STATS プロシージャを 'GATHER_AUTO' オプション付きで実行するのと異なる点は、最もオプティマイザ統計情報を収集する必要のある表から優先的に処理していくということです。

つまり、万が一'MAINTENANCE_WINDOW_GROUP'で指定されている時間枠の範囲内で処理が完了しなかったとしても、オプティマイザの動作に悪影響を及ぼしにくくなるよう配慮されているということのようです。

'MAINTENANCE_WINDOW_GROUP'は'WEEKNIGHT_WINDOW'と'WEEKEND_WINDOW'という２つの時間枠で構成され、'WEEKNIGHT_WINDOW'は月～金の 22:00 ～ 6:00、'WEEKEND_WINDOW'は土曜日の 0:00 ～月曜日の 0:00 に設定されています。

SQL> SELECT SW.WINDOW_NAME, SW.REPEAT_INTERVAL, SW.DURATION
  2     FROM DBA_SCHEDULER_WINGROUP_MEMBERS SWM
  3     INNER JOIN DBA_SCHEDULER_WINDOWS SW
  4             ON SWM.WINDOW_NAME = SW.WINDOW_NAME
  5     WHERE SWM.WINDOW_GROUP_NAME = 'MAINTENANCE_WINDOW_GROUP';

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

つまり、これらの時間枠を使用することによって、一般的にデータベースシステムがあまり使われていないであろうと思われる時間帯にオプティマイザ統計情報の収集が実行されるというわけです。

なかなかよく考えられた仕組みだと思います。

従来のバージョンでは、Oracle を安定的なパフォーマンスで稼動させるためには「定期的にアナライズを行わなければならない」ということが格言となっており、データベース管理者が「定期メンテナンスジョブ」などと称して定期的にアナライズを実行するよう仕込んだりしたものですが、10g の場合は、さほどシビアな性能を要求されないデータベースであれば、オプティマイザ統計情報の収集をデフォルトの設定のまま Oracle に任せておけば十分ではないでしょうか。

Archive Redo Blog

DBエンジニアのあれこれ備忘録

[Oracle] オプティマイザ統計情報の収集は基本的にOracle任せでOK