Performance hit after migrating to Oracle 12c:

Performance hit after migrating to Oracle 12c:

We recently upgraded a PeopleSoft installation at two clients to Oracle RDBMS 12.1.0.2. While it was a great success overall, we encountered performance issues that did not exist on the previous version (10g / 11g).

Proper collection of statistics did not help.

It was discovered that a new Oracle feature called Adaptive Query Optimization is the culprit. Adaptive Query Optimization is a feature that allows Oracle to adjust the execution plan of a SQL *during* the execution, and use “adaptive statistics” gathered during runtime to augment basic table statistics.

Unfortunately, this can be costly and severely impact your performance. There are many references on Oracle Support with regards to performance issues using Adaptive features:

1335892.1 (Oracle Order Management / Advanced Pricing)

1320300.1 (Oracle E-Business Suite)

2182373.1 (Oracle Project Costing)

2059364.1 (Oracle Agile)

16033838.8 (RDBMS 12.1.0.1 (bug))

Yet - the REAL smoking gun is found by searching for “optimizer_adaptive_features peoplesoft”, in which a PDF doc will be returned that states:

optimizer_adaptive_features = FALSE - After upgrading to Oracle Database 12c, many PeopleSoft customers have noticed overall performance degradation which is related to Oracle 12c Optimizer Adaptive Feature (OAF). It is recommended that this value be disabled in all Oracle Databases running PeopleSoft Applications.

As stated, the parameter in Oracle 12.1 that controls Adaptive features is: OPTIMIZER_ADAPTIVE_FEATURES.

In Oracle 12.2, that parameter is obsolete and it is now controlled by two separate parameters: OPTIMIZER_ADAPTIVE_PLANS, OPTIMIZER_ADAPTIVE_STATISTICS.

NOTE: If you apply patch 22652097 to a 12.1.xx database, it will “split” OPTIMIZER_ADAPTIVE_FEATURES into the two separate parameters that 12.2 uses.

The parameter can be dynamically changed and set at the session level as well (no restart required!).

If you’re struggling with performance issues after a 12c upgrade, try disabling the adaptive features to see if it helps. After disabling OPTIMIZER_ADAPTIVE_FEATURES, PeopleSoft performance was excellent and we had no more end user complaints.

If you still have questions or need assistance with an Oracle or database migration, contact Solvaria via the form below.