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 to
10.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 to
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
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:
I have a client upgraded 11g database to 19c but still using 10.2.0.2 OFE in both primary and standby database. He doesn’t want to change OFE to current version but wants to change to Standby database? Still using 11g compatible parameter too. Please suggest. Thank you so much for your time.
OFE and COMPATIBLE are independent from each other.
You can use all the above values in 19c – so even if I’m not a fan of it, and I disrecommend the use as you don’t get the exact same behavior, the customer can use it.
I disagree. When Oracle releases a new version, it should be Oracle’s job to deliver the same performance as before. If that is not possible, Oracle should provide a tool with which the database can be configured to the new requirements of the new features.
It cannot be that the customer has to look for why the software is suddenly slower and then gets to read a document (1359841.1) that refers to 30 other documents.
in theory I’d agree with you. But unfortunately the optimizer code is far too complex. Using OFE became a bit better in the past releases compared to 10g/11g times. But still, you turn off a lot of good things which (I have numerous customer examples about this) speed up things in 19c compared to the previous release.