19c Feature – 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.