Hey, did you catch that redirect? The Toad World URL is now community.toadworld.com. Don't worry -- you'll still find all the same great content here (and more on the way). We're just in the process of giving our Toad World site some well-deserved love. Stay tuned for some more updates coming this way.
Meanwhile, enjoy community.toadworld.com.
Every version of the Oracle Database brings new optimizer features, and, with them, the potential for changed query performance. For example, Oracle Database 12c brought a slew of new features collectively labeled “adaptive” features. While the hope is that new optimizer features will improve performance, there is no guarantee. Are not the new features well-tested? We have to believe they are but the Oracle development team does not have access to your organization’s workload which means that the new optimizer features were not tested on your organization’s workload prior to release. Nothing could be more obvious.
Someone recently gave me an insight with which you would probably agree with: If query performance is satisfactory before and after the upgrade, then business users are unlikely to complain if new optimizer features are not being used after the upgrade.
In other words, if query performance is satisfactory before and after the upgrade, business users are unlikely to complain if you mitigated risk by setting optimizer_features_enable to the previous database version, not the new database version.
What advantage would there be to upgrading Oracle Database if optimizer_features_enable is set to the previous database version? You will still get all the bug fixes in the new version including those for optimizer bugs and SQL execution bugs. And you can take advantage of new features in areas other than query performance.
It’s a point to ponder.