Database Reference
In-Depth Information
PROGRAM_ACTION NUMBER_OF_ARGUMENTS ENABLED
----------------------------------------- ------------------- -------
dbms_stats.gather_database_stats_job_proc 0 TRUE
SQL> SELECT window_group
2 FROM dba_autotask_client
3 WHERE client_name = 'auto optimizer stats collection';
WINDOW_GROUP
--------------
ORA$AT_WGRP_OS
SQL> SELECT w.window_name, w.repeat_interval, w.duration, w.enabled
2 FROM dba_autotask_window_clients c, dba_scheduler_windows w
3 WHERE c.window_name = w.window_name
4 AND c.optimizer_stats = 'ENABLED';
WINDOW_NAME REPEAT_INTERVAL DURATION ENABLED
---------------- ----------------------------------------------------- ------------- -------
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 TRUE
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 TRUE
In summary, the configuration is the following:
•
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 window group used by the automatic maintenance task has seven members, one for each
day of the week. From Monday to Friday it's open four hours a day. For Saturday and Sunday,
it's open 20 hours a day. The gathering of the object statistics takes place when one of these
windows is open. Note that when a window is open for a long time, such as on the weekend,
the
gather_stats_prog
program is restarted every four hours.
The maintenance task, 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.
To completely enable or disable the maintenance task, the following PL/SQL calls are available. By setting
the
windows_name
parameter to a non-
NULL
value, you can also enable or disable the maintenance task for a single
window only.
dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL)