Oracle Database 12.2 introduces the new split-up adaptive parameters, OPTIMIZER_ADAPTIVE_PLANS and OPTIMITER_ADAPTIVE_STATISTICS.
For more information please see:
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?
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.
OPTIMIZER_ADAPTIVE_STATISTICS, and in addition removes the parameter
Please make sure you'll remove
OPTIMIZER_ADAPTIVE_FEATURES from your spfile:
alter system reset optimizer_adaptive_features;
when applying the patches.
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.