Yury, Remember: This is How You Disable AUTO_TASKs in 11G

I find myself forgetting how to disable Oracle AUTO_TASKs on a regular basis. Therefore, I have decided to document it in this small blog post. As an additional bonus, I may hear others’ experience in the area. So if you disagree or have some other experience, please do not hesitate to comment under this post.

Why

You may ask: Why would anyone like to disable ‘auto space advisor’, ‘sql tuning advisor’ or ‘auto optimizer stats collection’? My current thinking is as follows:

• What’s the point to run advisors if no one is using the results? (Skip this point if you are using advisors; for most of my clients this argument works well.)
• Advisors may require (and I believe those do) additional Tuning licence. (If you don’t have a licence, you are wasting expensive CPU resources.)
• Some applications (such as Oracle E-Business Suite) have their own statistic-gathering jobs, and you should disable the seeded job.
• In many cases, it is easier and cheaper to disable auto tasks than to troubleshoot them (assumption – no one using auto tasks results).

Just to remind you that all: 3 tasks are enabled by default independently whether you update your database to 11G or create a database from scratch.

How

set lines 180 pages 1000
col client_name for a40
col attributes for a60
select client_name, status,attributes,service_name from dba_autotask_client
/
BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'auto space advisor',
    operation   => NULL,
    window_name => NULL);
END;
/
BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;
/
BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'auto optimizer stats collection',
    operation   => NULL,
    window_name => NULL);
END;
/
select client_name, status,attributes,service_name from dba_autotask_client
/
## Enabling
BEGIN
dbms_auto_task_admin.enable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
END;
/

SQL output example:

SQL > set lines 180 pages 1000
SQL > set lines 180 pages 1000
col client_name for a40
col attributes for a60
select client_name, status,attributes,service_name from dba_autotask_client
/
SQL > SQL > SQL >   2
CLIENT_NAME                              STATUS   ATTRIBUTES                                                   SERVICE_NAME
---------------------------------------- -------- ------------------------------------------------------------ ----------------------------------------------------------------
auto optimizer stats collection          DISABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL
auto space advisor                       ENABLED  ON BY DEFAULT, VOLATILE, SAFE TO KILL
sql tuning advisor                       ENABLED  ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL
SQL >

My reason

In my case, I was paged from one of the targets on the following error:

ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu sessi,pmuccst: adt/record)

The trace file indicates that the process was executing SQL Advisor routine at the time of the error.

[[email protected] ~]$ more /o001/oracle/megainst/log/diag/rdbms/megainst/megainst/incident/incdir_6841002/megainst_j001_14215_i6841002.trc
Dump file /o001/oracle/megainst/log/diag/rdbms/megainst/megainst/incident/incdir_6841002/megainst_j001_14215_i6841002.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /o001/oracle/megainst/db/11.2.0
System name:	Linux
Node name:	host
Release:	2.6.18-274.el5
Version:	#1 SMP Fri Jul 8 17:36:59 EDT 2011
Machine:	x86_64
VM name:	VMWare Version: 6
Instance name: megainst
Redo thread mounted by this instance: 1
Oracle process number: 60
Unix process pid: 14215, image: [email protected] (J001)

*** 2012-11-10 06:32:45.153
*** SESSION ID:(83.39433) 2012-11-10 06:32:45.153
*** CLIENT ID:() 2012-11-10 06:32:45.153
*** SERVICE NAME:(SYS$USERS) 2012-11-10 06:32:45.153
*** MODULE NAME:(DBMS_SCHEDULER) 2012-11-10 06:32:45.153
*** ACTION NAME:(ORA$AT_SQ_SQL_SW_6576) 2012-11-10 06:32:45.153

Dump continued from file: /o001/oracle/megainst/log/diag/rdbms/megainst/megainst/trace/megainst_j001_14215.trc
ORA-04030: out of process memory when trying to allocate 16408 bytes (pga heap,kgh stack)
ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu sessi,pmuccst: adt/record)

Don’t forget to comment and share your experiences!