X

Insights into Statistics, Query Optimization and the Oracle Optimizer

Optimizer Real-time Statistics Parameter in RU 19.10 Onwards

Nigel Bayliss
Product Manager

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. 

Join the discussion

Comments ( 3 )
  • Mohamed Houri Monday, February 8, 2021
    Hi Nigel,
    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
  • Nigel Bayliss Monday, February 8, 2021
    Hi Mohamed - Happy New Year - good to hear from you.

    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
  • Mohamed Houri Monday, February 8, 2021
    Hi 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
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.