How Oracle 12c Solves Batch‑Load Statistics Problems with Online Gathering

Classic Batch Statistics Issue

In many systems a nightly batch job runs at 3:00 AM, loading a large volume of data (e.g., one million rows) into an intermediate table T1 that is cleared before and after the job. Because Oracle automatically gathers statistics at 10:00 PM when T1 is empty, the collected statistics report zero rows, leading the optimizer to generate sub‑optimal execution plans during the actual load.

Traditional Approaches

Lock Statistics – Capture and lock statistics for the typical data state of the table so the optimizer always uses the same stats regardless of actual row count.

Real‑time Statistics Gathering – After the data surge, explicitly invoke statistics collection within the application to ensure fresh stats before subsequent queries.

Use HINTs – Embed optimizer hints in SQL to force a specific execution plan deemed optimal by the developer.

Drawbacks of Traditional Methods

Locking statistics can become inaccurate (e.g., locked for 1 M rows while only 100 k rows exist) and requires DBA monitoring to unlock and recalculate stats. Real‑time gathering adds extra full‑table scans, consuming resources and inserting non‑transparent logic into the application. HINTs are discouraged by Oracle, are opaque, and cannot adapt to data changes.

Underlying Dev‑Ops Collaboration Challenge

The root of the problem often lies in the disconnect between development teams, who focus on application logic, and DBA teams, who manage statistics collection. When data volume spikes are driven by application behavior, DBAs lack visibility, making coordinated solutions difficult.

Oracle 12c Online Statistics Gathering for Bulk‑Load

Oracle 12c introduces an online statistics gathering feature that automatically collects statistics during the execution of bulk‑load statements, eliminating the need for a separate scan. It applies to two statements:

CREATE TABLE AS SELECT

INSERT INTO … SELECT into an empty table using Direct Path Insert

When these statements run, Oracle gathers table statistics on‑the‑fly, ensuring the optimizer has up‑to‑date information without additional DBA or application changes.

Current Limitations

The feature does not automatically gather index or histogram statistics. DBAs must still manually invoke DBMS_STATS.GATHER_TABLE_STATS to collect those, which partially defeats the goal of a fully hands‑free solution.

Conclusion

Oracle 12c’s online statistics gathering for bulk‑load effectively addresses the classic batch‑processing statistics problem, reducing DBA workload and eliminating the need for application‑level workarounds, though full automation is limited by the lack of automatic index and histogram collection.