Optimizer adaptive feature caused performance in Oracle

In Oracle 12c, the Adaptive feature is released with the 12.1 release of the Oracle Database.
optimizer_adaptive_features is the parameter in 12.1 released to control the behavior of optimizer with one parameter optimizer adaptive features.

--Disable
alter system set optimizer_adaptive_features=false scope=both;
--Enable
alter system set optimizer_adaptive_features=true scope=both;

In release 12.2, Oracle depreciated the optimizer_adaptive_features parameter into two parameters Optimizer_Adaptive_plans and optimizer_Adaptive_statistics.

--Disable
ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_PLANS = FALSE SCOPE=BOTH;
--Enable
ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_PLANS = TRUE SCOPE=BOTH;

ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_STATISTICS = TRUE SCOPE=BOTH;
--Disable
ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_STATISTICS = FALSE SCOPE=BOTH;

The adaptive feature in the optimizer is used for making the run time discussion about choosing the execution plan and taking discussion of Stats. This feature tries to generate a more optimal query plan.

The adaptive feature in Oracle 12c caused the performance issue on many platforms. It increases the overhead of the system to choose a better plan during the execution of Query which sometimes causes performance issues.

optimizer_adaptive_features default is True has a huge negative impact on the performance of the database.

Example:

We got the following SQL statement from statspack report having a lot of execution in the database which caused the performance degradation in the Oracle Database.
On checking on the net, it is related to optimizer_adaptive_features (i.e. adaptive features introduced in Oracle 12c).

SELECT executions, end_of_fetch_count
    ,elapsed_time / px_servers elapsed_time
    ,cpu_time / px_servers cpu_time
    ,buffer_gets / executions buffer_gets
  FROM
    ( SELECT
          SUM (executions) AS executions,SUM (
            CASE
              WHEN px_servers_executions > 0
              THEN px_servers_executions
              ELSE executions
            END
          ) AS px_servers 
          ,SUM (end_of_fetch_count) AS end_of_fetch_count
          ,SUM (elapsed_time) AS elapsed_time
          ,SUM (cpu_time) AS cpu_time
          ,SUM (buffer_gets) AS buffer_gets
        FROM
          gv$sql
        WHERE
          executions > 0
          AND sql_id = : 1
          AND parsing_schema_name = : 2

optimizer_adaptive_features feature is used for making the life of dba easy. So, that optimizer can choose the better plan and stats at run time but in few cases it have very negative impact of database. so you need to disable it by following parameters

alter system set optimizer_adaptive_features=false scope=both;

we can set this hidden parameter:

alter system set "_optimizer_dsdir_usage_control"=0 scope=both;

Dynamic Sampling is also caused performance issue, So, we can also disable with following parameter or you follow link for more detail: Dynamic Sampling

alter system set optimizer_dynamic_sampling=0 scope=both;