Automated Database Maintenance Tasks

Oracle 11g includes three automated database maintenance tasks:

1.   Automatic Optimizer Statistics Collection - Gathers stale or missing statistics for all schema objects.

2.   Automatic Segment Advisor - Identifies segments that could be reorganized to save.

3.   Automatic SQL Tuning Advisor - Identifies and attempts to tune high load SQL.

You, as a DBA, should control the following:

·         Adjust the duration and start time of the maintence window appropriately

·         Control the restore plan that allocates to the automated maintenance tasks

·         Enable / disable individual tasks appropriate to your environment. 

Enterprise Manager is the preferred way to control these tasks, but you can always use the DBMS_AUTO_TASK_ADMIN package like the example below..

exec dbms_auto_task_admin.disable(  'auto optimizer stats collection', null, :window);

exec dbms_auto_task_admin.disable('auto space advisor', null, :window);

exec dbms_auto_task_admin.enable(  'auto optimizer stats collection', null, :window);

exec dbms_auto_task_admin.enable(  'auto space advisor', null, :window);

BEGIN
DBMS_SCHEDULER.DISABLE(name=>'"SYS"."MONDAY_WINDOW"',force=>TRUE);
END;

col client_name format a32, mean_job_duration format a32

set linesize 96

Select client_name, mean_job_duration, resource_percentage
FROM DBA_AUTOTASK_CLIENT

To change a task execution’s duration:

The value of the TIME_LIMIT parameter determines the total time allowed for a task execution.  By default its value is 60 minutes.

select parameter_value

from   dba_advisor_parameters

where  task_name = 'SYS_AUTO_SQL_TUNING_TASK' and parameter_name = 'TIME_LIMIT';

PARAMETER_VALUE

-------------------------------------------------------------------------

3600

To change it to 30 mintues:

exec dbms_sqltune.set_tuning_task_parameter(  'SYS_AUTO_SQL_TUNING_TASK', 'TIME_LIMIT', 1800);

SQL> select parameter_value

  2  from   dba_advisor_parameters

  3  where  task_name = 'SYS_AUTO_SQL_TUNING_TASK' and

  4         parameter_name = 'TIME_LIMIT';

PARAMETER_VALUE

---------------------------------------------------------------------

1800

SQL> exec dbms_sqltune.execute_tuning_task('SYS_AUTO_SQL_TUNING_TASK');

PL/SQL procedure successfully completed.