Posted On: December 05, 2017
Topic: ERP Application Management
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 126.96.36.199 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.
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".
In summary, after upgrading an 11g database to 12c, you have these options:
Upgrade to 12c Release 2 (12.2)
Upgrade to 12c Release 1 (12.1)
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.
OneNeck's Oracle ACE Biju Thomas presented Oracle Database 12c new features sessions at various conferences including the Asia Pacific Oracle User Group Community (APACOUC) 2017 webinar tour. Watch the webinar recording here.
Hybrid IT infrastructure that combines on-premises and public cloud capabilities is a strategy many enterprises are embracing. Download Now
Why is it important for organizations to embrace digital transformation? Just ask anyone that once worked for Blockbuster. It’s not that we quit... Continue Reading