Here's a reminder of the changes we’ve made to the optimizer’s adaptive feature settings in Oracle Database 12c Release 2. 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.
Here are the most common upgrade scenarios and for each I’m going to recommend what adaptive parameter settings to use.Scenario 1
Upgrading from Oracle Database 11g (or an earlier release)
Once you’ve upgraded the database to Oracle Database 12c Release 2, 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!Scenario 2
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:
- Remove references to optimizer_adaptive_plans and optimizer_adaptive_statistics from the database initialization parameter file.
- Ensure that the DBMS_STATS preference
AUTO_STAT_EXTENSIONS is set to OFF using DBMS_STATS.SET_GLOBAL_PREFS.
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) then instead you should use the Oracle Database 12c Release 2 recommended defaults once you've upgraded. To do this, you will need to check your initialization parameter file as follows:
- Remove references to the optimizer_adaptive_features parameter (it is obsolete in Oracle Database 12c Release 2).
- Remove any fix control and hidden parameter settings that were used to disable various adaptive features. Fix controls like 12914055, 12914055 and 7452863 were typically used along with hidden parameters like _optimizer_dsdir_usage_control and _sql_plan_directive_mgmt_control.
- There is no need to set optimizer_adaptive_plans and optimizer_adaptive_statistics because the default values are the recommended values.
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:
- Remove references to optimizer_adaptive_features from the
initialization file (it is obsolete in Oracle Database 12c Release 2).
- Add optimizer_adaptive_statistics=TRUEto the initialization parameter file (and there's no need to set optimizer_adaptive_plans because the default is TRUE).
- Set DBMS_STATS preference
AUTO_STAT_EXTENSIONS to ON using DBMS_STATS.SET_GLOBAL_PREFS
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.