Here's a reminder of the changes we’ve made to the optimizer’s adaptive feature settings in Oracle Database 12c Release 2 onwards. The new default behavior is covered in an earlier post but I want to cover the various options available to you depending on what you're upgrading from.
Since the October 2017 database bundle patch, Oracle has included fixes for the adaptive features. This is covered in a more recent post. I have kept this post in place for those that are upgrading and have not applied the proactive bundle patches.
Upgrading from Oracle Database 11g (or an earlier release)
Once you’ve upgraded the database to Oracle Database 12c Release 2 or Oracle Database 18c, use the default adaptive feature settings. To do this, simply don't include any adaptive feature parameters in your database's initialization parameter file. In other words, there's no need to set optimizer_adaptive_plans or optimizer_adaptive_statistics.
Keep it simple!
Upgrading from Oracle Database 12c Release 1 where patches for bugs 21171382 and 22652097 have been applied.
These two patches enable an Oracle Database 12c Release 1 database to use the same adaptive feature settings as those in Oracle Database 12c Release 2 (see MOS note 2187449.1). There is no requirement to apply them before upgrading. They have been provided because some customers want to use the new adaptive parameter settings in Oracle Database 12c Release 1 production environments.
Oracle Database 12c Release 1 databases with these patches can be upgraded without changing any adaptive feature settings.That's it!
Alternatively, if you were not using the recommended defaults pre-upgrade and you want to use them post-upgrade, then:
Upgrading from Oracle Database 12c Release 1 and patches for bugs 21171382 and 22652097 have not been applied.
If you disabled adaptive features in Oracle Database 12c Release 1 (by setting, for example, optimizer_adaptive_features to FALSE) you should use the new recommended defaults once you've upgraded. To do this, you will need to check your initialization parameter file as follows:
If adaptive features are enabled in your Oracle Database 12c Release 1 database and you want to continue to use these features in the same way once the database has been upgraded, then:
SQL execution plans are subject to change when a database is upgraded,so critical systems should be tested to make sure that there are no performance regressions. If comprehensive testing is not practical but there is a strong requirement to mitigate the risk of performance regression, then use SQL plan management (SPM). This feature allows you to keep the same execution plans post-upgrade as the ones you had pre-upgrade. It can do this without "freezing" SQL execution plans: SPM will identify, verify and enable SQL execution plans that perform better in the new release. In this way it is possible to take advantage of new optimizer features that yield improved performance without risking poor performance associated with regressed SQL execution plans.
If anything is not clear, please don't hesistate to post a comment. Thanks.