Managing maintenance tasks in Oracle 11g

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:

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