Enable or Disable Oracle GATHER_STATS_JOB for Automatic Statistics Collection using dbms_scheduler

calendar Oct 1, 2025 / · 4 min read · oracle database dbms scheduler statistics gathering performance tuning database administration pl/sql optimizer statistics maintenance tasks dbms_scheduler  ·
Share on: linkedin copy

Enable or Disable Oracle GATHER_STATS_JOB for Automatic Statistics Collection

The GATHER_STATS_JOB is a critical Oracle Database scheduled job that automatically collects optimizer statistics to ensure the database query optimizer generates efficient execution plans. This job is enabled by default when creating a new Oracle database and runs during predefined maintenance windows.

Purpose

The GATHER_STATS_JOB maintains optimal query performance by automatically gathering optimizer statistics for database objects that have missing or stale statistics. The job executes the internal procedure DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC, which prioritizes objects that most urgently need updated statistics. This ensures critical statistics are collected before the maintenance window closes.

Query

Enable/Disable automatic snapshots. Note: This job is enabled by default

1exec dbms_scheduler.enable('GATHER_STATS_JOB')
2
3-- and to disable...
4
5exec dbms_scheduler.disable('GATHER_STATS_JOB')

Breakdown of Code

The code demonstrates two essential DBMS_SCHEDULER procedures for controlling the GATHER_STATS_JOB:

Enabling the Job: The command exec dbms_scheduler.enable('GATHER_STATS_JOB') activates the automatic statistics gathering job. When enabled, the job runs during maintenance windows, which default to 10 PM to 6 AM on weekdays and all day on weekends. The job continues until completion, even if it exceeds the allocated maintenance window time.

Disabling the Job: The command exec dbms_scheduler.disable('GATHER_STATS_JOB') temporarily stops automatic statistics collection. This is useful when performing database migrations, installations, or when implementing custom statistics gathering procedures.

Key Points

Default Configuration: The GATHER_STATS_JOB is automatically created and enabled during database creation or upgrades. Statistics are gathered hourly by default in Oracle Database 10g and later versions.

Maintenance Windows: The job operates within scheduled maintenance windows that can be customized using Oracle Enterprise Manager or DBMS_SCHEDULER procedures. Organizations can adjust window timing to avoid conflicts with bulk load operations or peak usage periods.

Job Verification: Database administrators can verify the job status by querying the DBA_SCHEDULER_JOBS view with SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB'. This query displays the job's enabled status, schedule, and execution history.

Alternative Methods: In Oracle 11g and later, statistics gathering can also be controlled using DBMS_AUTO_TASK_ADMIN.DISABLE and DBMS_AUTO_TASK_ADMIN.ENABLE procedures for the 'auto optimizer stats collection' client.

Insights and Best Practices

When to Disable: Oracle strongly recommends keeping automatic statistics gathering enabled because accurate statistics are critical for optimal query execution plans. However, temporary disabling may be necessary during specific operations like Oracle Enterprise Manager installations, database migrations, or when implementing custom statistics procedures.

Stale Statistics Detection: The automatic job identifies stale statistics when objects have been modified by more than 10% of their rows. This threshold can be adjusted per table using DBMS_STATS.SET_TABLE_PREFS to set the STALE_PERCENT parameter.

Prioritization Strategy: Unlike manual statistics gathering, the automatic job intelligently prioritizes database objects. Objects with no statistics or very old statistics are processed first, ensuring the most critical updates occur before the maintenance window ends.

Resource Management: The GATHER_STATS_JOB respects all preferences set at the global, schema, or table level using DBMS_STATS preference procedures. This allows fine-grained control over statistics gathering behavior without disabling the automatic job.

Dictionary Statistics: If disabling the automatic job for application schemas, consider leaving it enabled for Oracle dictionary tables by setting AUTOSTATS_TARGET to 'ORACLE' instead of 'AUTO' using DBMS_STATS.SET_GLOBAL_PREFS.

Performance Considerations

The GATHER_STATS_JOB improves database performance by ensuring the Cost-Based Optimizer has current information for generating execution plans. High-frequency statistics collection can be configured for environments requiring more frequent updates, with intervals as short as 60 seconds in modern Oracle versions. However, administrators must balance the benefits of current statistics against the processing overhead of frequent collection.

Common Misconceptions

AWR Snapshots: A widespread misconception is that GATHER_STATS_JOB collects Automatic Workload Repository snapshots. In reality, AWR snapshot collection is performed by the MMON background process, not by GATHER_STATS_JOB. The GATHER_STATS_JOB solely focuses on optimizer statistics for query execution plan generation.

References

Posts in this series