Administrando tareas de mantenimiento en Oracle 11g

Como quizás sepas, si quieres cambiar los parámetros de ejecución de las tareas automáticas de mantenimiento de la base de datos en Oracle 10g, tienes que modificar los trabajos y las ventanas asociadas a esas tareas; por lo tanto, podrías estar tentado a cambiar las tareas de mantenimiento de 11g de la misma forma pero encontrarás que no hay trabajos que parezcan tareas de mantenimiento, y las ventanas aún existen pero están desactivadas:

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

Nadie te contó? En Oracle 11g tienes que usar los procedimientos DBMS_AUTO_TASK_ADMIN para controlar las tres tareas de mantenimiento: Automatic Optimizer Statistics Collection, Automatic Segment Advisor y Automatic SQL Tuning Advisor.

Para revisar la información de las tareas puedes hacer esto:

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

Como puedes ver, las tres tareas están activadas por defecto y tienen su propio grupo de ventanas, pero todos los grupos de ventanas tienen las mismas ventanas miembros, una por día de la semana:

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.

Entonces, si quieres desactivar una tarea puedes hacerlo de esta forma:

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

Y para activarla de nuevo:

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

Ahora, si quieres desactivar una tarea un cierto día (ventana), puedes hacer esto:

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.

Y para activar una tarea en una ventana:

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.

Finalmente, si quieres cambiar los atributos de una ventana como el intérvalo de repetición, puedes hacerlo como es 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.

Y en caso de que quieras saber que operación está asociada a que cliente:

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

Más información:

Managing Automated Database Maintenance Tasks
DBMS_AUTO_TASK_ADMIN