Use Caution if Changing the OPTIMIZER_FEATURES_ENABLE Parameter After an Upgrade
(Doc ID 1362332.1)
Last updated on MARCH 12, 2021
Applies to:Oracle Database - Enterprise Edition - Version 126.96.36.199 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
On rare occasions, after an upgrade from an earlier version to higher version, performance may degrade because new optimizer features are implemented but the environment is not setup correctly to allow them to perform to their full potential. For example statistics may not be sufficiently accurate nor gathered to a sufficient level of detail for the features to operate in an optimal fashion.
Although such issues can largely be avoided by implementing some form of upgrade testing (such as the use of Real Application Testing (RAT) features such as SQL Performance Analyzer and Database Replay) and applying the relevent changes, if this has not happened then a suitable short term workaround may be necessary.
In such cases, especially if insufficient testing has occurred, a system may be live on a new version and performance of certain queries can be severely reduced. In the short term, reverting the optimizer back to a previous version with the parameter OPTIMIZER_FEATURES_ENABLE to improve performance of individual queries and implement a suitable workaround (such as using SQL Plan Management or other plan stability features) to stabilise under a 'good' access path while another solution is found.
By reducing the OPTIMIZER_FEATURES_ENABLE level, new optimizer features are disabled. This has serious potential for negatively affecting performance generally by eliminating the possibility of choosing better plans that are only available with features enabled within the higher revision levels.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document