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 is a set of capabilities that enable the optimizer to make run-time adjustments to execution plans and discover additional information that can lead to better statistics. 

This new approach is extremely helpful when existing statistics are not sufficient to generate an optimal plan. 

Adaptive query optimization means during runtime of SQL statement find better execution plan with adjust statistics.

There are two distinct aspects in Adaptive Query Optimization:

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=true scope=both;
alter system set optimizer_adaptive_statistics=true scope=both;

-- Disable
In 12101 version:
alter system set optimizer_adaptive_features=false scope=both;

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

1) 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.

2) 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                                KSPPDESC
-------------------------------------- -------------------------------------------------------------
_optimizer_use_feedback                optimizer use feedback
_optimizer_gather_feedback             optimizer gather feedback
_sql_plan_directive_mgmt_control       controls internal SQL Plan Directive management activities
_px_adaptive_dist_method               determines the behavior of adaptive distribution methods
_optimizer_dsdir_usage_control         controls optimizer usage of dynamic sampling directives
_optimizer_strans_adaptive_pruning     allow adaptive pruning of star transformation bitmap trees
_optimizer_performance_feedback        controls the performance feedback
_optimizer_nlj_hj_adaptive_join        allow adaptive NL Hash joins

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

--Disable the parameters
alter system set "_optimizer_nlj_hj_adaptive_join"= FALSE scope=both;
alter system set "_optimizer_strans_adaptive_pruning"= FALSE scope=both;
alter system set "_px_adaptive_dist_method" = OFF scope=both;
alter system set "_sql_plan_directive_mgmt_control" = 0 scope=both;
alter system set "_optimizer_dsdir_usage_control" = 0 scope=both;
alter system set "_optimizer_use_feedback" = FALSE scope=both;
alter system set "_optimizer_gather_feedback" = FALSE scope=both;
alter system set "_optimizer_performance_feedback" = OFF scope=both;

-- 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;