Online Stats vs Real-time Stats
Are you aware of these new terminologies, real-time stats and online stats? Or are these new to you? Maybe you thought they are the same thing, just different DBAs use these names interchangeably. Well, they are not the same thing!
Online Statistics gathering was introduced in 12c and Real-time Statistics gathering was introduced in 19c.
Online Stats were introduced for CREATE TABLE AS SELECT statements aka CTAS method, and for direct-path inserts into empty tables or partitions, insert /+APPEND */ as select aka IAS.
The purpose of online stats was to populate the stats right away for the table, and to avoid the optimizer being misled by stale stats or no stats at all.
These online stats are accurate statistics, and are available right away after the CTAS or IAS method is used.
If you only run regular inserts, online stats are not gathered by the database, you need to manually re-gather stats using the dbms_stats package.
An important fact to notice is that the table you are inserting into, has to be empty, meaning, it cannot have any blocks allocated to it. Let me explain below! If your table has 1000 rows, you delete these rows, and then run a direct path insert, online stats will not be gathered by the database.
However, if your table has 1000 rows, and you truncate the table (meaning that not only you delete the rows but also the high watermark gets reset), then you run a bulk load insert, online stats will be gathered by the database.
The stats that are gathered by the database, are base table and column stats, no histograms.
Ultimately the purpose of online stats is to improve performance.
Real-time Stats were introduced as a bonus to the online stats, for conventional DML statements (aka regular inserts, updates, deletes). The existing stats on tables can become stale between the times you gather statistics, thus Oracle introduced real time stats to help the optimizer create better plans. Real-time stats are not available right away in the data dictionary. You can use dbms_stats.flush_database_monitoring_info to flush the stats from memory to the data dictionary. The purpose of the real time statistics is to improve performance.
Both online and real time statistics are here as a bonus to regular statistics, and not to replace regular stats. It is important that you keep gathering regular statistics on your tables and indexes, either through the gather stats job provided by Oracle, or your custom method.
A major difference between Real-time and Online statistics, as of this writing is the licensing. Online stats are available for all editions, same as regular stats, however real-time stats are only available for Enterprise Edition on Engineered Systems like Exadata or Exadata Cloud Services.
If you are keen on testing the Real-time stats feature out, and who would not be?, then there is a workaround. You can set the _exadata_feature_on=true init.ora parameter in a test database, and play with the feature. Remember to only use this in your sandbox environment or your laptop, as you might be breaking your licensing agreement, and set it back to false, right after you are done.
Online stats can be viewed in the same columns as the stats you gather with the dbms_stats package, ie. NUM_ROWS column in the dba_tables view.
Real-time stats can be viewed in the NOTES column of the same views, ie. notes in the dba_tables view. What you will see in the notes column is STATS_ON_CONVENTIONAL_DML entry.
Putting it all together below in a mind map! Feel free to print it and share it!
This is Part 1 of a 3 part mini-series! join me next week to dive into the online-stats with examples and details!
If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!
If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get the best price, only $18.99 CAD!
