Oracle DBA

In Oracle Database 19c, a powerful new feature arrives: Real-Time Statistics. Its main goal is to reduce the chance that the optimizer makes poor decisions because of stale statistics — especially in scenarios where data changes rapidly between scheduled statistics gathering cycles.

Real-time statistics build on earlier features like online statistics gathering for bulk loads (introduced in Oracle 12.1). While 12.1 allowed statistics to be gathered during certain bulk operations (CTAS, direct loads), 19c’s real-time statistics go further: they allow stats to be updated even during conventional DML operations (in supported environments).

But note: real-time statistics is only supported on Engineered Systems (like Exadata or Exadata Cloud Service). There is an unsupported workaround by enabling the hidden parameter _exadata_feature_on for testing — but this should never be used in a production environment because it violates licensing.

---

Prerequisites & Enabling for Testing

Because it’s restricted to Engineered Systems, if you want to test the feature in a non-Engineered environment, you’d need to enable:

ALTER SYSTEM SET "_exadata_feature_on" = TRUE SCOPE = SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

After testing, you must reset it:

ALTER SYSTEM RESET "_exadata_feature_on" SCOPE = SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

Again: don’t do this in production as it’s outside the licensing boundary.

---

Setup & Testing Environment

To experiment, the author does the following:

1. Create a test user with privileges: CREATE SESSION, CREATE TABLE, plus extra grants (CREATE PROCEDURE, SELECT_CATALOG_ROLE, ANALYZE ANY) so that the user can run DBMS_STATS, use DBMS_XPLAN.DISPLAY_CURSOR, and flush monitoring info.
2. Create a test table TAB1 ( id NUMBER, description VARCHAR2(50) ) under that user.
3. Write a helper procedure reset_tab1 that truncates and reloads 1,000 rows (via APPEND path insert) and then gathers statistics.
4. Before each test, call reset_tab1, then flush monitoring info using: EXEC DBMS_STATS.flush_database_monitoring_info; This ensures that metadata from the *_TAB_MODIFICATIONS, *_TAB_STATISTICS, and *_IND_STATISTICS views is up to date in the data dictionary.
5. Use queries like: SELECT table_name, num_rows FROM user_tables WHERE table_name = 'TAB1'; SELECT table_name, num_rows, notes FROM user_tab_statistics WHERE table_name = 'TAB1'; SELECT column_name, UTL_RAW.CAST_TO_NUMBER(low_value) AS low_value, ... FROM user_tab_col_statistics WHERE table_name = 'TAB1' AND column_name = 'ID'; Also, display the execution plan using DBMS_XPLAN.DISPLAY_CURSOR.

These baseline queries allow comparing before vs after each DML test, to see how real-time stats impact the statistics metadata and optimizer.

---

Behavior Under Different Types of DML

The author runs various types of DML operations and observes whether real-time stats get triggered, and how the optimizer uses them. Below is a summary of the observations and examples.

1. Conventional Path INSERT … SELECT

• After resetting, they insert 1000 new rows via conventional path: INSERT INTO tab1 SELECT level+1000 AS id, 'Description …' FROM dual CONNECT BY level <= 1000; COMMIT;
• In the execution plan for the INSERT, an “OPTIMIZER STATISTICS GATHERING” operation appears. That means the optimizer triggers statistics collection during the DML.
• After flushing, in USER_TAB_STATISTICS, there’s an extra row with NOTES = 'STATS_ON_CONVENTIONAL_DML' and NUM_ROWS = 2000 (the projected new row count).
• In USER_TAB_COL_STATISTICS, similarly, a new row with NOTES = 'STATS_ON_CONVENTIONAL_DML' appears, containing sample statistics (e.g. sample size = 11, and estimated HIGH_VALUE = 1885) even though actual high value is 2000.
• When running a query like SELECT MAX(id) FROM tab1;, the optimizer uses these real-time statistics (shown in plan as “dynamic statistics used: statistics for conventional DML”) and estimates the full scan cardinality = 2000 rows.

Thus, for conventional path INSERT … SELECT, real-time stats are generated and used.

2. Direct Path INSERT … SELECT (with APPEND hint)

• Reset again, then: INSERT /*+ APPEND */ INTO tab1 SELECT …; -- adds 1000 rows COMMIT;
• After flushing, no new row appears in USER_TAB_STATISTICS and no real-time stats in USER_TAB_COL_STATISTICS. In short, real-time stats are not triggered in this case.

So direct path inserts (bulk append) do not trigger real-time stats.

3. Conventional Path Single INSERT

• Reset, then: INSERT INTO tab1 VALUES (1001, 'Description …'); COMMIT;
• After flushing, again no real-time statistics are triggered.

Single row conventional inserts generally do not generate real-time stats.

4. Conventional Path INSERT in a Loop (batch insert)

