X

Insights into Statistics, Query Optimization and the Oracle Optimizer

  • October 12, 2016

Optimizer Adaptive Features in Oracle Database 12c Release 2

Nigel Bayliss
Product Manager

Introduction

In Oracle Database 12c Release 2 we have changed the way optimizer adaptive features can be controlled. In this post, I'll present what has changed and give you guidance on how to you can choose what settings to use.

These changes are also relevant for Oracle Database 12c Release 1. If you want to know more about that, there's information at end of this post.

What’s Changed

In Oracle Database 12c Release 1, the database parameter optimizer_adaptive_features controls all of the adaptive features like this:

Adaptive features in Oracle Database 12c Release 1

In Oracle Database 12c Release 2, this parameter has been made obsolete and replaced with two new parameters that control adaptive plans and adaptive statistics separately, like this:

Adaptive features in Exadata Express Cloud Service

What are the Optimizer Adaptive Features?

Adaptive plans and adaptive statistics are umbrella terms for a number of optimizer features. The following tables summarize them.

These features are enabled by default:

optimizer_adaptive_plans default TRUE

Description

Nested loop join/Hash join selection

The optimizer chooses between nested loops or hash joins at query runtime.

Adaptive parallel distribution method

The parallel distribution method is determined at runtime.

Star transformation bitmap pruning

Certain bitmap indexes may be removed from the SQL execution plan at runtime if selectivity is significantly poorer than the estimate.

 

These features are disabled by default:

optimizer_adaptive_statistics default FALSE

Description

SQL plan directives

SQL plan directives are created and used to adapt SQL execution plans.

Statistics feedback for joins

Cardinality from table joins is used to improve SQL execution plans.

Performance feedback

Improves the degree of parallelism chosen when PARALLEL_DEGREE_POLICY is set to ADAPTIVE

Adaptive dynamic sampling for parallel execution

Dynamic statistics are gathered for certain parallel queries to improve cardinality estimates. The sample size is determined automatically.

 

SQL Plan Directives

Setting optimizer_adaptive_statistics to FALSE does not disable the creation of SQL plan directives. Instead, it prevents them from being used in conjunction with dynamic statistics to influence SQL execution plans.

Statistics Feedback

Setting optimizer_adaptive_statistics to FALSE disables Oracle Database 12c statistics feedback for joins.Note that statistics feedback for single table cardinality (introduced in Oracle Database 11g as cardinality feedback) is always enabled.

Adaptive Dynamic Sampling

If optimizer_adaptive_statistics is set to TRUE then dynamic statistics using an adaptive sample size will be used in response to SQL plan directives. In addition, adaptive dynamic sampling will be used for certain parallel queries if optimizer_dynamic_sampling is 2 (the default value). If optimizer_adaptive_statistics is set to FALSE, then adaptive dynamic sampling will not be used in these scenarios.

Changes to Auto Creation of Column Group Statistics

In Oracle Database 12c Release 1, SQL Plan Directives trigger the creation of column group statistics when statistics are gathered. You can see the extended statistics that were created automatically using a query like this:

select owner,
       table_name,
       extension,
       extension_name
from dba_stat_extensions
where creator = 'SYSTEM'
order by owner,table_name,extension_name;

We received feedback that some DBAs wanted to be able to control this feature, so in Oracle Database 12c Release 2 automatic column group statistics creation is controlled by a DBMS_STATS preference AUTO_STAT_EXTENSIONS. By default, the preference is OFF so that extended statistics are not created automatically. You can re-enable this feature using:

EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_STAT_EXTENSIONS','ON')

Choosing What Settings to Use

The defaults have been chosen on the basis of what works best for widest range of workloads, so it is recommended that you start with these for most systems. If you’re already using all the adaptive features in Oracle Database 12c Release 1, and you want the same in Oracle Database 12c Release 2, then override the defaults by setting the parameter optimizer_adaptive_statistics to TRUE and the preference AUTO_STAT_EXTENSIONS preference to ON.

We have received very positive feedback from customer with respect to adaptive plans. It has proved to be very able at improving system performance for a huge range of workloads. Wrongly choosing a nested loop join rather than a hash (or visa-versa) will often cause a query to experience very poor performance. Adaptive plans give the optimizer a way of avoiding this mistake; the final plan being based on the number of rows actually seen rather than an estimate that was made prior to execution. In other words, adapting the execution plan at run time is likely to benefit all types of systems so we elected to set the default of optimizer_adaptive_plans to TRUE. Of course, if you prefer not to use it initially then you can set the parameter to FALSE.

Adaptive statistics features are more useful in environments where queries, data distributions and schemas are very complex. In systems like this, where query execution times are longer, it makes especially good sense for the optimizer to invest time and resources to improve the overall quality of the execution plans. The parameter optimizer_adaptive_statistics can be set to TRUE in these environments.

What about Oracle Database 12c Release 1?

Oracle has made improvements to the way adaptive features are controlled. If you are currently using or upgrading to Oracle Database 12c Release 1, then check out the previous post. It shows you where you can find out more information on how to control adaptive features in this release.

If you need to find out more about the adaptive features, there is a white paper that covers Oracle Database 12c Release 1 and Release 2.

 

 

Join the discussion

