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.
[oracle@host ~]$ 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: oracle@host (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!
