|
Oracle10g Cost Base Optimizer -Monitoring Attribute-
|
For the past few weeks, we have confirmed that Oracle 10g can collect statistics to create task plans automatically by CBO. Before moving to the next topic, I would like to discuss more about the previous one. If you read the previous issue carefully,
you should have noticed that the scheduled time and the actual time when the statistical data is collected were different.
Confirm the job schedule (Scheduler Window) for obtaining statistics
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
|
According to the schedule, the statistical data will be obtained at 10 pm on weekdays and0 at 12 midnight on Saturday and Sunday (by default). Then, letfs check the actually executed job log.
Check the job log
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
|
On 11/6 (Sat), the job was executed at 3 pm and 11 pm. This results from the fact that the time zone of Scheduler Window is by default set as the Pacific zone where Oracle Inc. has its headquarters. (Refer to DBA_SCHEDULER_WINDOWS.NEXT_START_DATE column) This is a problem corresponding to Oracle bug:3721687. To execute it by Japanese standard time, it is necessary to change the time zone setting.
Change the time zone of Scheduler
exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('DEFAULT_TIMEZONE','+9:00');
|
Check the job log after changing the time zone
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
|
After changing the time zone, the job was executed at 10 pm on 11/12 (Fri).
However, it was executed at 6 am on 11/13 (Sat). In fact, it was scheduled to be at 12 midnight. This results from the fact that no more than two Scheduler Windows can be open at the same time.
Since the connecting time of WEEKNIGHT_WINDOW is 8 hours (dba_scheduler_windows), WEEKEND_WINDOW will be open at 10 pm+8 hours = 6 am. (Refer to DURATION column)
Judging from DBA_SCHEDULER_JOB_RUN_DETAILS.RUN_DURATION column, it takes 23 seconds to obtain the statistical data. For job scheduling, it is better to avoid peak time. It is recommended to change it during off-peak hours or to stop obtaining statistics automatically.
Change the Scheduler of Monday-Friday to 11 pm
exec DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW','repeat_interval',
'freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=23;byminute=0; bysecond=0');
|
Stop collecting statistics automatically
exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
|
When it is necessary to fix each individual table statistics to freeze the job plan, try the following command.
Fix the statistics
exec DBMS_STATS.LOCK_TABLE_STATS('SCOTT','EMP');
|
That's all for today.
Takuya Kishimoto
|
|
