Mastering Oracle Statistics Collection: When, How, and Optimization Tips

When to Collect Statistics

Accurate statistics enable the optimizer to choose the best execution plan; they need not be the newest but must be representative of the data distribution.

Disabling Automatic Statistics Collection

Oracle automatically gathers stats for objects with missing or stale information during predefined maintenance windows (weekdays 22:00‑02:00, weekends 06:00‑02:00). To disable this job, run:

sys@JINGYONG> begin
  dbms_auto_task_admin.disable(
    client_name=>'auto optimizer stats collection',
    operation=>null,
    window_name=>null);
end;
/

PL/SQL procedure completed successfully.

Manual Statistics Collection

When manually maintaining stats, decide the timing based on stale stats, automatic job schedules, or data load times. Avoid frequent re‑collection if data has not changed significantly. For partition‑exchange loads, collect stats after the exchange. Use user_tab_statistics.stale_stats or user_tab_modifications to detect changes, and refresh monitoring info with dbms_stats.flush_database_monitoring_info if needed.

Preventing Out‑of‑Range Conditions

If users query newly inserted rows before stats are refreshed, the optimizer may produce sub‑optimal plans, especially when predicate values fall outside the min/max column stats. For partitioned tables, use dbms_stats.copy_table_stats (available from Oracle 10.2.0.4) to copy stats from an existing partition to a new one, setting flags=>8 if global updates are required. For non‑partitioned tables, dbms_stats.set_column_stats can manually set column maximums, though it does not replace real stats.

Improving Collection Efficiency

As data volume grows and maintenance windows shrink, Oracle offers parallel methods:

Internal object parallelism

External object parallelism

Combination of both

Parallel Statistics Collection

Internal Object Parallelism is controlled by the degree parameter of dbms_stats.gather_*_stats. Set degree to a specific number or auto_degree to let Oracle decide based on object size. For partitioned tables, each partition can use multiple parallel servers, but partitions are processed sequentially.

External Object Parallelism (Oracle 11.2.0.2+) is enabled via the

global_stats_gathering_preference

concurrent

flag. Oracle creates multiple jobs using the job scheduler and advanced queue groups. The maximum number of parallel jobs is governed by job_queue_processes (default 1000, but a practical value is twice the CPU count per RAC node). Set this at the system level with alter system, not at the session level.

Combination of internal and external parallelism further reduces collection time. Disable the parallel_adaptive_multi_user initialization parameter at the system level to prevent parallel jobs from being downgraded to serial execution:

sys@JINGYONG> alter system set parallel_adaptive_multi_user=false;

System altered.

Incremental Statistics

For partitioned tables, enable the incremental preference (set to true) so that dbms_stats.gather_*_stats with granularity=>global and estimate_percent=>auto_sample_size gathers global stats by scanning only modified partitions. The global stats are derived from per‑partition summaries, eliminating full‑table scans. Note that incremental stats are not aggregated from sub‑partition stats.

When Not to Collect Statistics

Collecting stats can be costly or impractical in certain scenarios:

Volatile tables : Tables whose row count fluctuates dramatically (e.g., order queues). Lock representative stats during the day and rely on dynamic sampling for ad‑hoc queries.

Global temporary tables : Statistics are session‑private; collect and lock a representative set in one session if the data pattern is consistent across sessions. Automatic jobs never gather these.

Intermediate work tables : Used once in ELT or complex transactions; the cost of collecting outweighs benefits. Prefer dynamic sampling and lock stats to prevent automatic collection.

Collecting Other Types of Statistics

All database objects, including data‑dictionary tables (SYS, SYSTEM) and X$ tables accessed via V$ performance views, require statistics for the cost‑based optimizer. Enable automatic collection for dictionary stats, or manually set the global preference:

sys@JINGYONG> begin
  dbms_stats.set_global_prefs('AUTOSTATS_TARGET','ORACLE');
end;
/

PL/SQL procedure completed successfully.

Fixed Object Statistics

Automatic jobs do not gather statistics for fixed objects (e.g., X$ tables). Missing stats cause the optimizer to fall back on default values, which may lead to poor plans. Manually collect them with dbms_stats.gather_fixed_objects_stats. In large systems, prioritize critical fixed objects such as control file contents, session‑based data (v$session, v$access), and workload data (v$sql, v$sql_plan).

After major database or application upgrades, or configuration changes (e.g., increased SGA size), re‑collect fixed object stats to keep system statistics accurate. System statistics provide hardware‑related cost information (CPU speed, I/O performance) and are enabled by default with representative defaults.

Conclusion

Accurate object and system statistics are essential for the optimizer to evaluate execution‑plan costs correctly. By combining automatic jobs with manual techniques—such as disabling unwanted jobs, using parallel and incremental collection, and handling special table types—DBAs can maintain a reliable statistics set that ensures optimal query performance while minimizing overhead.