• Reset, then insert 1000 rows in a PL/SQL loop (one commit after all inserts). After flush:
  • USER_TAB_STATISTICS and USER_TAB_COL_STATISTICS show extra rows, with NOTES = 'STATS_ON_CONVENTIONAL_DML'.
  • For the column, sample size is small (e.g. 6), and HIGH_VALUE estimate ~1998.
• If commits are done per row instead of at the end, behavior is similar—real-time stats triggered, though estimates may vary slightly.

So looping conventional inserts in bulk can trigger real-time statistics.

5. Bulk-Bind INSERT

• Reset, then use PL/SQL bulk bind: DECLARE TYPE t_tab IS TABLE OF tab1%ROWTYPE; l_tab t_tab; BEGIN SELECT … BULK COLLECT INTO l_tab …; FORALL i IN l_tab.first..l_tab.last INSERT INTO tab1 VALUES l_tab(i); COMMIT; END;
• After flush, real-time statistics are triggered. USER_TAB_STATISTICS and USER_TAB_COL_STATISTICS get new rows with NOTES = 'STATS_ON_CONVENTIONAL_DML'. Sample size ~8, HIGH_VALUE ~1971.

So bulk binds (PL/SQL arrays) qualify for real-time stats.

6. UPDATE

• Reset, then: UPDATE tab1 SET id = id + 10000; COMMIT;
• Flush, and in USER_TAB_COL_STATISTICS, a new row appears with real-time statistics, sampling the updated values. For example, HIGH_VALUE estimated ~10887 though actual is 11000.
• Note: LOW_VALUE remains unchanged (1), even though logically it should have shifted +10000. That’s a limitation of real-time stats for updates.

7. DELETE

• Reset, then: DELETE FROM tab1; COMMIT;
• Flush, and no real-time statistics are triggered. USER_TAB_STATISTICS and USER_TAB_COL_STATISTICS remain unchanged.
• The author notes he’s never seen a delete trigger real-time stats in his tests.

8. Partitioned Tables

• The author tests with a partitioned table TAB2 partitioned by created_date. After inserting 1000 rows (all falling into one partition), flushing, the real-time stats show up at the global (table) level, not per partition. The partition-level views show zero, while the global statistics row gets NOTES = 'STATS_ON_CONVENTIONAL_DML'.
• Splitting the insert explicitly into a particular partition also doesn’t change this: estimates are collected globally, and partition-level stats remain unchanged.
• The estimation tends to be less accurate in this partitioned setting.

Thus, real-time statistics behave less precisely with partitions—they collect at table-level, not partition-level.

---

Controlling Real-Time Statistics

Hint to suppress real-time stats: NO_GATHER_OPTIMIZER_STATISTICS

You can include the hint to prevent statistics from being triggered during the DML:

INSERT /*+ NO_GATHER_OPTIMIZER_STATISTICS */ INTO tab1
  SELECT …;
COMMIT;

In tests, using this hint results in no real-time statistics being generated.

Hidden Parameters

There are several underscore (hidden) parameters associated with this feature:

• _optimizer_gather_stats_on_conventional_config
• _optimizer_gather_stats_on_conventional_dml (defaults to TRUE)
• _optimizer_stats_on_conventional_dml_sample_rate
• _optimizer_use_stats_on_conventional_config
• _optimizer_use_stats_on_conventional_dml

You can disable gathering or usage of real-time stats via these:

• To stop gathering: ALTER SESSION SET "_optimizer_gather_stats_on_conventional_dml" = FALSE;
• To stop using them (after collection): ALTER SESSION SET "_optimizer_use_stats_on_conventional_dml" = FALSE;

But these are hidden, unsupported parameters—use with caution, only in controlled environments.

---

Import / Export Real-Time Statistics

Real-time statistics are integrated into Oracle’s normal statistics export/import infrastructure.

• Create a stats table: BEGIN DBMS_STATS.create_stat_table('TESTUSER1','STATS_TABLE'); END;
• Export both object and real-time stats: DBMS_STATS.import_schema_stats( ownname => 'TESTUSER1', stattab => 'STATS_TABLE', statown => 'TESTUSER1', stat_category => 'OBJECT_STATS,REALTIME_STATS' );
• Later, on another database, you can import them similarly, using force => TRUE, and then drop the stats table.

This means you can carry over real-time stats across environments along with regular statistics.

---

Thoughts, Caveats & Use Cases

• Real-time statistics is only available on Engineered Systems (e.g. Exadata) under normal licensing.
• The feature doesn’t produce perfect statistics, but it makes them more representative of the current data, especially when there’s a lot of change between scheduled gather runs.
• Once you explicitly gather statistics via DBMS_STATS, the real-time statistics are wiped out (they become redundant).
• The author speculates one of the most useful use cases is for load tables in ETL workflows (where large inserts/deletes happen between statistics runs).
• Some DBAs may worry about plan instability or unwanted optimizer behavior. If so, you can disable or bypass real-time statistics via hidden parameters or hints—but that moves you back toward “stale stats” risk.