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;
