Predefined Automated Maintenance Tasks
Automatic Optimizer Statistics Collection
Automatic Segment Advisor
Automatic SQL Tuning Advisor
Predefined Maintenance Windows
Window Name Description
MONDAY_WINDOW Starts at 10 p.m. on Monday and ends at 2 a.m.
TUESDAY_WINDOW Starts at 10 p.m. on Tuesday and ends at 2 a.m.
WEDNESDAY_WINDOW Starts at 10 p.m. on Wednesday and ends at 2 a.m.
THURSDAY_WINDOW Starts at 10 p.m. on Thursday and ends at 2 a.m.
FRIDAY_WINDOW Starts at 10 p.m. on Friday and ends at 2 a.m.
SATURDAY_WINDOW Starts at 6 a.m. on Saturday and is 20 hours long.
SUNDAY_WINDOW Starts at 6 a.m. on Sunday and is 20 hours long.
Obtaining Information about Predefined Maintenance Tasks
-- currently running Scheduler jobs created for automated maintenance tasks
select CLIENT_NAME,JOB_NAME,JOB_SCHEDULER_STATUS,TASK_NAME,TASK_TARGET_TYPE,TASK_TARGET_NAME,TASK_PRIORITY,TASK_OPERATION From DBA_AUTOTASK_CLIENT_JOB ;
-- automated maintenance task statistics over 7-day and 30-day periods
select CLIENT_NAME,STATUS,CONSUMER_GROUP,CLIENT_TAG,PRIORITY_OVERRIDE,ATTRIBUTES,WINDOW_GROUP,SERVICE_NAME,RESOURCE_PERCENTAGE,USE_RESOURCE_ESTIMATES,MEAN_JOB_DURATION,MEAN_JOB_CPU,MEAN_JOB_ATTEMPTS,MEAN_INCOMING_TASKS_7_DAYS,MEAN_INCOMING_TASKS_30_DAYS,TOTAL_CPU_LAST_7_DAYS,TOTAL_CPU_LAST_30_DAYS,MAX_DURATION_LAST_7_DAYS,MAX_DURATION_LAST_30_DAYS,WINDOW_DURATION_LAST_7_DAYS,WINDOW_DURATION_LAST_30_DAYS From DBA_AUTOTASK_CLIENT
-- history of automated maintenance task job runs
select CLIENT_NAME,WINDOW_NAME,WINDOW_START_TIME,WINDOW_DURATION,JOB_NAME,JOB_STATUS,JOB_START_TIME,JOB_DURATION,JOB_ERROR,JOB_INFO from DBA_AUTOTASK_JOB_HISTORY
order by WINDOW_START_TIME desc;
-- Lists the windows that belong to MAINTENANCE_WINDOW_GROUP
select WINDOW_NAME,WINDOW_NEXT_TIME,WINDOW_ACTIVE,AUTOTASK_STATUS,OPTIMIZER_STATS,SEGMENT_ADVISOR,SQL_TUNE_ADVISOR,HEALTH_MONITOR from DBA_AUTOTASK_WINDOW_CLIENTS;
-- per-window history of job execution counts for each
-- automated maintenance task
select CLIENT_NAME,WINDOW_NAME,WINDOW_START_TIME,WINDOW_DURATION,JOBS_CREATED,JOBS_STARTED,JOBS_COMPLETED,WINDOW_END_TIME from DBA_AUTOTASK_CLIENT_HISTORY;
Enabling and Disabling Maintenance Tasks
select CLIENT_NAME,STATUS,CONSUMER_GROUP,CLIENT_TAG,PRIORITY_OVERRIDE,ATTRIBUTES,WINDOW_GROUP,SERVICE_NAME,RESOURCE_PERCENTAGE,USE_RESOURCE_ESTIMATES,MEAN_JOB_DURATION,MEAN_JOB_CPU,MEAN_JOB_ATTEMPTS,MEAN_INCOMING_TASKS_7_DAYS,MEAN_INCOMING_TASKS_30_DAYS,TOTAL_CPU_LAST_7_DAYS,TOTAL_CPU_LAST_30_DAYS,MAX_DURATION_LAST_7_DAYS,MAX_DURATION_LAST_30_DAYS,WINDOW_DURATION_LAST_7_DAYS,WINDOW_DURATION_LAST_30_DAYS from DBA_AUTOTASK_CLIENT;
-- disable
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL); -- null means all widows
END;
/
-- enable
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
-- enable or disable ALL automated maintenance tasks for ALL windows
EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE;
EXECUTE DBMS_AUTO_TASK_ADMIN.ENABLE;
-- Enabling and Disabling Maintenance Tasks for Specific Maintenance Windows
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => 'MONDAY_WINDOW');
END;
/
Configuring Maintenance Windows
-- Modifying a Maintenance Window
-- see Using Windows SECTION
BEGIN
dbms_scheduler.disable( name => 'SATURDAY_WINDOW');
dbms_scheduler.set_attribute(
name => 'SATURDAY_WINDOW',
attribute => 'DURATION',
value => numtodsinterval(4, 'hour'));
dbms_scheduler.enable(
name => 'SATURDAY_WINDOW');
END;
/
-- Creating a New Maintenance Window
BEGIN
DBMS_SCHEDULER.CREATE_WINDOW(
window_name => 'EARLY_MORNING_WINDOW',
duration => numtodsinterval(1, 'hour'),
resource_plan => 'DEFAULT_MAINTENANCE_PLAN',
repeat_interval => 'FREQ=DAILY;BYHOUR=5;BYMINUTE=0;BYSECOND=0');
dbms_scheduler.add_window_group_member(
group_name => 'MAINTENANCE_WINDOW_GROUP',
window_list => 'EARLY_MORNING_WINDOW');
END;
/
-- Removing a Maintenance Window
-- window continues to exist but no longer runs automated maintenance tasks.
-- Any other Scheduler jobs assigned to this window continue to run as
usual.
BEGIN
DBMS_SCHEDULER.REMOVE_WINDOW_GROUP_MEMBER(
group_name => 'MAINTENANCE_WINDOW_GROUP',
window_list => 'EARLY_MORNING_WINDOW');
END;
/
Automatic Optimizer Statistics Collection
Automatic Segment Advisor
Automatic SQL Tuning Advisor
Predefined Maintenance Windows
Window Name Description
MONDAY_WINDOW Starts at 10 p.m. on Monday and ends at 2 a.m.
TUESDAY_WINDOW Starts at 10 p.m. on Tuesday and ends at 2 a.m.
WEDNESDAY_WINDOW Starts at 10 p.m. on Wednesday and ends at 2 a.m.
THURSDAY_WINDOW Starts at 10 p.m. on Thursday and ends at 2 a.m.
FRIDAY_WINDOW Starts at 10 p.m. on Friday and ends at 2 a.m.
SATURDAY_WINDOW Starts at 6 a.m. on Saturday and is 20 hours long.
SUNDAY_WINDOW Starts at 6 a.m. on Sunday and is 20 hours long.
Obtaining Information about Predefined Maintenance Tasks
-- currently running Scheduler jobs created for automated maintenance tasks
select CLIENT_NAME,JOB_NAME,JOB_SCHEDULER_STATUS,TASK_NAME,TASK_TARGET_TYPE,TASK_TARGET_NAME,TASK_PRIORITY,TASK_OPERATION From DBA_AUTOTASK_CLIENT_JOB ;
-- automated maintenance task statistics over 7-day and 30-day periods
select CLIENT_NAME,STATUS,CONSUMER_GROUP,CLIENT_TAG,PRIORITY_OVERRIDE,ATTRIBUTES,WINDOW_GROUP,SERVICE_NAME,RESOURCE_PERCENTAGE,USE_RESOURCE_ESTIMATES,MEAN_JOB_DURATION,MEAN_JOB_CPU,MEAN_JOB_ATTEMPTS,MEAN_INCOMING_TASKS_7_DAYS,MEAN_INCOMING_TASKS_30_DAYS,TOTAL_CPU_LAST_7_DAYS,TOTAL_CPU_LAST_30_DAYS,MAX_DURATION_LAST_7_DAYS,MAX_DURATION_LAST_30_DAYS,WINDOW_DURATION_LAST_7_DAYS,WINDOW_DURATION_LAST_30_DAYS From DBA_AUTOTASK_CLIENT
-- history of automated maintenance task job runs
select CLIENT_NAME,WINDOW_NAME,WINDOW_START_TIME,WINDOW_DURATION,JOB_NAME,JOB_STATUS,JOB_START_TIME,JOB_DURATION,JOB_ERROR,JOB_INFO from DBA_AUTOTASK_JOB_HISTORY
order by WINDOW_START_TIME desc;
-- Lists the windows that belong to MAINTENANCE_WINDOW_GROUP
select WINDOW_NAME,WINDOW_NEXT_TIME,WINDOW_ACTIVE,AUTOTASK_STATUS,OPTIMIZER_STATS,SEGMENT_ADVISOR,SQL_TUNE_ADVISOR,HEALTH_MONITOR from DBA_AUTOTASK_WINDOW_CLIENTS;
-- per-window history of job execution counts for each
-- automated maintenance task
select CLIENT_NAME,WINDOW_NAME,WINDOW_START_TIME,WINDOW_DURATION,JOBS_CREATED,JOBS_STARTED,JOBS_COMPLETED,WINDOW_END_TIME from DBA_AUTOTASK_CLIENT_HISTORY;
Enabling and Disabling Maintenance Tasks
select CLIENT_NAME,STATUS,CONSUMER_GROUP,CLIENT_TAG,PRIORITY_OVERRIDE,ATTRIBUTES,WINDOW_GROUP,SERVICE_NAME,RESOURCE_PERCENTAGE,USE_RESOURCE_ESTIMATES,MEAN_JOB_DURATION,MEAN_JOB_CPU,MEAN_JOB_ATTEMPTS,MEAN_INCOMING_TASKS_7_DAYS,MEAN_INCOMING_TASKS_30_DAYS,TOTAL_CPU_LAST_7_DAYS,TOTAL_CPU_LAST_30_DAYS,MAX_DURATION_LAST_7_DAYS,MAX_DURATION_LAST_30_DAYS,WINDOW_DURATION_LAST_7_DAYS,WINDOW_DURATION_LAST_30_DAYS from DBA_AUTOTASK_CLIENT;
-- disable
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL); -- null means all widows
END;
/
-- enable
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
-- enable or disable ALL automated maintenance tasks for ALL windows
EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE;
EXECUTE DBMS_AUTO_TASK_ADMIN.ENABLE;
-- Enabling and Disabling Maintenance Tasks for Specific Maintenance Windows
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => 'MONDAY_WINDOW');
END;
/
Configuring Maintenance Windows
-- Modifying a Maintenance Window
-- see Using Windows SECTION
BEGIN
dbms_scheduler.disable( name => 'SATURDAY_WINDOW');
dbms_scheduler.set_attribute(
name => 'SATURDAY_WINDOW',
attribute => 'DURATION',
value => numtodsinterval(4, 'hour'));
dbms_scheduler.enable(
name => 'SATURDAY_WINDOW');
END;
/
-- Creating a New Maintenance Window
BEGIN
DBMS_SCHEDULER.CREATE_WINDOW(
window_name => 'EARLY_MORNING_WINDOW',
duration => numtodsinterval(1, 'hour'),
resource_plan => 'DEFAULT_MAINTENANCE_PLAN',
repeat_interval => 'FREQ=DAILY;BYHOUR=5;BYMINUTE=0;BYSECOND=0');
dbms_scheduler.add_window_group_member(
group_name => 'MAINTENANCE_WINDOW_GROUP',
window_list => 'EARLY_MORNING_WINDOW');
END;
/
-- Removing a Maintenance Window
-- window continues to exist but no longer runs automated maintenance tasks.
-- Any other Scheduler jobs assigned to this window continue to run as
usual.
BEGIN
DBMS_SCHEDULER.REMOVE_WINDOW_GROUP_MEMBER(
group_name => 'MAINTENANCE_WINDOW_GROUP',
window_list => 'EARLY_MORNING_WINDOW');
END;
/