1.a),check the task name(in database 11g the name is :auto optimizer stats collection ) and the window group:
>>select client_name,status, attributes, window_group,service_name from dba_autotask_client where client_name='auto optimizer stats collection';
CLIENT_NAME STATUS WINDOW_GROUP
---------------------------------------- -------- ------------------------------
auto optimizer stats collection ENABLED ORA$AT_WGRP_OS
1.b),check the window name of the window group:
>>select window_name from DBA_SCHEDULER_WINGROUP_MEMBERS where window_group_name = 'ORA$AT_WGRP_OS';
WINDOW_NAME
------------------------------
MONDAY_WINDOW
TUESDAY_WINDOW
WEDNESDAY_WINDOW
THURSDAY_WINDOW
FRIDAY_WINDOW
SATURDAY_WINDOW
SUNDAY_WINDOW
1.c),check the start time and the duration of every window:
>>select window_name, REPEAT_INTERVAL, DURATION from DBA_SCHEDULER_WINDOWS where window_name='MONDAY_WINDOW';
WINDOW_NAME REPEAT_INTERVAL DURATION
------------------------------ ------------------------------------------------------------ ---------------------------------------------------------------------------
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
11g+ Automatic Maintenance Tasks
-
What is the name of the default stats gathering job on 11g?
The automatic statistics gathering job on 11g is called "auto optimizer stats collection".
-
What are the default windows for the automated maintenance task to run?
In 11g daily maintenance windows are provided. by default these are defined as :
- Weeknights: Starts at 10 p.m. and ends at 2 a.m.
- Weekends: Starts at 6 a.m. is 20 hours long.
See:
Oracle® Database Administrator's Guide
11gRelease 2 (11.2)
Part Number E17120-05
Automated Maintenance Tasks Reference
Table 26-1 Predefined Maintenance Windows
http://docs.oracle.com/cd/E18283_01/server.112/e17120/tasks006.htm#CIHJHGCA
Document 743507.1How to Benefit from Automatic Maintenance Tasks Following the Removal of the GATHER_STATS_JOB in 11g? -
How do you change the default windows for the automated maintenance task to run?
Maintenance windows can be modified using the DBMS_SCHEDULER PL/SQL package. For details see:
Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E17120-05
Configuring Maintenance Windows
http://docs.oracle.com/cd/E18283_01/server.112/e17120/tasks004.htm
Here is an example to change the default time for the daily maintenance window for example to 2:00 AM instead of 10:00 PM using the following commands :
BEGIN
dbms_scheduler.disable(
name => 'SATURDAY_WINDOW');
dbms_scheduler.set_attribute(
name => 'SATURDAY_WINDOW',
attribute => 'repeat_interval',
value => 'freq=daily;byday=SAT;byhour=02;byminute=0;bysecond=0');
dbms_scheduler.enable(
name => 'SATURDAY_WINDOW');
END;
/
BEGIN
dbms_scheduler.disable(
name => 'SUNDAY_WINDOW');
dbms_scheduler.set_attribute(
name => 'SUNDAY_WINDOW',
attribute => 'repeat_interval',
value => 'freq=daily;byday=SUN;byhour=02;byminute=0;bysecond=0');
dbms_scheduler.enable(
name => 'SUNDAY_WINDOW');
END;
/
BEGIN
dbms_scheduler.disable(
name => 'MONDAY_WINDOW');
dbms_scheduler.set_attribute(
name => 'MONDAY_WINDOW',
attribute => 'repeat_interval',
value => 'freq=daily;byday=MON;byhour=02;byminute=0;bysecond=0');
dbms_scheduler.enable(
name => 'MONDAY_WINDOW');
END;
/
BEGIN
dbms_scheduler.disable(
name => 'TUESDAY_WINDOW');
dbms_scheduler.set_attribute(
name => 'TUESDAY_WINDOW',
attribute => 'repeat_interval',
value => 'freq=daily;byday=TUE;byhour=02;byminute=0;bysecond=0');
dbms_scheduler.enable(
name => 'TUESDAY_WINDOW');
END;
/
BEGIN
dbms_scheduler.disable(
name => 'WEDNESDAY_WINDOW');
dbms_scheduler.set_attribute(
name => 'WEDNESDAY_WINDOW',
attribute => 'repeat_interval',
value => 'freq=daily;byday=WED;byhour=02;byminute=0;bysecond=0');
dbms_scheduler.enable(
name => 'WEDNESDAY_WINDOW');
END;
/
BEGIN
dbms_scheduler.disable(
name => 'THURSDAY_WINDOW');
dbms_scheduler.set_attribute(
name => 'THURSDAY_WINDOW',
attribute => 'repeat_interval',
value => 'freq=daily;byday=THU;byhour=02;byminute=0;bysecond=0');
dbms_scheduler.enable(
name => 'THURSDAY_WINDOW');
END;
/
BEGIN
dbms_scheduler.disable(
name => 'FRIDAY_WINDOW');
dbms_scheduler.set_attribute(
name => 'FRIDAY_WINDOW',
attribute => 'repeat_interval',
value => 'freq=daily;byday=FRI;byhour=02;byminute=0;bysecond=0');
dbms_scheduler.enable(
name => 'FRIDAY_WINDOW');
END;
/ - 参考:FAQ: Automatic Statistics Collection (文档 ID 1233203.1)