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

Posted in: Technical Track

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!

View Yury Velikanov's profile on LinkedIn

email

Interested in working with Yury? Schedule a tech call.

About the Author

Yury is a nice person who enjoys meeting and working with people on challenging projects in the Oracle space. He started to work as an Oracle DBA 14 years ago (1997). For the past 10 years (since 2001) his main area of expertise is Oracle e-Business Suite. Yury is an OCP 7,8,9,10g and OCM 9i,10g. He is a frequent presenter at Oracle related conferences such as Hotsos, UKOUG and AUOUG. Yury is a socially active person. Apart from Social Media (Twitter, Bloging, Facebook) he is the primary organizer of Sydney Oracle Meetup group (250 people). So if you happen to be in Sydney (Australia) drop Yury a message and stop by at one of his Meetups.

5 Comments. Leave new

Just a follow up on the blog post.
I found the hit for this very specific issue:

PLSQL Procedure Causing ORA-04030: (pga heap,control file i/o buffer) And ORA-04030: (koh-kghu sessi,pmuccst: adt/record) or ORA-04030: (koh-kghucall ,pmucalm coll) Errors [ID 1325100.1]

Solution:
— Change the upper max_map_count limit at either the OS or at the database level

Reply

At SAP we deactivate (or ask for) the stats collection.
Same about the autospace, but we remind that can be called “on demmand” (once per month or so if the results are going to be seen)
We do not use the sql tuning advisor, so we alo deactivate it by default unless it is used (usually not)

Reply

Thanks, Yury !
Very useful post. Very descriptive: you find everything you need to resolve the issue. I love it.
We always disable this behind-the-scenes tasks (I always prefer to handle it in more controlled way), but every major upgrade (like we just upgraded to 12C) brings it all back “ENABLED”.
Again, thanks for the very good post.

Reply

Hi,
Thanks for this link . i have some doubt. i executed below command to disable all the auto task.
exec dbms_auto_task_admin.disable;
but still after that i am seeing task are in enabled state

SELECT client_name, status FROM dba_autotask_operation;

CLIENT_NAME STATUS
—————————————————————- ——–
auto optimizer stats collection DISABLED
auto space advisor ENABLED
sql tuning advisor ENABLED

Regards
Ramki

Reply

Hi Ramki!

Here is the solution:

This is expected Behaviour.
The views assume that there is a one to many relationship between CLIENTS and TASKS.
Task can be used by different/multiple client. So even though if we disable the client, the DBA_AUTOTASK_TASK may still show the status as enabled. In current version, the TASKS has only one CLIENT. But in future version of oracle , the TASKS can have multiple CLIENTS so the status in DBA_AUTOTASK_TASK will show as ENABLED.
So the correct way to check the status is through DBA_AUTOTASK_CLIENT.

Source:
DBA_AUTOTASK_TASK and DBA_AUTOTASK_CLIENT Shows Different Status For Auto Optimizer Stats Collection (Doc ID 858852.1)

Cheers,
R.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *