This was first published on https://blog.dbi-services.com/oracle-12cr2-optimizer-adaptive-statistics (2016-09-20)
Republishing here for new followers. The content is related to the the versions available at the publication date
When 12.1 came out, the major issue we encountered after migration was related to the new adaptive features of the optimizer: automatic reoptimization, SQL Plan Directives and the resulting dynamic sampling. Of course, Oracle product managers listen to feedbacks, ensure to provide workarounds or fixes and make things better for next release. Let’s see what has been announce on this topic for 12.2
Unfortunately, in most case, when a problem is encountered people put priority on it only until the issue appears as “solved”, and then close the problem. However for stability and reliability, this is not enough. There are two phases in problem resolution:
If you stop after the first point, you don’t have a solution. You have a workaround, and two things will happen soon or later:
So, when you upgrade to 12c from 11.2.0.4 for example, the easiest way to fix a regression is to set optimizer_features_enable=’11.2.0.4′. But when you do that, you did only the first step. Nothing is fixed. Actually, when doing that you didn’t even finish your upgrade job. I’ve already blogged about how to fix an adaptive statistics issue and keep the fix in the same scope as the problem by:
so there are many combinations that depend on your context.
It’s easy to disable all new adaptive features and claim that the 12c optimizer is full of bugs. However there are two things that I’m sure:
And they do something very nice: each individual feature can be enabled or disabled by a parameter. So there are lot of parameters. Some of them are undocumented just because at release time they don’t think they should have a different value other than default, except special situations guided by the support. But one set of default value cannot fit all environments. Are you doing OLTP or BI? OLTP likes stability, BI likes adaptive optimization. And probably your database has both OLTP and reporting workloads, and maybe at the same time. This is the first reason why one set of parameter cannot fit all. There’s another one you should think about before blaming the optimizer. Maybe they bring features that helps to make good applications even better. Maybe the set of default value is not chosen to fit the worst application design… Let’s come back to the OLTP vs. BI example. Adaptive features are enabled by default for BI. You may spend more time on parsing in order to get the optimal execution plan. But then you complain that your OLTP spends more time on parsing… But you are not supposed to parse on OLTP! The overhead of adaptive features should not be a blocking problem if you parse your queries once and then execute them.
I tend to blog on encountered issues rather that on thinks that do not raise any problem. Because my job is to solve problems rather that stay looking at what works well. I’ve encountered a lot of issues with those adaptive features. But I have seen lot of application that had no problem at all when upgraded to 12c. When you disable the adaptive features, do you workaround an optimizer problem, or your application design problem?
In 12.1 only optimizer_adaptive_features is documented but it disables too many features. You may want to disable SQL Plan Directive and their consequences. But you probably want to keep adaptive plans as they are awesome and less prone of bad side effects. So in 12.2 this parameter has been split into two parameters: OPTIMIZER_ADAPTIVE_PLANS and OPTIMIZER_ADAPTIVE_STATISTICS
In addition to that, only OPTIMIZER_ADAPTIVE_PLANS is set to true by default. OPTIMIZER_ADAPTIVE_STATISTICS is false so by default you will not have the following 12c features: SQL Plan Directives, Statistics feedback, Performance feedback, Adaptive dynamic sampling for parallel query.
Here are the features enabled by OPTIMIZER_ADAPTIVE_PLANS:
optimizer_adaptive_plans | TRUE | FALSE |
---|---|---|
_optimizer_nlj_hj_adaptive_join | TRUE | FALSE |
_px_adaptive_dist_method | CHOOSE | OFF |
_optimizer_strans_adaptive_pruning | TRUE | FALSE |
If you want more information about them, I’ve written articles about adaptive join, adaptive PX distribution and adaptive star transformation bitmap pruning
Here are the features enabled by OPTIMIZER_ADAPTIVE_STATISTICS:
optimizer_adaptive_statistics | FALSE | TRUE |
---|---|---|
_optimizer_dsdir_usage_control | 0 | 126 |
_optimizer_use_feedback_for_join | FALSE | TRUE |
_optimizer_ads_for_pq | FALSE | TRUE |
As you can see there is no “_optimizer_gather_feedback” here so the cardinality feedback coming from 11g is still there when you disable adaptive statistics. You may like it or not, and maybe want to disable cardinality feedback as well if you don’t want plans that change.
What if you already have some SPDs? as “_optimizer_dsdir_usage_control” is 0 they will not be used. And they will be dropped automatically after 53 weeks of no usage.
What a surprise they disabled sql plan directives by default. Good move!
Thanks Franck – the optimizer team had to cover several big themes: 1.) row and column store in full harmony, 2.) adaptive plans and stats, 3.) sharding. They really know their stuff very well and provided us with the tools we’ve been asking for. As usual: caution with all *optimizer” parameters. Plan stability is nice but not all we wanted. Now we’ve got more choices.
Franck, I chatted with some of the CBO guys at OOW and learned some things I haven’t seen documented elsewhere. I have blogged about this here: https://hkpatora.com/2016/09/25/upcoming-adaptive-query-optimization-changes/ In summary: The splitting of optimizer_adaptive_features parameter will be back-ported via a patch to 12.1 (maybe with the same defaults). With default settings SQL Plan Directives will still be created, but will not automatically cause dynamic sampling or creation of column groups (is this is how automatic creation of column groups currently works?). New mechanism for persistence of dynamic sampling query results. No longer uses result cache. Information is available to all RAC nodes and persists across instance restarts. These results are automatically invalidated when sufficient changes made to base tables (somewhat similar to stale statistics with dbms_stats). Regards Patrick
Hi Patrick, thanks. Good to know it will be backported even if it is only some parameter changes. Easier to made it accepted when it looks like a fix rather than playing with underscore parameters.
Hi Franck and Patrick
Thanks for the clear explanations; I’m slowly getting my head around these changes.
Oracle have now released information of the back-porting of the parameter change to 12.1 as MOS note 2187449.1 (https://support.oracle.com/epmos/faces/DocContentDisplay?id=2187449.1)
Regards
Patrick Hurley
Hi Patrick, Thanks or the information. This is very good news. Even when it is technically the same, it’s a lot easier to tell a customer “It was considered as bad, old setting is deprecated, and there is now a patch to fix it” rather than “just set those underscore parameters to fix the issue”. Regards, Franck.