20160318瞭解oracle版本升級的一些引數變化

[20160318]瞭解oracle版本升級的一些引數變化.txt

--曾經寫過一篇瞭解oracle版本升級後一些引數變化,可以透過如下連線瞭解:
http://blog.itpub.net/267265/viewspace-1655594/

--實際上還有一個簡單的方法就是提示opt_param('optimizer_features_enable' '11.2.0.1') ,然後順便執行一條sql語句,檢視執行計
--劃就可以那些引數發生了變化.透過例子說明:

1.環境:

SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> show parameter  OPTIMIZER_FEATURES_ENABLE
NAME                                 TYPE    VALUE
------------------------------------ ------- ---------
optimizer_features_enable            string  11.2.0.4

2.簡單執行某個語句:

SCOTT@book> select  /*+ opt_param('optimizer_features_enable' '10.2.0.4') */ * from dept where deptno=10;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@book> @ &r/dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  by919k30x9mas, child number 0
-------------------------------------
select  /*+ opt_param('optimizer_features_enable' '10.2.0.4') */ * from
dept where deptno=10
Plan hash value: 2852011669
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |        |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |    20 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |      1 |       |     0   (0)|          |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / DEPT@SEL$1
   2 - SEL$1 / DEPT@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optimizer_undo_cost_change' '11.2.0.4')
      OPT_PARAM('_optimizer_null_aware_antijoin' 'true')
      OPT_PARAM('_optimizer_extend_jppd_view_types' 'true')
      OPT_PARAM('_replace_virtual_columns' 'true')
      OPT_PARAM('_first_k_rows_dynamic_proration' 'true')
      OPT_PARAM('_bloom_pruning_enabled' 'true')
      OPT_PARAM('_optimizer_multi_level_push_pred' 'true')
      OPT_PARAM('_optimizer_group_by_placement' 'true')
      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_native_full_outer_join' 'force')
      OPT_PARAM('_optimizer_enable_extended_stats' 'true')
      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')
      OPT_PARAM('_optimizer_full_outer_join_to_outer' 'true')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPTNO"=10)
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

--如果 /*+ opt_param('optimizer_features_enable' '11.2.0.1') */ ,明顯少許多.

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optimizer_undo_cost_change' '11.2.0.4')
      OPT_PARAM('_optimizer_extended_stats_usage_control' 192)
      OPT_PARAM('_px_partition_scan_enabled' 'true')
      OPT_PARAM('_optimizer_false_filter_pred_pullup' 'true')
      OPT_PARAM('_optimizer_enable_table_lookup_by_nl' 'true')
      OPT_PARAM('_optimizer_full_outer_join_to_outer' 'true')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))
      END_OUTLINE_DATA
  */