19c Feature – Real-Time Statistics

Real-Time Statistics

Oracle Database 12c introduced online statistics gathering for CREATE TABLE AS SELECT statements and direct-path inserts.

Oracle Database 19c introduces real-time statistics, which extend online support to conventional DML statements.
Because statistics can go stale between DBMS_STATS jobs, real-time statistics helps the optimizer generate more optimal plans.

Checking for real-time statistics gathering

1) Generate explain plan/execution plan for the operation.
The Note field shows that the query used the real-time statistics.

2) If real-time statistics gathering is used, then the view “DBA_TAB_COL_STATISTICS” and “DBA_TAB_STATISTICS”
 will contain STATS_ON_CONVENTIONAL_DML in the NOTES column and SHARED in the SCOPE column.

Disabling real-time statistics gathering

The NO_GATHER_OPTIMIZER_STATISTICS hint prevents the collection of real-time statistics.