Oracle 12cR2 Optimizer Adaptive Statistics

By Franck Pachot

.
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 announced on this topic for 12.2

Fixing vs. workarounds

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:

1. Make broken things working again as soon as possible
2. Ensure that the solution addresses the root cause and is in the same scope as the problem

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:

• The problem will come back again
• New problems will appear as side effects

12.1

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:

• Disabling the SQL Plan Directives that cause problem
• Disabling the feature that cause problem

so there are many combinations that depend on your context.

One parameter do not fit all

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:

• The developers of the optimizer know their stuff at least 100x better than I do
• They want to bring nice features rather than trying to break things

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?

12.2

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:

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:

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.