Performance Issues with Oracle 12c

Last update: 12 December 2017

Information

We got information from a customer that upgrading the underlaying Oracle Database 11g to Oracle Database 12c brought up obvious performance issues with Fabasoft Folio / Fabasoft eGov-Suite products.

For example, the startup time of the Fabasoft Folio Backendservices in a test environment increased from 90 seconds to about 1 hour.

Oracle introduced a new feature Adaptive Query Optimization in Oracle 12c to better estimate statistics and optimize plans. In the system of the mentioned customer this lead to the performance issues.

This feature is enabled by default and can be turned off by a database setting.

Fabasoft welcomes new optimizations in our underlying software to optimize stability and performance, therefore we suggest to keep the default setting if you do not notice any performance impact. If you can verify any performance impacts, we recommend to temporary turn off the new Oracle feature. Please re-evaluate the performance from time to time in a test environment after installing Oracle updates and/or fixes.

Solution

Oracle 12c 12.0/12.1

Use the following system setting to disable Adaptive Query Optimization:

   ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_FEATURES = FALSE SCOPE=BOTH;

(Default is TRUE)

Hint: The settings from Oracle 12.2 where backported to 12.1. See https://support.oracle.com/epmos/faces/DocContentDisplay?id=2187449.1

Before changing settings, please verify if your Oracle 12.1 database may already have installed this backport.

Oracle 12c 12.2

Oracle has splitted the Optimizer Adaptive Features into two settings:

   ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_PLANS = FALSE SCOPE=BOTH;

(Default is TRUE)

   ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_STATISTICS = FALSE SCOPE=BOTH;

(Default is FALSE)

Details

Please visit Oracle Support for details about the Adaptive Optimization feature.

Weblinks:

https://oracle-base.com/articles/12c/adaptive-query-optimization-12cr1

https://bdrouvot.wordpress.com/2014/10/17/watch-out-for-optimizer_adaptive_features-as-it-may-have-a-huge-negative-impact/

http://www.ludovicocaldara.net/dba/tag/optimizer_adaptive_features/

http://kerryosborne.oracle-guy.com/2013/11/12c-adaptive-optimization-part-1/