11.2.0的参数OPTIMIZER_FEATURES_ENABLE会话层与语句层上设置区别；

--下午在测试ventor io的发现OPTIMIZER_FEATURES_ENABLE在会话层级别和语句级别的HINT有差异；
先操练下：
--加入hint情况，OPT_PARAM('OPTIMIZER_FEATURES_ENABLE','10.2.0.1')
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> show parameter OPTIMIZER_FEATURES_ENABLE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable            string      11.2.0.2
SQL> select  /*+ OPT_PARAM('OPTIMIZER_FEATURES_ENABLE','10.2.0.1')  */t1.status,t3.status
  2  from 
  3  t1,t3
  4  where t1.object_id=t3.object_id
  5  and t1.object_type='VIEW';

no rows selected

SQL> @adv

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0xsv85g37s46d, child number 0
-------------------------------------
select  /*+ OPT_PARAM('OPTIMIZER_FEATURES_ENABLE','10.2.0.1')
*/t1.status,t3.status from t1,t3 where t1.object_id=t3.object_id and
t1.object_type='VIEW'

Plan hash value: 504643441

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |     7 (100)|          |
|   1 |  NESTED LOOPS                |           |       |       |            |          |
|   2 |   NESTED LOOPS               |           |     2 |    94 |     7   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | T3        |     2 |    36 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | IDX_T1_ID |     1 |       |     1   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| T1        |     1 |    29 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
     OPTIMIZER_FEATURES_ENABLE('10.2.0.1')   --说明已经生效了
      DB_VERSION('11.2.0.2')
      OPT_PARAM('_optimizer_undo_cost_change' '11.2.0.2')
      ....
      
--使hint失效，在会话层进行设置参数
alter session set OPTIMIZER_FEATURES_ENABLE='10.2.0.1';
SQL_ID  59a0r9bz3jyd7, child number 0
-------------------------------------
select  /*   + OPTIMIZER_FEATURES_ENABLE(10.2.0.1)
*/t1.status,t3.status from t1,t3 where t1.object_id=t3.object_id and
t1.object_type='VIEW'

