What Adaptive Feature Settings Should You Use In Oracle Database 12c?

Happy new year everyone! Let's start the year with a simple question: what adaptive feature settings does Oracle recommend for the optimizer in Oracle Database 12c? I've covered this a couple times in the past (links below) but I've tended to dive into the details and unintentionally buried the short answer.

Here's the Short Answer

Oracle Database 12c Release 2

For the majority of systems: use the defaults. There is no need to specifically set optimizer adaptive feature parameters.

By default, adaptive statistics are disabled and adaptive plans are enabled. If you are in a position to test the benefits of adaptive statistics on your system, then it's something to consider using of course.

Oracle Database 12c Release 1

If you have a production environment that's not encountering performance issues related to the adaptive optimizer then it's more than reasonable to keep things as they are. In general, if you want to change adaptive feature settings in a production environment (or enable adaptive feature patches included in a bundle patch), then you should test before implementation.

If you are currently testing (or about to test) an upgrade to Oracle Database 12c Release 1, then it's a good idea to apply the latest bundle patch and specifically enable the adaptive features patches. See Mike Dietrich's blog post on this topic.

Here's the Long Answer

Not everyone likes a short answer; the real-world can be very complicated. To that end, I have blogged in more detail about upgrades here and there are some specifics on the adaptive feature changes in Oracle Database 12c Release 2 here. There's also Mike's blog post as I mentioned earlier.

It's ironic that in this post, the short answer looks longer than the long answer. Oh well.

Join the discussion

Comments ( 5 )

Neil Chandler Friday, January 5, 2018

Given that BP's are inherently less stable than CPU's as they have optimizer changes, and a patch merge (and therefore delay) is required for every PSU if we use the stand-alone patch, can you recommend an alternative to the patch; perhaps setting underscore parameters or explicitly disabling and removing auto_drop from SQL Plan Directives?

Nigel Bayliss Monday, January 8, 2018

Hi Neil,

Your question is a very fair one and - well - there's a lot of history behind the answer. Before I do answer it…

We do not include 'active' optimizer fixes in BPs other than wrong-results so I would not agree that they are inherently less stable than CPUs. You can apply BPs without changing plans. There is a MOS note on the topic, with a section documenting what’s included in a BP:

I had to use the word 'active' above because of the special case we introduced in the October BP. Firstly, it’s important to know that you can safely apply the October BP without changing SQL execution plans. This is because the included adaptive optimizer patches are NOT active by default. If you want to use them, you must use fix control settings to enable them. A lot of development effort went into coding the BP to make sure that it is safe to apply it without affecting plans (in common with previous BPs). If you specifically enable the adaptive feature fixes then plans may change (but that’s the point of course).

To answer your question – it is extremely rare for development to recommend hidden parameter solutions or ‘formulas’, but I choose to give it serious consideration because (back in the day) it was inconceivable that we would be able to include optimizer fixes like this in a BP. Unfortunately, a parameter-led, one-size-fits-all approach was untenable. I admit that this was not entirely for technical reasons, but also for practical reasons because the complexity of the decision-tree and the number of hidden parameter settings grew as we took into account patches and settings that might already be in place. We could not get to a point where we could be sure that the resulting DB would reproduce 12.2 behavior correctly and in a way that was free of unintended consequence. I even considered breaking the ‘BP rule’ by proposing that we include the adaptive patches in a BP with a hidden-parameter formula to disable them, but this was even more problematic. In the end, code changes proved necessary to deliver a solution that could be activated and deactivated reliably.

Thanks for the heads-up. I have been in touch with the author of the blog post and he has kindly given me a lot of the background. Firstly, I can see that I definitely need to clarify what parameter changes you can expect to see when the BP is applied. In addition, I need to cover the adaptive parameter defaults, what effect each parameter has and how they interact with one another. I can see that the way the settings interact can lead to some confusion and (to be 100% clear) I'm not implying any criticism of the blogger whatsoever. It's all on me to clarify the details, so I'll make it the subject of my next blog post (coming soon).

Regarding the parse issue seen post-BP - the cause is unknown (at the time of writing). I want to emphasize that everyone involved worked extremely hard to ensure that the adaptive changes included in the BP are 'silent' unless they are activated. Nevertheless, if I find out conclusively that there is an issue then I will blog about it.