Introduction
In version 12c, a new feature was introduced called "online statistics". Online statistics were gathered during certain load statements. For example, CTAS (Create table as select) and direct-path inserts. In 19c, this feature has been extended to include conventional DML as is being called "real-time statistics". The idea behind this feature, is that statistics can go stale between stats jobs, so wouldn’t it be great if the database could capture up to date statistics in real-time.
The key difference between "online statistics" and "real-time" statistics is that bulk load operations gather all necessary statistics whereas, real-time statistics supplement, rather than replace standard statistics. Subsequently, it is critical to continue gathering statistics regularly using your preferred method.
Enabling Real-Time Statistics
A new initialisation parameter has been introduced called OPTIMIZER_REAL_TIME_STATISTICS. When set to true, Oracle will automatically gather statistics during conventional DML operations. The default is FALSE.
Note: Its good to see this feature set to false by default. There have been a few instances recently, where a new feature has been enabled by default causing all kinds of chaos to applications when upgrading
The How
During DML operations Oracle will capture and compute statistics for the key statistics. For example, when adding or deleting a large number of rows to a table, oracle will capture the details and update the row count statistic. Subsequent runs of the query, assuming it is hard parsed, can make use of these new statistics in generating the query plan.
Managing Real-Time Statistics
You can manage and access real-time statistics through PL/SQL, dictionary views, hints and query plans.
PLSQL
The following PLSQL packages now have a real-time statistic parameter
EXPORT_TABLE_STATS and EXPORT_SCHEMA_STATS
IMPORT_TABLE_STATS and IMPORT_SCHEMA_STATS
DELETE_TABLE_STATS and DELETE_SCHEMA_STATS
DIFF_TABLE_STATS_IN_STATTAB
DIFF_TABLE_STATS_IN_HISTORY
Note
The stat_category parameter includes real-time statistics by default.
The REALTIME_STATS value specifies only real-time statistics.
Real-time statistics are always included in the diff packages
Dictionary Views
When real-time statistics are collected, then the notes column on the following views are updated. See below.
DBA_TAB_COL_STATISTICS
DBA_TAB_STATISTICS
Notes
Partition-level statistics are not supported.
Real-time statistics are indicated by STATS_ON_CONVENTIONAL_DML in the notes column.
The ALL_* and USER_* views are also contain the notes column
Hints
To stop real-time statistics being collected when the optimizer paramter is set, the following SLQ hint can be used.
NO_GATHER_OPTIMIZER_STATISTICS
Query Plan
When real-time statistics have been used in a optimizer plan, then the query plan will contain a note. See below
Note ----- - dynamic statistics used: stats for conventional DML
Published 28th February 2023
