Optimizer Adaptive feature parameter in Oracle

Oracle optimizer is used to find the most effective execution plan for each SQL statement. Oracle released adaptive feature in Oracle 12c.
Adaptive query optimization means during runtime of SQL statement find better execution plan with adjust statistics.
Adaptive plans: focuses on improving the execution of a query
Adaptive statistics: uses additional information to improve query execution plans.

Following are the parameter as optimizer adaptive plan and optimizer adaptive statistics.

SQL> show parameter adaptive

NAME                                 TYPE         VALUE
------------------------------------ ------------ ------------------------------
optimizer_adaptive_plans             boolean      TRUE
optimizer_adaptive_reporting_only    boolean      FALSE
optimizer_adaptive_statistics        boolean      FALSE
parallel_adaptive_multi_user         boolean      FALSE

For Enable and Disable Adaptive features
In Oracle 12101 version only one parameter defines for adaptive but in 12102 version use two parameter as adaptive:

-- Enable
In 12101 version:
alter system set optimizer_adaptive_features=true scope=both;

In 12102 onward:
alter system set optimizer_adaptive_plans=false scope=both;
alter system set optimizer_adaptive_statistics=false scope=both;

OPTIMIZER_ADAPTIVE_PLANS
It used by query optimizer for considers adaptive plans. If it’s set to TRUE (the default), the feature is enabled.
Following are the hidden parameter used by adaptive plans:
_OPTIMIZER_NLJ_HJ_ADAPTIVE_JOIN:
It is used the adaptive join methods. If it’s set to TRUE (the default), the feature is enabled.
_PX_ADAPTIVE_DIST_METHOD:
It controls the adaptive parallel distribution methods. If it’s set to CHOOSE (the default), the feature is enabled. If it’s set to OFF, the feature is disabled.
_OPTIMIZER_STRANS_ADAPTIVE_PRUNING:
It will controls the adaptive star transformations. If it’s set to TRUE (the default), the feature is enabled.

OPTIMIZER_ADAPTIVE_STATISTICS
It used by query optimizer for considers adaptive statistics, It is set to TRUE then enabled, FALSE (DEFAULT)
Following are the hidden parameter used by adaptive statistics:
_OPTIMIZER_DSDIR_USAGE_CONTROL
It controls whether the query optimizer uses SQL plan directives. If it’s set to 126 (the default), the feature is enabled.
If it’s set to 0, the feature is disabled. This parameter also supports other integer values, but I never spent time investigating their behavior.
_OPTIMIZER_GATHER_FEEDBACK
It controls whether the query optimizer asks the execution engine to check for mis-estimates then create feedback, If it’s set to TRUE (the default), the feature is enabled.
_OPTIMIZER_USE_FEEDBACK
It controls whether the query optimizer choose to use the feedback’s and produced more execution plan for compare. If it’s set to TRUE (the default), the feature is enabled.
_sql_plan_directive_mgmt_control
O means disable creation of directives, 3 value means enable creation of directives.

Check discription of these parameter

select ksppinm,ksppdesc from x$ksppi where ksppinm in ('_optimizer_nlj_hj_adaptive_join', '_optimizer_strans_adaptive_pruning',
'_px_adaptive_dist_method', '_sql_plan_directive_mgmt_control', '_optimizer_dsdir_usage_control', '_optimizer_use_feedback', '_optimizer_gather_feedback', '_optimizer_performance_feedback');


Change the adaptive parameter inner functionality with hidden parameters

-- Backup of the parameter
COLUMN ksppinm FORMAT A40
COLUMN ksppstvl FORMAT A20
SELECT ksppinm,ksppstvl FROM x$ksppi a,x$ksppsv b WHERE a.indx=b.indx
AND ksppinm in ('_optimizer_nlj_hj_adaptive_join', '_optimizer_strans_adaptive_pruning', '_px_adaptive_dist_method', '_sql_plan_directive_mgmt_control', '_optimizer_dsdir_usage_control',
'_optimizer_use_feedback', '_optimizer_gather_feedback', '_optimizer_performance_feedback') ORDER BY ksppinm;

KSPPINM                                  KSPPSTVL
---------------------------------------- --------------------
_optimizer_dsdir_usage_control           0
_optimizer_gather_feedback               TRUE
_optimizer_nlj_hj_adaptive_join          TRUE
_optimizer_performance_feedback          OFF
_optimizer_strans_adaptive_pruning       TRUE
_optimizer_use_feedback                  TRUE
_px_adaptive_dist_method                 CHOOSE
_sql_plan_directive_mgmt_control         67

-- Reset the parameter
alter system reset "_optimizer_nlj_hj_adaptive_join" scope=both;
alter system reset "_optimizer_strans_adaptive_pruning" scope=both;
alter system reset "_px_adaptive_dist_method" scope=both;
alter system reset "_sql_plan_directive_mgmt_control" scope=both;
alter system reset "_optimizer_dsdir_usage_control" scope=both;
alter system reset "_optimizer_use_feedback" scope=both;
alter system reset "_optimizer_gather_feedback" scope=both;
alter system reset "_optimizer_performance_feedback" scope=both;


You can also use the parameter as hints:

select /*+ opt_param('_optimizer_use_feedback' 'false') */ * from table;