Automated Database Maintenance Tasks – Oracle Database 11g

Oracle is always trying to ease (or increase!) the burdens of DBAs  by introducing automation in their daily task. One of the tool is Automated Database Maintenance Tasks. This tool will run during the designated maintenance window. (By default, it will start from 10pm – 6pm on Weekdays and Saturday 12am to Monday 12am, for 48 hours)

This include 3 components.

  1. Automatic Optimizer Statistics Collection.
  2. Automatic Segment Advisor.
  3. Automatic SQL Tuning Advisor.

Automatic Optimizer Statistics Collection.

This will collection information regarding your database and database objects like number of rows and columns or IO/CPU performance. These information will be used to tune the best SQL execution plan. (How the SQL statement will execute)

Automatic Segment Advisor.

This will detect  fragmented tablespace and  identify which segments are available to reclaim. It will then make recommendations on how to reclaim them. Either by Online Segment Shrink or Online Table Redefinition.

Automatic SQL Tuning Advisor.

This will select top 5 high load SQL statements(CPU and IO) from AWR and analyze them. Recommendations include indexes and SQL profile. (These are like statistics regarding the particular query). Optionally, only SQL profiles can be implemented automatically. By default, Oracle will not implement accept the new SQL profile.

You may want to disable the Automated Database Maintenance Tasks for whatever reason. Perhaps a very heavy load database?

The code below will disable all 3 components. 

DBMS_AUTO_TASK_ADMIN.disable(‘auto optimizer stats collection’,NULL,NULL);
DBMS_AUTO_TASK_ADMIN.disable(‘auto space advisor’,NULL,NULL);
DBMS_AUTO_TASK_ADMIN.disable(‘sql tuning advisor’,NULL,NULL);

To verify that the components has been disabled, you can run the following

SELECT client_name, status, mean_job_duration FROM dba_autotask_client;

CLIENT_NAME                                           STATUS               MEAN_JOB_DURATION
————————————————– ——————– —————————————-
auto optimizer stats collection            DISABLED          +000000000 00:00:19.800000000
auto space advisor                                    DISABLED          +000000000 00:00:02.233333333
sql tuning advisor                                      DISABLED          +000000000 00:00:18.666666667

Personally, I feel that we can leave these components on UNLESS they are causing issues to the database. But other DBAs may have different views. Do share with me if you have any strong justifications why this should be turned off (Besides modification of SQL execution plans).

Side Topic;

Oracle 10g do not have DBMS_AUTO_TASK_ADMIN, instead, it has GATHER_STATS_JOB package. Similarly, if you want to disable and verify, you may run the following;

exec dbms_scheduler.disable(‘SYS.GATHER_STATS_JOB’);



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s