Additional Info for Adaptive Features Fixes in Oracle 12.1.0.2

I blogged about the Adaptive Features fixes in the past several times. But following some of the comments readers had I believe there’s some additional info for Adaptive Feature Fixes in Oracle 12.1.0.2 necessary.

What happened so far?

We delivered the most important fixes not only for adaptive features but only for dynamic sampling and some other things with the Database Bundle Patch in October 2017 for Oracle Database 12.1.0.2. The fixes got delivered on MS Windows a bit earlier.

• Enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2 – BEFORE the patches got included in BPs
• Oracle 12.2 Adaptive Features are enabled in Oracle 12.1.0.2 Windows BPs
• Fixes for Adaptive Features are included in Oracle 12.1.0.2 October 2017 BP
• OPTIMIZER_ADAPTIVE_FEATURES obsolete in Oracle 12.2
• Adpative Features Patches with Oracle Peoplesoft

But there were additional questions coming on the blog from readers. One person had significant issues – and it turned out that our settings were not obvious. Another person commented on the _fix_control settings in Oracle 12.1.0.2.

Additional Info for Adaptive Features Fixes in Oracle 12.1.0.2

First of all, my colleague Nigel Bayliss, the Product Manager for the Optimizer, has blogged on the Optimizer Blog about some things to take into consideration when enabling the Adaptive Feature Fixes in Oracle Database 12.1.0.2:

• The Oracle 12.1.0.2 October 2017 BP and the Adaptive Optimizer

Nigel writes about important things such as:

• Why you see the new parameters optimizer_adaptive_plans and optimizer_adaptive_statistics even if you haven’t enabled the fix with a _fix_control settings
• Why you need to disconnect your session in order to see the correct parameter settings
• What happens when you don’t enable the fixes with _fix_control but still use the new parameters

Clearly worth a read!

And how about the _fix_control settings?

In addition I received this comment and question from Olivier from dbi services regarding this blog post:

• Fixes for Adaptive Features are included in Oracle 12.1.0.2 October 2017 BP

Let me clarify a few things for those who’d like to dig deeper into such topics (and thanks to Mark Jefferys for his patience) 🙂

Olivier’s question is: “Why do we need to add 20243268:1 again?”

It looks strange as 20243268 is part of 26664361 which seems to be enabled by setting the bit vector to 7. This bit vector means that all three fixes included in 26664361 are present.

• 26664361 contains these 3 patches: 22652097, 21171382 and 20243268
  • In addition to fix 20243268, patch 20243268 contains fix 16732417
• The bit value of 7 as recommended in the _fix_control makes the 3 fixes present to the database but doesn’t necessarily enable them
• 20243268 is controlled via a _fix_control setting whereas 22652097 and 21171382 aren’t
• Therefore to enable 20243268 the _fix_control=20243268:1 is necessary

The differentiation happens in case a fix is working under the FCP (Fix Control Persistence) framework ensuring that your settings are kept when you apply a future Bundle Patch. That’s why you have to use this setting as described in MOS Note: 2312911.1 – Recommendations for Adaptive Features in Oracle Database 12.1..

_fix_control='26664361:7','16732417:1','20243268:1'

IMPORTANT: Added on Apr 10, 2018:

In addition please set explicitly OPTIMIZER_ADAPTIVE_STATISTICS=FALSE in your SPFILE if you’d like to disable Adaptive Statistics as otherwise SPDs will still be created in some cases. This is fixed with the April 2018 Bundle Patch.

–Mike