Oracle10g Cost Base Optimizerにまつわる検証 その4

<Oracle10g Cost Base Optimizerにまつわる検証 その4>
~Monitoring属性の変~
ペンネーム:グリーンペペ

先回までで10gからは、CBOによって実行計画が作成されるように統計情報が自
動で取得されることを確認しました。
次の話題へと発展させて行きたいのですが、前回分の補足を今回はさせてくだ
さい。前回の検証生活をじっくり読まれた読者の皆様はスケジュールされてい
る時間と実際に統計情報が取得された時間が違っていることにお気づきになら
れたのではないでしょうか?

■統計情報取得のjobスケジュール(スケジューラ・ウィンドウ)を確認

sql> select window_name,repeat_interval,duration,next_start_date,last_start_date
     from dba_scheduler_windows;

WINDOW_NAME
------------------------------
WEEKNIGHT_WINDOW
WEEKEND_WINDOW

REPEAT_INTERVAL
---------------------------------------------------------------------------
freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0
freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0

DURATION
---------------------------------------------------------------------------
+000 08:00:00
+002 00:00:00

NEXT_START_DATE                    LAST_START_DATE
---------------------------------- ----------------------------------------
04-11-08 22:00:00.300000 -08:00    04-11-05 22:00:00.260934 -08:00
04-11-06 00:00:00.500000 -08:00    04-11-06 06:00:01.983457 -08:00

平日は22時、土日は0時にスケジュールされています(defaultの状態)。
では、実際に実行されているjobログを確認してみます。

■jobのログを確認

SQL> select log_date,owner,job_name,status,run_duration
     from dba_scheduler_job_run_details; 

LOG_DATE
-------------------------------------------------------------
04-11-06 15:00:23.394481 +09:00
04-11-06 23:00:13.160208 +09:00

OWNER                          JOB_NAME
------------------------------ ------------------------------
SYS                            GATHER_STATS_JOB
SYS                            GATHER_STATS_JOB

STATUS                         RUN_DURATION
------------------------------ ------------------------------
SUCCEEDED                      +000 00:00:21
SUCCEEDED                      +000 00:00:09

11/6(土)の15時と23時に実行されています。
これはスケジューラ・ウィンドウのタイムゾーンがオラクル本社のある太平洋
標準時に設定されてしまっているからです。
(DBA_SCHEDULER_WINDOWS.NEXT_START_DATE列参照)
これはOracleのbug:3721687に該当する問題です。
日本の標準時で実行したい場合は、タイムゾーンを変更する必要があります。

■スケジューラのタイムゾーンを変更

exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('DEFAULT_TIMEZONE','+9:00');

■タイムゾーン変更後のjobのログを確認

LOG_DATE
------------------------------------------------------------
04-11-12 22:00:26.606820 +09:00
04-11-13 06:00:29.429540 +09:00

OWNER                          JOB_NAME
------------------------------ ------------------------------
SYS                            GATHER_STATS_JOB
SYS                            GATHER_STATS_JOB

STATUS                         RUN_DURATION
------------------------------ ------------------------------
SUCCEEDED                      +000 00:00:23
SUCCEEDED                      +000 00:00:24

変更後は11/12(金) 22時に実行されていることが確認できます。
しかし、11/13(土)は6時に実行されています。スケジュールでは0時にスケ
ジュールされているのですが。これは、スケジューラ・ウィンドウは一度に
1つしかオープンにできないためです。
WEEKNIGHT_WINDOWの持続時間が8時間であるために(dba_scheduler_windows.
DURATION列参照)22時+8時間で6時にWEEKEND_WINDOWはオープンされます。

また、DBA_SCHEDULER_JOB_RUN_DETAILS.RUN_DURATION列を見ると統計情報の取
得に23秒を要していることが分かります。
jobがスケジュールされている時間帯がピーク時であるなら好ましくありません。
オフピーク時にスケジュールを変更するか、自動統計収集を止める必要があり
ます。

■月-金曜日のスケジューラを23時に変更

exec DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW','repeat_interval',
'freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=23;byminute=0; bysecond=0');

■自動統計収集を止める

exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

また、実行計画を凍結するために個別の表の統計情報を固定する必要がある場
合は以下のコマンドになります。

■統計情報をfixする

exec DBMS_STATS.LOCK_TABLE_STATS('SCOTT','EMP');

今回はTIPSっぽくなりました。
来週もお付き合い下さい。

ラーメンが恋しい茅ヶ崎より