As you might know, if you want to change execution parameters of automated database maintenance tasks in Oracle 10g you have to modify the jobs and windows associated to those tasks; therefore, you might be tempted to change 11g maintenance tasks the same way but you'll find that there are no jobs that look like maintenance tasks, and the windows still exist but are disabled:
Did nobody tell you? In Oracle 11g you have to use the DBMS_AUTO_TASK_ADMIN procedures to control the three maintenance tasks: Automatic Optimizer Statistics Collection, Automatic Segment Advisor and Automatic SQL Tuning Advisor.
To check tasks information you can do this:
As you can see, all the three tasks are enabled by default and have its own window group, but all the window groups have the same window members, one per day of the week:
Then, if you want to disable a task you can do it this way:
And for enabling it again:
Now, if you want to disable a task for a certain day (window), you can do this:
And for enabling a task in a window:
Finally, if you want to change window attributes like the repeat interval, you can do it as usual:
And just in case you want to know what operation is associated to what client:
More information:
Managing Automated Database Maintenance Tasks
DBMS_AUTO_TASK_ADMIN
SQL> set linesize 120
SQL> column WINDOW_NAME format a20
SQL> column ENABLED format a7
SQL> column REPEAT_INTERVAL format a60
SQL> column DURATION format a15
SQL> select window_name, enabled, repeat_interval, duration from dba_scheduler_windows
where window_name like 'WEEK%';
WINDOW_NAME ENABLED REPEAT_INTERVAL DURATION
-------------------- ------- ------------------------------------------------------------ ---------------
WEEKNIGHT_WINDOW FALSE freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; b +000 08:00:00
ysecond=0
WEEKEND_WINDOW FALSE freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00
Did nobody tell you? In Oracle 11g you have to use the DBMS_AUTO_TASK_ADMIN procedures to control the three maintenance tasks: Automatic Optimizer Statistics Collection, Automatic Segment Advisor and Automatic SQL Tuning Advisor.
To check tasks information you can do this:
SQL> column CLIENT_NAME format a32
SQL> column STATUS format a10
SQL> column WINDOW_GROUP format a20
SQL> column ATTRIBUTES format a40
SQL> select client_name, status, window_group, attributes from dba_autotask_client;
CLIENT_NAME STATUS WINDOW_GROUP ATTRIBUTES
-------------------------------- ---------- -------------------- ----------------------------------------
auto optimizer stats collection ENABLED ORA$AT_WGRP_OS ON BY DEFAULT, VOLATILE, SAFE TO KILL
auto space advisor ENABLED ORA$AT_WGRP_SA ON BY DEFAULT, VOLATILE, SAFE TO KILL
sql tuning advisor ENABLED ORA$AT_WGRP_SQ ONCE PER WINDOW, ON BY DEFAULT, VOLATILE
, SAFE TO KILL
As you can see, all the three tasks are enabled by default and have its own window group, but all the window groups have the same window members, one per day of the week:
SQL> select window_name, enabled, repeat_interval, duration from dba_scheduler_windows where window_name in
(select WINDOW_NAME from dba_scheduler_wingroup_members where WINDOW_GROUP_NAME='ORA$AT_WGRP_SQ');
WINDOW_NAME ENABLED REPEAT_INTERVAL DURATION
-------------------- ------- ------------------------------------------------------------ ---------------
SATURDAY_WINDOW TRUE freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00
WEDNESDAY_WINDOW TRUE freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
THURSDAY_WINDOW TRUE freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SUNDAY_WINDOW TRUE freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00
FRIDAY_WINDOW TRUE freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
TUESDAY_WINDOW TRUE freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
MONDAY_WINDOW TRUE freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
7 rows selected.
Then, if you want to disable a task you can do it this way:
SQL> begin
dbms_auto_task_admin.disable
(client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
end;
/
PL/SQL procedure successfully completed.
SQL> select client_name, status, window_group, attributes from dba_autotask_client;
CLIENT_NAME STATUS WINDOW_GROUP ATTRIBUTES
-------------------------------- ---------- -------------------- ----------------------------------------
auto optimizer stats collection ENABLED ORA$AT_WGRP_OS ON BY DEFAULT, VOLATILE, SAFE TO KILL
auto space advisor ENABLED ORA$AT_WGRP_SA ON BY DEFAULT, VOLATILE, SAFE TO KILL
sql tuning advisor DISABLED ORA$AT_WGRP_SQ ONCE PER WINDOW, ON BY DEFAULT, VOLATILE
, SAFE TO KILL
And for enabling it again:
SQL> begin
dbms_auto_task_admin.enable
(client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
end;
/
PL/SQL procedure successfully completed.
SQL> select client_name, status, window_group, attributes from dba_autotask_client;
CLIENT_NAME STATUS WINDOW_GROUP ATTRIBUTES
-------------------------------- ---------- -------------------- ----------------------------------------
auto optimizer stats collection ENABLED ORA$AT_WGRP_OS ON BY DEFAULT, VOLATILE, SAFE TO KILL
auto space advisor ENABLED ORA$AT_WGRP_SA ON BY DEFAULT, VOLATILE, SAFE TO KILL
sql tuning advisor ENABLED ORA$AT_WGRP_SQ ONCE PER WINDOW, ON BY DEFAULT, VOLATILE
, SAFE TO KILL
Now, if you want to disable a task for a certain day (window), you can do this:
SQL> begin
dbms_auto_task_admin.disable
(client_name => 'sql tuning advisor',
operation => NULL,
window_name => 'MONDAY_WINDOW');
end;
/
PL/SQL procedure successfully completed.
SQL> select WINDOW_NAME, OPTIMIZER_STATS, SEGMENT_ADVISOR, SQL_TUNE_ADVISOR from dba_autotask_window_clients;
WINDOW_NAME OPTIMIZER_STATS SEGMENT_ADVISOR SQL_TUNE_ADVISOR
-------------------- ------------------------ ------------------------ ------------------------
WEDNESDAY_WINDOW ENABLED ENABLED ENABLED
FRIDAY_WINDOW ENABLED ENABLED ENABLED
SATURDAY_WINDOW ENABLED ENABLED ENABLED
THURSDAY_WINDOW ENABLED ENABLED ENABLED
TUESDAY_WINDOW ENABLED ENABLED ENABLED
SUNDAY_WINDOW ENABLED ENABLED ENABLED
MONDAY_WINDOW ENABLED ENABLED DISABLED
7 rows selected.
And for enabling a task in a window:
SQL> begin
dbms_auto_task_admin.enable
(client_name => 'sql tuning advisor',
operation => NULL,
window_name => 'MONDAY_WINDOW');
end;
/
PL/SQL procedure successfully completed.
SQL> select WINDOW_NAME, OPTIMIZER_STATS, SEGMENT_ADVISOR, SQL_TUNE_ADVISOR from dba_autotask_window_clients;
WINDOW_NAME OPTIMIZER_STATS SEGMENT_ADVISOR SQL_TUNE_ADVISOR
-------------------- ------------------------ ------------------------ ------------------------
WEDNESDAY_WINDOW ENABLED ENABLED ENABLED
FRIDAY_WINDOW ENABLED ENABLED ENABLED
SATURDAY_WINDOW ENABLED ENABLED ENABLED
THURSDAY_WINDOW ENABLED ENABLED ENABLED
TUESDAY_WINDOW ENABLED ENABLED ENABLED
SUNDAY_WINDOW ENABLED ENABLED ENABLED
MONDAY_WINDOW ENABLED ENABLED ENABLED
7 rows selected.
Finally, if you want to change window attributes like the repeat interval, you can do it as usual:
SQL> exec dbms_scheduler.set_attribute('MONDAY_WINDOW','repeat_interval','freq=daily;byday=MON;byhour=21;byminute=0; bysecond=0');
PL/SQL procedure successfully completed.
SQL> select window_name, enabled, repeat_interval, duration from dba_scheduler_windows where window_name in
(select WINDOW_NAME from dba_scheduler_wingroup_members where WINDOW_GROUP_NAME='ORA$AT_WGRP_SQ');
WINDOW_NAME ENABLED REPEAT_INTERVAL DURATION
-------------------- ------- ------------------------------------------------------------ ---------------
SATURDAY_WINDOW TRUE freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00
MONDAY_WINDOW TRUE freq=daily;byday=MON;byhour=21;byminute=0; bysecond=0 +000 04:00:00
WEDNESDAY_WINDOW TRUE freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
THURSDAY_WINDOW TRUE freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SUNDAY_WINDOW TRUE freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00
FRIDAY_WINDOW TRUE freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
TUESDAY_WINDOW TRUE freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
7 rows selected.
And just in case you want to know what operation is associated to what client:
SQL> column OPERATION_NAME format a40
SQL> select client_name, operation_name from dba_autotask_operation;
CLIENT_NAME OPERATION_NAME
-------------------------------- ----------------------------------------
auto optimizer stats collection auto optimizer stats job
auto space advisor auto space advisor job
sql tuning advisor automatic sql tuning task
More information:
Managing Automated Database Maintenance Tasks
DBMS_AUTO_TASK_ADMIN
