12.1.0.2 adaptive features cause SQL performance to degrade

Background introduction

When you upgrade to the 12.1.0.2.0 database version, the default configuration of the adaptive features introduced in 12c can cause a drop in SQL performance.

Problem phenomenon
SQL statement performance may be degraded after upgrading to 12.1.0.2.0.

Affected Products and versions
Oracle 12.1.0.2.0

Cause and trigger conditions
Oracle 12.1.0.2, added 2 adaptive features: Adaptive planning and adaptive statistics, both of which are turned on by default, with one parameter Optimizer_adaptive_features control, Some scenarios may cause SQL statement performance issues to occur;

• The adaptive feature is a set of features that enables the optimizer to run-time adjustments to the execution plan, and adjusts future execution plans based on the results of previous executions.

1. Adaptive plan (Adaptive plans)-Allows you to change the plan during execution.
2. Adaptive statistics (Adaptive Statistics)-Allows you to build a plan based on previously executed results. You can save some change data in a data dictionary by using SQL Plan directives or by automatically creating extended statistics.

• In 12.2, the parameter optimizer_adaptive_features has been deprecated. The adaptive feature is controlled by two new parameters Optimizer_adaptive_plans and Optimizer_adaptive_statistics.

The Optimizer_adaptive_plans parameter controls whether the optimizer creates an adaptive schedule, which defaults to true. When Optimizer_features_enable is set to 12.1.0.1 or later, all of the adaptive features are enabled by Optimizer_adaptive_plans control. The Optimizer_adaptive_statistics parameter controls whether the optimizer uses adaptive statistics and defaults to false.
These default values are selected to emphasize the implementation of a stable SQL execution plan.

Solution Solutions
It is recommended to upgrade the 12.1 Adaptive feature with reference to the 12.2 standard and you need to install the patch:
Patch 22652097
The parameter optimizer_adaptive_features is discarded and divided into 2 parameters Optimizer_adaptive_plans and Optimizer_adaptive_statistics, For the control of Adaptive Execution Plan and adaptive statistics, the Adaptive Execution plan is enabled by default, and adaptive statistics are disabled;
Patch 21171382
Disables the automatic generation of extended statistics unless the parameter auto_stat_extensions is set to ON.
If your environment is 12.1 and you are experiencing the above issues, you need to consider installing the 2 patches above, if the above patches do not exist on your specific version and platform, or if they conflict with existing patches, please contact support to request a patch.

Reference documents
Recommendations for Adaptive Features in Oracle Database 12c (Adaptive Statistics & 12c SQL performance) (Doc ID 21874 49.1)

12.1.0.2 adaptive features cause SQL performance to degrade