Comments ( 8 )
  • Tyler Saturday, November 26, 2016

    Nigel - currently the extra dynamic sampling kicks in for non-parallel queries. Your paragraph on that seems to indicate that now, even if optimizer_adaptive_statistics were enabled, it would only kick in for parallel queries. Has the use of extra sampling (with optimizer_dynamic_sampling being 2) for non-parallel queries been totally scrapped, no matter how you set these two new parameters? (Which would be great -- just trying to get a clear picture of the changes.) Thank you!


  • NigelBayliss Monday, November 28, 2016

    Hi Tyler,

    Adaptive dynamic sampling will kick in for serial queries in response to SQL plan directives if you enable optimizer adaptive statistics (assuming that you have not disabled dynamic sampling). In other words, if you enable adaptive statistics in Oracle Database Release 2, dynamic sampling will be used as it was in Oracle Database Release 1. I'll update the text in the post.

    Thanks,

    Nigel.


  • Foued Thursday, March 2, 2017

    Thank you for this post.


  • goran Monday, June 19, 2017
    Thanks for good wrap-up of Adaptive features changes in 12.2
  • Werner Monday, March 12, 2018
    Hi Nigel,

    today i applied the 180116 (db = p27162953, ojvm = p27162998) patch set for Oracle 12.1.0.2. In my spfile the parameter optimizer_adaptive_feature was set false. When i try to startup the database instances it caused an error because of this parameter. After i switched back to init.ora file and removed the parameter i was able to restart the instance.
    No readme or note containes a hint, what to do when this parameter is set!!!!!

    Best regards
    Werner
  • Nigel Bayliss Monday, March 12, 2018
    Hi Werner,
    It sounds like you have set the "_fix_control" parameter in your spfile/pfile. This will disable the optimizer_adaptive_features parameter and will give you the ability to set the new parameters we made available in Oracle Database 12c Release 2: optimizer_adaptive_plans and optimizer_adaptive_statistics. Do not set the fix control unless you definitely want to enable the new adaptive optimizer parameter settings we made available in Oracle Database 12c Release 2. It is important to realize that execution plans can change if you set the fix control, so if you want to apply the bundle patch and keep things are they are then do not set the fix control parameter. Just remove the "_fix_control" setting you added to your spfile/pfile and you will get your optimizer_adaptive_features parameter back.

    Also, see the MOS note referenced in Mike's blog post:

    https://mikedietrichde.com/2017/10/19/fixes-for-adaptive-features-are-included-in-oracle-12-1-0-2-october-2017-bp/

    Regards,
    Nigel.
  • Paul De Smedt Thursday, July 12, 2018
    Dear Nigel,

    On one of our databases (Oracle 12.2.0.1 with PSU of April 2018) we discovered that a query may return wrong results.

    We logged a support request at Oracle Support (SR 3-17487236651 : Query returns wrong result.).
    It seems that the problem was caused by OPTIMIZER ADAPTIVE PLAN = TRUE.
    The solution was to put that parameter on FALSE. A patch (Bug 25309116 - WRONG RESULT USING OPTIMIZER ADAPTIVE PLAN ) did not work.

    There is one serious consideration which I make.
    This option (optimizer_adaptive_plans) is by default TRUE (in version 12.2.0.1) and it can lead to wrong results.
    Not only for this query, but - who knows - also for other queries. And there are thousands... or more queries which run daily here.
    Should Oracle or - at least we - not put that option by default on FALSE on every database (version 12)
    because we can no longer be sure about the results of a query?

    The final answer of support was: "Yes you can set the adaptive plans to false on every instance."

    So, "you can". However, you are not obliged (...if you can live with the fact that a query on that version can return wrong results).

    Does there exist information that setting OPTIMIZER ADAPTIVE PLAN = TRUE can be dangerous (not returning the correct result) in version 12.2.0.1?
    Or do you think that it is not dangerous?

    Kind regards,

    Paul De Smedt
  • Nigel Bayliss Thursday, July 12, 2018
    Hi Paul,
    This is an interesting question and it's hard to convey a 'perfect' tailored answer in a blog post comment but I will try.

    Fortunately wrong results are incredibly rare and, because we run very large regression test, they are overwhelmingly likely to be confined to very specific corner-cases.

    Optimizer problems are sometimes unrelated to the original problem description in a bug and sometimes they are not even related to workarounds you find searching MOS or the Internet. For example, imagine the case where a query has a particular shape that has a wrong-results issue. Optimizer parameter changes can (for example) affect query transformations, the shape of a queries (e.g. from nested loop to hash join) and the way rows are processed. If we use an optimizer parameter to turn a feature off and the problem goes away, we cannot simply assume that the feature is the cause. It may have changed the query shape and avoided the problem as a side-effect.

    Support are asking you to try things and it looks like they are not on the right track yet. Often I see SRs with suggestions to try different optimizer parameters (sometimes hidden parameters), and sometimes they work. However, in general, they should be treated as experiments. Until confirmed otherwise they might not be *good* workarounds, and they do not necessarily identify a cause. I ask for you to be patient and a little careful about jumping to too many conclusion during the investigative process. If you decide to take OAP=FALSE now, it might be a hammer to crack a nut and it might not be the right choice. The bug you mention is a very rare corner-case and is (apparently) not relevant in your case.

    Regards,
    Nigel
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services