Database Reference
In-Depth Information
PROGRAM_NAME SCHEDULE_NAME ENABLED STATE
----------------- ------------------------ ------- ---------
GATHER_STATS_PROG MAINTENANCE_WINDOW_GROUP TRUE SCHEDULED
SQL> SELECT program_action, number_of_arguments, enabled
2 FROM dba_scheduler_programs
3 WHERE owner = 'SYS'
4 AND program_name = 'GATHER_STATS_PROG';
PROGRAM_ACTION NUMBER_OF_ARGUMENTS ENABLED
----------------------------------------- ------------------- -------
dbms_stats.gather_database_stats_job_proc 0 TRUE
SQL> SELECT w.window_name, w.repeat_interval, w.duration, w.enabled
2 FROM dba_scheduler_jobs j, dba_scheduler_wingroup_members m,
3 dba_scheduler_windows w
4 WHERE j.schedule_name = m.window_group_name
5 AND m.window_name = w.window_name
6 AND j.owner = 'SYS'
7 AND j.job_name = 'GATHER_STATS_JOB';
WINDOW_NAME REPEAT_INTERVAL DURATION ENABLED
---------------- ------------------------------------- ------------- -------
WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI; +000 08:00:00 TRUE
byhour=22;byminute=0; bysecond=0
WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminut +002 00:00:00 TRUE
e=0;bysecond=0
In summary, the configuration is the following:
•
The job executes the
gather_stats_prog
program and is can run within the
maintenance_window_group
window group.
The
•
gather_stats_prog
program calls, without parameters, the
gather_database_stats_
job_proc
procedure in the
dbms_stats
package. Because no parameters are passed to it,
the only way to change its behavior is to change the default configuration of the
dbms_stats
package, as explained in the “Configuring the dbms_stats Package” section earlier in this
chapter. Note that this procedure is undocumented and tagged “for internal use only.”
•
The
maintenance_window_group
window group has two members: the
weeknight_window
window and the
weekend_window
window. The former opens for eight hours every night
from Monday to Friday. The latter is open Saturday and Sunday. The gathering of the object
statistics takes place when one of these two windows is open.
•
The job, the program, and the windows are enabled.
The opening and duration of the default scheduling should be checked and, whenever necessary, changed to
match the expected statistics gathering frequency. If possible, they should match the low-utilization periods.
