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 real-time statistics.
... Note ----- - dynamic statistics used: stats for conventional DML
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.
Example:
SELECT /*+ NO_GATHER_OPTIMIZER_STATISTICS */ ...