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.
In Oracle Database 12c Release 1, the database parameter optimizer_adaptive_features controls all of the adaptive features like this:
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:
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
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
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.
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.
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.