Use Caution if Changing the OPTIMIZER_FEATURES_ENABLE Parameter After an Upgrade (Doc ID 1362332.1)

Last updated on OCTOBER 04, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.

Goal

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. 

NOTE: Modifying the OPTIMIZER_FEATURES_ENABLE parameter generally is strongly discouraged and should only be used as a short term measure at the suggestion of Oracle Global Support.

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.

Solution

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