Insights into Statistics, Query Optimization and the Oracle Optimizer
-
19c,
Oracle Optimizer
February 2, 2021
Optimizer Real-time Statistics Parameter in RU 19.10 Onwards
The Oracle Database 21c database initialisation parameter optimizer_real_time_statistics has been backported to Oracle Database 19c RU 10.
The real-time statistics feature is available of some platforms (as specified in the licensing manual). It is now disabled by default in Oracle Database 19c RU10 on-premises Exadata systems, but it can be enabled using the new parameter.
This means that if you are...
- Upgrading an on-premises Exadata platform to Oracle Database RU 19.10 (or greater) from an earlier 19c RU,
- and you want to continue using real-time statistics
...then you must explicitly set optimizer_real_time_statistics = TRUE to override the new default.
The Oracle Database 19c documentation will reflect this change soon.
As per the comment below, this change is not to de-emphasize the feature: it brings it in-line with other Autonomous Database features available on Oracle Exadata. Note also that there are further enhancement to real-time statistics in Oracle Database 21c, adding number-of-distinct-value modelling.
Happy new year.
Thanks for this update
I am wondering why this parameter has been disabled by default in 19.10 and greater?
We moved several engineered databases into 19c, and we started seeing the Note - dynamic statistics used: statistics for conventional DML at the bottom of several of our execution plans. When a query performs poorly in 19c and when the developer sees this Note it rings a bell for him and asked to disable this feature. I started my investigations before disabling this feature and haven’t yet found any inconvenient for using it, as all (roughly) what this feature does, is to update the num_rows dynamically after a conventional load.
I opened the following question to see whether other persons have encountered any issue with this feature
https://community.oracle.com/tech/developers/discussion/4479282/19c-real-time-statistics-any-known-performance-issues-to-share
I think that it hasn’t disabled innocently but Oracle has very probably found that it might introduce few problems that I want to know
Best regards
Mohamed
The feature has needed a public init parameter of course, so when we introduced it we reconsidered what was best in the context of customers upgrading to Oracle Database 19c - since the feedback I get from the field is that we should be conservative in our use of on-by-default because of upgrades. It also puts it in line with the other autonomous database features we have made available on Oracle Exadata.
BTW - we are pushing hard on the development of real-time statistics - with a number-of-distinct-value enhancement in Oracle Database 21c.
I am always keen to look at examples I can test and diag, so anything concrete reported in the discussion I am happy to check out. Create an SR, upload the case, and let me know the SR#. Even if the SR is allowed to close, it provides me with a record and solid evidence of where improvements might be needed. Blog posts are OK too of course, but Support and Development is, at least in part, a machine - so it is always useful to pull the SR levers IMO.
Thanks,
Nigel
I will keep you informed if I open an SR or write a blog article about any issue we might encounter using this 19c feature.
Best regards
Mohamed Houri