Oracle Database 12.2 introduces the new split-up adaptive parameters, OPTIMIZER_ADAPTIVE_PLANS and OPTIMITER_ADAPTIVE_STATISTICS. And the key trigger to cure potential and known performance issues in Oracle Database 12.1.0.2 is enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2
For more information please see:
- https://mikedietrichde.com/2016/11/22/optimizer_adaptive_features-obsolete-in-oracle-12-2/
- https://blogs.oracle.com/optimizer/entry/optimizer_adaptive_features_in_the
Enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2
But Oracle Database 12.2 on-premises is not out yet – so what should you do when upgrading to Oracle Database 12.1 – or struggling with some of the “adaptive” features in Oracle 12.1?
- MOS Note: 2187449.1
Oracle Highly Recommended Adaptive Feature Configuration Parameter Settings for 12.1.0.2.0
It recommends to adopt the Oracle Database 12.2 defaults when upgrading to Oracle Database 12.1. This can be achieved by installing two patches – we call it the recommended approach.
-
- The patch for bug# 22652097 introduces the two parameters
OPTIMIZER_ADAPTIVE_PLANSandOPTIMIZER_ADAPTIVE_STATISTICS, and in addition removes the parameterOPTIMIZER_ADAPTIVE_FEATURES. - The patch for bug# 21171382 disables the automatic creation of extended statistics unless the optimizer preference AUTO_STATS_EXTENSIONS is set to ON.
- The patch for bug# 22652097 introduces the two parameters
Please make sure you’ll remove OPTIMIZER_ADAPTIVE_FEATURES from your spfile:
alter system reset optimizer_adaptive_features;
when applying the patches.
Adaptive Features – backporting Oracle 12.2 parameters into Oracle 12.1
Both patches should help as well once you have upgraded already to Oracle Database 12.1 but encountering performance issues.
Please note that it’s not necessary to set OPTIMIZER_DYNAMIC_SAMPLING to a
non-default value because the patches will disable the use of adaptive dynamic sampling
to match the default behavior in Oracle Database 12.2 when both new parameters are used in their default settings.
Further Information:
–Mike
Hello,
patch 22652097 is currently available only as overlay on top of PSU July 2016 and only for Linux x64 and AIX. I have requested an overlay on top of DBBP Proactive October 2016.
Regards,
Martin
Nice Post, thanks. But why is the patch for bug# 22652097 only available for the PSU branch and not for BP, which is said to be the future for 12c?
BTW: Enjoyed your Presentations at DOAG 🙂
Same problem! Just checked: patch 22652097 on top of DBBP-Oct is still with development. Should we request an overlay every time the new PSU and DBBP released?
— Kirill Loifman
Hi Kiril,
I see your point, I fully understand it, I agree with you – and still I can’t change it. I checked 226… for another customer last Friday and it was escalated already by then. I can only ask you to open an SR and request it as well. The internal automatism will increase the SR COUNTER in the bug and thus show how many people are affected.
Patching is not in our hands unfortunately.
Sorry for the inconvenience and kind regards
Mike
Hello,
Do you know if the two patches have been included in any 12.1.0.2 database PSU yet?
Thanks
Mark
This is the list of fixes for the Proactive Bundle Patch Jan17:
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=363486125922311&id=1937782.1&_adf.ctrl-state=op9njo7xy_119
And this is the one for the PSU Jan17 for 12c:
https://support.oracle.com/epmos/faces/DocumentDisplay?id=1924126.1
And none of them is included. The first one is obvious as it would massively change the setup by introducing two new parameters making one other obsolete. So this one will have to be applied always on top (unfortunately over and over again with every PSU or BP).
The other one is not included either – and I don’t know if it will be included in a future Proactive BP. I would hope so but you’ll have to double check with support and ask for an inclusion.
Cheers
Mike