Plan hash value: 1640834233

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |     7 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |    29 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |           |     2 |    94 |     7   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL        | T3        |     2 |    36 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | IDX_T1_ID |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T1@SEL$1
   3 - SEL$1 / T3@SEL$1
   4 - SEL$1 / T1@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')    --也生效了
      DB_VERSION('11.2.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T3"@"SEL$1")
      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))
      LEADING(@"SEL$1" "T3"@"SEL$1" "T1"@"SEL$1")
      USE_NL(@"SEL$1" "T1"@"SEL$1")
      NLJ_PREFETCH(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */      
  
--问题是为啥单独加hint不生效呢
我们仔细看下加hint时生成的outline信息
/*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      DB_VERSION('11.2.0.2')
      OPT_PARAM('_optimizer_undo_cost_change' '11.2.0.2')
      OPT_PARAM('_optimizer_null_aware_antijoin' 'true')
      OPT_PARAM('_optimizer_extend_jppd_view_types' 'true')
      OPT_PARAM('_optimizer_connect_by_cost_based' 'true')
      OPT_PARAM('_globalindex_pnum_filter_enabled' 'true')
      OPT_PARAM('_replace_virtual_columns' 'true')
      OPT_PARAM('_first_k_rows_dynamic_proration' 'true')
      OPT_PARAM('_bloom_pruning_enabled' 'true')
      OPT_PARAM('_px_ual_serial_input' 'true')
      OPT_PARAM('_optimizer_distinct_elimination' 'true')
      OPT_PARAM('_optimizer_multi_level_push_pred' 'true')
      OPT_PARAM('_optimizer_group_by_placement' 'true')
      OPT_PARAM('_optimizer_rownum_bind_default' 10)
      OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'simple')
      OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'true')
      OPT_PARAM('_optimizer_improve_selectivity' 'true')
      OPT_PARAM('_optimizer_enable_density_improvements' 'true')
      OPT_PARAM('_optimizer_connect_by_combine_sw' 'true')
      OPT_PARAM('_optimizer_native_full_outer_join' 'force')
      OPT_PARAM('_optimizer_enable_extended_stats' 'true')
      OPT_PARAM('_optimizer_fkr_index_cost_bias' 10)
      OPT_PARAM('_nlj_batching_enabled' 1)
      OPT_PARAM('_optimizer_extended_stats_usage_control' 192)
      OPT_PARAM('_bloom_folding_enabled' 'true')
      OPT_PARAM('_optimizer_coalesce_subqueries' 'true')
      OPT_PARAM('_optimizer_fast_pred_transitivity' 'true')
      OPT_PARAM('_optimizer_fast_access_pred_analysis' 'true')
      OPT_PARAM('_optimizer_unnest_disjunctive_subq' 'true')
      OPT_PARAM('_optimizer_unnest_corr_set_subq' 'true')
      OPT_PARAM('_optimizer_distinct_agg_transform' 'true')
      OPT_PARAM('_aggregation_optimization_settings' 0)
      OPT_PARAM('_optimizer_connect_by_elim_dups' 'true')
      OPT_PARAM('_optimizer_eliminate_filtering_join' 'true')
      OPT_PARAM('_connect_by_use_union_all' 'true')
      OPT_PARAM('_optimizer_join_factorization' 'true')
      OPT_PARAM('_optimizer_use_cbqt_star_transformation' 'true')
      OPT_PARAM('_optimizer_table_expansion' 'true')
      OPT_PARAM('_and_pruning_enabled' 'true')
      OPT_PARAM('_optimizer_distinct_placement' 'true')
      OPT_PARAM('_optimizer_use_feedback' 'true')
      OPT_PARAM('_optimizer_try_st_before_jppd' 'true')
      OPT_PARAM('_optimizer_interleave_jppd' 'true')
      OPT_PARAM('_px_partition_scan_enabled' 'true')
      OPT_PARAM('_optimizer_false_filter_pred_pullup' 'true')
      OPT_PARAM('_optimizer_enable_table_lookup_by_nl' 'true')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T3"@"SEL$1")
      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))
      LEADING(@"SEL$1" "T3"@"SEL$1" "T1"@"SEL$1")
      USE_NL(@"SEL$1" "T1"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */
  
  --而在会话层设置时的outline信息如下：
  很简洁：
  Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      DB_VERSION('11.2.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T3"@"SEL$1")
      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))
      LEADING(@"SEL$1" "T3"@"SEL$1" "T1"@"SEL$1")
      USE_NL(@"SEL$1" "T1"@"SEL$1")
      NLJ_PREFETCH(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */
  
  --接着我们来比对下那些出现的隐含参数是不是影响了；
  --不在会话层设置：
  set linesize 132
column name format a40
column value format a25
select
  x.ksppinm  name,
  y.ksppstvl  value,
  y.ksppstdf  isdefault,
  decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE')  ismod,
  decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE')  isadj
from
  sys.x$ksppi x,
  sys.x$ksppcv y
where
  x.inst_id = userenv('Instance') and
  y.inst_id = userenv('Instance') and
  x.indx = y.indx and
  x.ksppinm in (
'_optimizer_undo_cost_change',
'_optimizer_null_aware_antijoin',
'_optimizer_extend_jppd_view_types',
'_optimizer_connect_by_cost_based',
'_globalindex_pnum_filter_enabled',
'_replace_virtual_columns',
'_first_k_rows_dynamic_proration',
'_bloom_pruning_enabled',
'_px_ual_serial_input',
'_optimizer_distinct_elimination',
'_optimizer_multi_level_push_pred',
'_optimizer_group_by_placement',
'_optimizer_rownum_bind_default',
'_optimizer_extended_cursor_sharing_rel',
'_optimizer_adaptive_cursor_sharing',
'_optimizer_improve_selectivity',
'_optimizer_enable_density_improvements',
'_optimizer_connect_by_combine_sw',
'_optimizer_native_full_outer_join',
'_optimizer_enable_extended_stats',
'_optimizer_fkr_index_cost_bias',
'_nlj_batching_enabled',
'_optimizer_extended_stats_usage_control',
'_bloom_folding_enabled',
'_optimizer_coalesce_subqueries',
'_optimizer_fast_pred_transitivity',
'_optimizer_fast_access_pred_analysis',
'_optimizer_unnest_disjunctive_subq',
'_optimizer_unnest_corr_set_subq',
'_optimizer_distinct_agg_transform',
'_aggregation_optimization_settings',
'_optimizer_connect_by_elim_dups',
'_optimizer_eliminate_filtering_join',
'_connect_by_use_union_all',
'_optimizer_join_factorization',
'_optimizer_use_cbqt_star_transformation',
'_optimizer_table_expansion',
'_and_pruning_enabled',
'_optimizer_distinct_placement',
'_optimizer_use_feedback',
'_optimizer_try_st_before_jppd',
'_optimizer_interleave_jppd',
'_px_partition_scan_enabled',
'_optimizer_false_filter_pred_pullup',
'_optimizer_enable_table_lookup_by_nl'
  )
order by
  translate(x.ksppinm, ' _', ' ');
  
  NAME                                     VALUE                     ISDEFAULT ISMOD      ISADJ
---------------------------------------- ------------------------- --------- ---------- -----
_aggregation_optimization_settings       0                         TRUE      FALSE      FALSE
_and_pruning_enabled                     TRUE                      TRUE      FALSE      FALSE
_bloom_folding_enabled                   TRUE                      TRUE      FALSE      FALSE
_bloom_pruning_enabled                   TRUE                      TRUE      FALSE      FALSE
_connect_by_use_union_all                TRUE                      TRUE      FALSE      FALSE
_first_k_rows_dynamic_proration          TRUE                      TRUE      FALSE      FALSE
_globalindex_pnum_filter_enabled         TRUE                      TRUE      FALSE      FALSE
_nlj_batching_enabled                    1                         TRUE      FALSE      FALSE
_optimizer_adaptive_cursor_sharing       TRUE                      TRUE      FALSE      FALSE
_optimizer_coalesce_subqueries           TRUE                      TRUE      FALSE      FALSE
_optimizer_connect_by_combine_sw         TRUE                      TRUE      FALSE      FALSE
_optimizer_connect_by_cost_based         TRUE                      TRUE      FALSE      FALSE
_optimizer_connect_by_elim_dups          TRUE                      TRUE      FALSE      FALSE
_optimizer_distinct_agg_transform        TRUE                      TRUE      FALSE      FALSE
_optimizer_distinct_elimination          TRUE                      TRUE      FALSE      FALSE
_optimizer_distinct_placement            TRUE                      TRUE      FALSE      FALSE
_optimizer_eliminate_filtering_join      TRUE                      TRUE      FALSE      FALSE
_optimizer_enable_density_improvements   TRUE                      TRUE      FALSE      FALSE
_optimizer_enable_extended_stats         TRUE                      TRUE      FALSE      FALSE
_optimizer_enable_table_lookup_by_nl     TRUE                      TRUE      FALSE      FALSE
_optimizer_extended_cursor_sharing_rel   SIMPLE                    TRUE      FALSE      FALSE
_optimizer_extended_stats_usage_control  192                       TRUE      FALSE      FALSE
_optimizer_extend_jppd_view_types        TRUE                      TRUE      FALSE      FALSE
_optimizer_false_filter_pred_pullup      TRUE                      TRUE      FALSE      FALSE
_optimizer_fast_access_pred_analysis     TRUE                      TRUE      FALSE      FALSE
_optimizer_fast_pred_transitivity        TRUE                      TRUE      FALSE      FALSE
_optimizer_fkr_index_cost_bias           10                        TRUE      FALSE      FALSE
_optimizer_group_by_placement            TRUE                      TRUE      FALSE      FALSE
_optimizer_improve_selectivity           TRUE                      TRUE      FALSE      FALSE
_optimizer_interleave_jppd               TRUE                      TRUE      FALSE      FALSE
_optimizer_join_factorization            TRUE                      TRUE      FALSE      FALSE
_optimizer_multi_level_push_pred         TRUE                      TRUE      FALSE      FALSE
_optimizer_native_full_outer_join        FORCE                     TRUE      FALSE      FALSE
_optimizer_null_aware_antijoin           TRUE                      TRUE      FALSE      FALSE
_optimizer_rownum_bind_default           10                        TRUE      FALSE      FALSE
_optimizer_table_expansion               TRUE                      TRUE      FALSE      FALSE
_optimizer_try_st_before_jppd            TRUE                      TRUE      FALSE      FALSE
_optimizer_undo_cost_change              11.2.0.2                  TRUE      FALSE      FALSE
_optimizer_unnest_corr_set_subq          TRUE                      TRUE      FALSE      FALSE
_optimizer_unnest_disjunctive_subq       TRUE                      TRUE      FALSE      FALSE
_optimizer_use_cbqt_star_transformation  TRUE                      TRUE      FALSE      FALSE
_optimizer_use_feedback                  TRUE                      TRUE      FALSE      FALSE
_px_partition_scan_enabled               TRUE                      TRUE      FALSE      FALSE
_px_ual_serial_input                     TRUE                      TRUE      FALSE      FALSE
_replace_virtual_columns                 TRUE                      TRUE      FALSE      FALSE
  
  --在会话设置的呢
  NAME                                     VALUE                     ISDEFAULT ISMOD      ISADJ
---------------------------------------- ------------------------- --------- ---------- -----
_aggregation_optimization_settings       32                        TRUE      FALSE      FALSE
_and_pruning_enabled                     FALSE                     TRUE      FALSE      FALSE
_bloom_folding_enabled                   FALSE                     TRUE      FALSE      FALSE
_bloom_pruning_enabled                   FALSE                     TRUE      FALSE      FALSE
_connect_by_use_union_all                OLD_PLAN_MODE             TRUE      FALSE      FALSE
_first_k_rows_dynamic_proration          FALSE                     TRUE      FALSE      FALSE
_globalindex_pnum_filter_enabled         FALSE                     TRUE      FALSE      FALSE
_nlj_batching_enabled                    0                         TRUE      FALSE      FALSE
_optimizer_adaptive_cursor_sharing       FALSE                     TRUE      FALSE      FALSE
_optimizer_coalesce_subqueries           FALSE                     TRUE      FALSE      FALSE
_optimizer_connect_by_combine_sw         FALSE                     TRUE      FALSE      FALSE
_optimizer_connect_by_cost_based         FALSE                     TRUE      FALSE      FALSE
_optimizer_connect_by_elim_dups          FALSE                     TRUE      FALSE      FALSE
_optimizer_distinct_agg_transform        FALSE                     TRUE      FALSE      FALSE
_optimizer_distinct_elimination          FALSE                     TRUE      FALSE      FALSE
_optimizer_distinct_placement            FALSE                     TRUE      FALSE      FALSE
_optimizer_eliminate_filtering_join      FALSE                     TRUE      FALSE      FALSE
_optimizer_enable_density_improvements   FALSE                     TRUE      FALSE      FALSE
_optimizer_enable_extended_stats         FALSE                     TRUE      FALSE      FALSE
_optimizer_enable_table_lookup_by_nl     FALSE                     TRUE      FALSE      FALSE
_optimizer_extended_cursor_sharing_rel   NONE                      TRUE      FALSE      FALSE
_optimizer_extended_stats_usage_control  255                       TRUE      FALSE      FALSE
_optimizer_extend_jppd_view_types        FALSE                     TRUE      FALSE      FALSE
_optimizer_false_filter_pred_pullup      FALSE                     TRUE      FALSE      FALSE
_optimizer_fast_access_pred_analysis     FALSE                     TRUE      FALSE      FALSE
_optimizer_fast_pred_transitivity        FALSE                     TRUE      FALSE      FALSE
_optimizer_fkr_index_cost_bias           2                         TRUE      FALSE      FALSE
_optimizer_group_by_placement            FALSE                     TRUE      FALSE      FALSE
_optimizer_improve_selectivity           FALSE                     TRUE      FALSE      FALSE
_optimizer_interleave_jppd               FALSE                     TRUE      FALSE      FALSE
_optimizer_join_factorization            FALSE                     TRUE      FALSE      FALSE
_optimizer_multi_level_push_pred         FALSE                     TRUE      FALSE      FALSE
_optimizer_native_full_outer_join        OFF                       TRUE      FALSE      FALSE
_optimizer_null_aware_antijoin           FALSE                     TRUE      FALSE      FALSE
_optimizer_rownum_bind_default           0                         TRUE      FALSE      FALSE
_optimizer_table_expansion               FALSE                     TRUE      FALSE      FALSE
_optimizer_try_st_before_jppd            FALSE                     TRUE      FALSE      FALSE
_optimizer_undo_cost_change              10.2.0.1                  TRUE      FALSE      FALSE
_optimizer_unnest_corr_set_subq          FALSE                     TRUE      FALSE      FALSE
_optimizer_unnest_disjunctive_subq       FALSE                     TRUE      FALSE      FALSE
_optimizer_use_cbqt_star_transformation  FALSE                     TRUE      FALSE      FALSE
_optimizer_use_feedback                  FALSE                     TRUE      FALSE      FALSE
_px_partition_scan_enabled               FALSE                     TRUE      FALSE      FALSE
_px_ual_serial_input                     FALSE                     TRUE      FALSE      FALSE
_replace_virtual_columns                 FALSE                     TRUE      FALSE      FALSE

--可以看到这些隐含参数几乎不一样，这样看来单独在SQL中设置/*+ OPT_PARAM('OPTIMIZER_FEATURES_ENABLE','10.2.0.1')  不能生效，而应在会话中设置；