Roy today answered an interesting question on one of our internal mailing lists. And this reminds me to pick up that piece of information as we see this quite often on customer sites, especially after upgrades or migrations. People set OPTIMIZER_FEATURES_ENABLE (OFE) to revert the optimizer’s behaviour to another pre-current release. That’s what a lot of people think this parameter does.
But in fact this is not true. Even though our documentation states it:
OPTIMIZER_FEATURES_ENABLEacts as an umbrella parameter for enabling a series of optimizer features based on an Oracle release number. For example, if you upgrade your database from release 10.1 to release 11.1, but you want to keep the release 10.1 optimizer behavior, you can do so by setting this parameter to10.1.0. At a later time, you can try the enhancements introduced in releases up to and including release 11.1 by setting the parameter to11.1.0.6.
In my experience the following is true:
Setting this parameter reverts the optimizer settings in terms of parameters – but we still use the new optimizer code from that release you are on now. And as far as I know nobody ever tests if switching back OFE will turn back to exactly the behaviour as it was known in the release OFE is now switched to. So it’s simply guessing and assuming. But as code got changed there’s very little chance to get back to the old behaviour.
What people sometimes experience:
Turning back OFE brings back good performance. This can happen. But if we act really really really precise than the performance should always be better with the optimizer running with the new settings – and not when the wheel is turned back. So in some cases this should be treated as a bug unless new behaviour leads to predictable worse results (such as more buffer gets etc). And I get so angry when Oracle Support people recommend to switch OFE to this or that setting to cure one or the otther misbehaviour. That’s like throwing a big rock to kill a fly.
But the real danger is described in the following note – and I’m pretty sure a lot of people are not aware of this:
Reverting the optimizer parametrization with OFE can turn back to misbehaviour which got fixed already in the current release. MOS Note:1581465.1 describes this pretty well.
And in addition regarding upgrades you may want to read this note here as well:
MOS Note: 1362332.1 Use Caution if Changing the OPTIMIZER_FEATURES_ENABLE Parameter after an Upgrade
(Thanks Roy!)
Recommendation:
Stay away from tweaking anything with OFE. Use Real Application Testing’s SQL Performance Analyzer (SPA) to find out which plans get changed and use SQL Plan Management (SPM) to nail down misbehaving plans in 11g or 12c.
Please see also Jonathan Lewis’ thoughts about the pros and cons of using OFE especially with an upgrade:
https://jonathanlewis.wordpress.com/2017/07/07/ofe/
credit:https://mikedietrichde.com/2013/12/16/why-you-shouldnt-set-optimizer_features_enable/