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.
- Automatic Optimizer Statistics Collection.
- Automatic Segment Advisor.
- 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).
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;
select state from DBA_SCHEDULER_JOBS where JOB_NAME =’GATHER_STATS_JOB’;