automatic collection of statistics in 11G

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

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'