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 18.104.22.168 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 22.214.171.124. The fixes got delivered on MS Windows a bit earlier.
- Enabling Oracle 12.2 ADAPTIVE Features in Oracle 126.96.36.199 – BEFORE the patches got included in BPs
- Oracle 12.2 Adaptive Features are enabled in Oracle 188.8.131.52 Windows BPs
- Fixes for Adaptive Features are included in Oracle 184.108.40.206 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 220.127.116.11.
Additional Info for Adaptive Features Fixes in Oracle 18.104.22.168
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 22.214.171.124:
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:
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_controlmakes the 3 fixes present to the database but doesn’t necessarily enable them
- 20243268 is controlled via a
_fix_controlsetting whereas 22652097 and 21171382 aren’t
- Therefore to enable 20243268 the
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..
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.