20160318了解oracle版本更新的一些參數變化

[20160318]了解oracle版本更新的一些參數變化.txt

--曾經寫過一篇了解oracle版本更新後一些參數變化,可以通過如下連接配接了解:

<a href="http://blog.itpub.net/267265/viewspace-1655594/">http://blog.itpub.net/267265/viewspace-1655594/</a>

--實際上還有一個簡單的方法就是提示opt_param('optimizer_features_enable' '11.2.0.1') ,然後順便執行一條sql語句,檢視執行計

--劃就可以那些參數發生了變化.通過例子說明:

1.環境:

SCOTT@book&gt; @ &amp;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&gt; show parameter  OPTIMIZER_FEATURES_ENABLE

NAME                                 TYPE    VALUE

------------------------------------ ------- ---------

optimizer_features_enable            string  11.2.0.4

2.簡單執行某個語句:

SCOTT@book&gt; select  /*+ opt_param('optimizer_features_enable' '10.2.0.4') */ * from dept where deptno=10;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

SCOTT@book&gt; @ &amp;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') */ ,明顯少許多.

      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')