The Oracle Database 12.1 parameter OPTIMIZER_ADAPTIVE_FEATURES has been made OBSOLETE (i.e. must be removed from the SPFILE when upgrading) in Oracle Database 12.2.
It gets replaced with two parameters of whom one is enabled, the other one is disabled by default:
- OPTIMIZER_ADAPTIVE_PLANS=TRUE by default
- OPTIMIZER_ADAPTIVE_STATISTICS=FALSE by default
Nigel Bayliss, our “Optimizer” Product Manager has blogged about it already with way more detailed insights.
But as Oracle Database 12.2 on-premises is not available how should you deal with this feature in Oracle Database 12.1?
–Mike
.
thanks for your information
thank you very much.
There is a typo in the parameter name:
-OPTIMITER_ADAPTIVE_STATISTICS=FALSE
+OPTIMIZER_ADAPTIVE_STATISTICS=FALSE
Thanks 🙂
Mike
It seems as if the default value for OPTIMIZER_ADAPTIVE_PLANS has been changed to false per Oracle documentation. Of course, there’s one spot in the doc where it says it’s still true, but I think that was just an oversight. https://docs.oracle.com/database/122/UPGRD/initialization-parameter-changes-oracle-database-12c-r2.htm#UPGRD-GUID-315015C7-EBC3-4AB4-B2E3-EB4A2F2A9452
Pavel,
are you sure? In my envs (unless I change it) "Plans" is TRUE and "Stats" is FALSE by default. Any other parameters you have set in addition such as OPTIMIZER_FEATURES_ENABLE to a lower value?
Cheers
Mike
Hi Mike,
I noticed in this edition comparison chart for 12c (https://www.oracle.com/au/products/database/enterprise-edition/comparisons/index.html) that Adaptive Execution Plans is listed as an Enterprise only feature. Via SR 3-15068257241, it was confirmed that this should only be available for Enterprise Edition.
As this functionality is currently available in Standard Edition in 12.1.0.2 (and enabled by default), do you know what the plan is to restrict SE2 usage of this feature in the future?
Adam,
very interesting topic – please let me clarify this with the optimizer team.
Cheers
Mike
Adam,
thanks for bringing this to my attention. I will release a blog post next week with some explanation. And don’t worry, regardless of the parameter, the functionality is not there in 12.2 SE2 – and it won’t raise an error.
Cheers
Mike