Oracle Adaptive LGWR and Adaptive Plans -- Nightmare in 12c database

Oracle  introduced adaptive  features from 12c  as a enhancement for automatic tuning  how 2  features that gave us nightmare where  Adaptive LGWR  and Adaptive plans .

Adaptive LGWR  : 

Adaptive LGWR - which tries to determine whether it is better to use serial LGWR or parallel LGWR and switches between these dynamically. However this may lead to DEADLOCK / Database Hang or ORA-742 "Log read detects lost write" or ORA-600 [kcrfrgv_nextlwn_scn] during instance OPEN or ORA-600 [krr_process_read_error_2] during Recovery on IBM AIX and potentially on HPUX Itanium 64bit. as reported in Bug  21915719. Doc ID 21915719.8  

This can be disabled

o To use only serial LGWR:

§ _use_single_log_writer=true

o To use only parallel LGWR:

§ _use_single_log_writer=false

MEMO: "ADAPTIVE" is the default value, it will toggle between TRUE/FALSE depending on workload. Adaptive means the instance will startup in parallel mode , then may switch back to serial if it thinks it's better that way.

Adaptive plans .

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. 

We got information from a customer that upgrading the underlaying Oracle Database 11g to Oracle Database 12c brought up obvious performance issues and sql plan flips issues . 

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

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)

We can disable  at session level  too using below 

alter session set "_optimizer_adaptive_plans"=false;