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.