How to Disable and Re‑Enable Oracle 11g Automatic Statistics Collection

Background

Oracle Database 11g enables automatic optimizer statistics collection by default during installation. While convenient, the background task runs continuously and may degrade performance on always‑on (24×7) environments, so administrators often need to turn it off.

1. View the automatic statistics collection task and its status

Run the following query to list the autotask clients and see whether the statistics collection task is enabled:

sys@ora11g> select client_name, status from dba_autotask_client;

Typical output:

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

The row with auto optimizer stats collection is the task we want to manage.

2. Disable the automatic statistics collection task

Use the DBMS_AUTO_TASK_ADMIN package to disable the task:

sys@ora11g> exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);

The command returns “PL/SQL procedure successfully completed.” Verify the change:

sys@ora11g> select client_name, status from dba_autotask_client;

Now the status should show DISABLED for the optimizer stats collection client.

3. Re‑enable the automatic statistics collection task

When you need the task again, enable it with:

sys@ora11g> exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);

Confirm the task is back to ENABLED using the same query as before.

4. Query method differences between Oracle 10g and 11g

In Oracle 10g the autotask information is obtained from the scheduler jobs view. Example:

sys@ora10g> col JOB_NAME for a16
sys@ora10g> col PROGRAM_NAME for a18
sys@ora10g> col SCHEDULE_NAME for a20
sys@ora10g> col JOB_CLASS for a20
sys@ora10g> select job_name, program_name, schedule_name, job_class from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';

Result:

JOB_NAME          PROGRAM_NAME          SCHEDULE_NAME          JOB_CLASS
---------------- -------------------- -------------------- --------------------
GATHER_STATS_JOB  GATHER_STATS_PROG    MAINTENANCE_WINDOW_GROUP  AUTO_TASKS_JOB_CLASS

These 10g queries are not valid in 11g; the 11g method uses dba_autotask_client as shown earlier.