Adaptive Query Optimization in Oracle Database 12c Release 1

Oracle Database 12c was released back in June 2013. After upgrading database to 12c Release 1, the biggest problem faced by DBAs was the unpredictable performance of SQL queries. And it was determined that the new behavior of optimizer is causing the performance issues. Hence the natural solution for many DBAs was to set OPTIMIZER_FEATURES_ENABLE database parameter to 11.2.0.4 so that the optimizer behaves similar to 11gR2 database. This works, but then we are missing out all the right things the optimizer learned and implemented in 12c database. The problem feature causing performance issues may be the “Adaptive Query Optimization.”

Adaptive Query Optimization is a collection of features that work together to allow the cost based optimizer (CBO) to improve the accuracy of execution plans. This feature helps the optimizer adjust the plans based on the real time data during run time.

Adaptive Query Optimization

The Adaptive Query Optimization feature has two components.  Adaptive Plans allow plans to change during execution and Adaptive Statistics allow plans to be built based upon the results of previous executions.  In 12.1 database both components are controlled by one initialization parameter and the DBA can disable adaptive query optimization feature by setting the parameter OPTIMIZER_ADAPTIVE_FEATURES=FALSE. In 12.2 database, Oracle de-supported this parameter, instead introduced two new parameters.

In 12.2, when you create a new database, the default for OPTIMIZER_ADAPTIVE_PLANS is TRUE, and OPTIMIZER_ADAPTIVE_STATISTICS is FALSE. After the 12.2 release, Oracle released patch 22652097 for 12.1 database by which you get the two parameters in 12.1 similar to the 12.2 database.

Read more about adaptive features in the Oracle white paper “Optimizer with Oracle Database 12c Release 2“.

Options After Upgrading to Database 12c

In summary, after upgrading an 11g database to 12c, you have these options:

Upgrade to 12c Release 2 (12.2)

• For most OLTP databases, default behavior is good. No DBA action required.
• Adaptive Plan enabled (OPTIMIZER_ADAPTIVE_PLANS=TRUE), Adaptive Statistics disabled (OPTIMIZER_ADAPTIVE_STATISTICS=FALSE)

Upgrade to 12c Release 1 (12.1)

• Option 1
  • Apply October 2017 (or latest) Database Proactive Bundle Patch (MOS Doc ID 26635880.8)
  • Though October 2017 BP includes the recommended patches, the fixes are not enabled by default. To enable set “_FIX_CONTROL”=’26664361:3′.
  • Turn off Adaptive Statistics (OPTIMIZER_ADAPTIVE_STATISTICS=FALSE) and turn on Adaptive Plans (OPTIMIZER_ADAPTIVE_PLANS=TRUE).
  • Set AUTO_STAT_EXTENSIONS to OFF using DBMS_STATS.SET_GLOBAL_PREFS.
• Option 2 (if you do not wish to apply Bundle Patches)
  • If set, remove the OPTIMIZER_ADAPTIVE_FEATURES parameter from the pfile or spfile.
  • Apply Patch 22652097 and Patch 21171382.
  • Turn off Adaptive Statistics (OPTIMIZER_ADAPTIVE_STATISTICS=FALSE) and turn on Adaptive Plans (OPTIMIZER_ADAPTIVE_PLANS=TRUE).
• Option 3
  • Turn off adaptive features by setting OPTIMIZER_ADAPTIVE_FEATURES=FALSE or
  • Do not act on adaptive information collected during query execution by setting OPTIMIZER_ADAPTIVE_REPORTING_ONLY=TRUE
  • Do not change the OPTIMIZER_FEAURES_ENABLE parameter.

Remember to remove all the Adaptive Query changes you made in 12.1 database (init parameters OPTIMIZER_ADAPTIVE* and _FIX_CONTROL) before upgrading the database to 12c Release 2 (12.2).

If you have already applied individual patches 22652097, 20243268 or 21171382 on your 12cR1 (12.1) database, read the MOS Note ID 2312911.1 “Recommendations for Adaptive Features in Oracle Database 12c Release 1 and Changes to Adaptive Feature in Release 2 (Adaptive Features, Adaptive Statistics and 12c SQL Performance)” before applying the October 2017 Proactive Bundle Patch.