Use Caution if Changing the OPTIMIZER_FEATURES_ENABLE Parameter After an Upgrade
Last updated on SEPTEMBER 29, 2017
Applies to:Oracle Database - Enterprise Edition - Version 18.104.22.168 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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms