How to Disable Automatic Statistics Collection in 11g [ID 1056968.1]

		Modified 31-MAR-2011 Type HOWTO Status PUBLISHED

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.1 - Release: 11.1 to 11.2
Information in this document applies to any platform.

Goal

How to disable automatic statistics collection in 11g

Solution

The command to disable automatic optimizer statistics collection is:

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/

This can be verified with:

SQL> select client_name,status from Dba_Autotask_Client;

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

to re-enable again:

BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/

For more details see :

Note:779596.1 How Can We Find Out Status Of Task 'Auto Optimizer Stats Collection'

References

NOTE:311836.1 - How to Disable Automatic Statistics Collection in 10G ?
NOTE:743507.1 - Why Has the GATHER_STATS_JOB been removed in 11g?
NOTE:779596.1 - How Can We Find Out Status Of Task 'Auto Optimizer Stats Collection'
NOTE:858852.1 - DBA_AUTOTASK_TASK and DBA_AUTOTASK_CLIENT Shows Different Status For Auto Optimizer Stats Collection