[20150515]簡單瞭解某個版本有什麼新特性

[20150515]簡單瞭解某個版本有什麼新特性.txt

--如果想簡單的瞭解某個版本的新特性,可以設定optimizer_features_enable

SCOTT@test> show parameter optimizer_features_enable
NAME                                 TYPE    VALUE
------------------------------------ ------- --------
optimizer_features_enable            string  11.2.0.3

--匯出相關引數,對比可以瞭解一些特性,拿11.2.0.3做一個測試:

1.測試:
SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> show parameter optimizer_features_enable
NAME                                 TYPE    VALUE
------------------------------------ ------- --------
optimizer_features_enable            string  11.2.0.3

create table params_11g
as
select ksppinm name, ksppstvl value, ksppdesc description
from x$ksppi x, x$ksppcv y
where (x.indx = y.indx)
order by name;

2.修改引數optimizer_features_enable:
SCOTT@test> alter system set optimizer_features_enable='10.2.0.4' scope=memory;
System altered.

create table params_10g
as
select ksppinm name, ksppstvl value, ksppdesc description
from x$ksppi x, x$ksppcv y
where (x.indx = y.indx)
order by name;

3.然後對比表params_*g:

SCOTT@test> column name format a40
SCOTT@test> select a.name,a.value value_10g,b.value value_11g from params_10g a ,params_11g b where a.name=b.name and a.value<>b.value;
NAME                                     VALUE_10G                      VALUE_11G
---------------------------------------- ------------------------------ ------------------------------
_aggregation_optimization_settings       32                             0
_and_pruning_enabled                     FALSE                          TRUE
_bloom_folding_enabled                   FALSE                          TRUE
_bloom_pruning_enabled                   FALSE                          TRUE
_connect_by_use_union_all                OLD_PLAN_MODE                  TRUE
_first_k_rows_dynamic_proration          FALSE                          TRUE
_nlj_batching_enabled                    0                              1
_optimizer_adaptive_cursor_sharing       FALSE                          TRUE
_optimizer_coalesce_subqueries           FALSE                          TRUE
_optimizer_connect_by_elim_dups          FALSE                          TRUE
_optimizer_distinct_agg_transform        FALSE                          TRUE
_optimizer_distinct_placement            FALSE                          TRUE
_optimizer_eliminate_filtering_join      FALSE                          TRUE
_optimizer_enable_density_improvements   FALSE                          TRUE
_optimizer_enable_extended_stats         FALSE                          TRUE
_optimizer_enable_table_lookup_by_nl     FALSE                          TRUE
_optimizer_extend_jppd_view_types        FALSE                          TRUE
_optimizer_extended_cursor_sharing_rel   NONE                           SIMPLE
_optimizer_extended_stats_usage_control  255                            192
_optimizer_false_filter_pred_pullup      FALSE                          TRUE
_optimizer_fast_access_pred_analysis     FALSE                          TRUE
_optimizer_fast_pred_transitivity        FALSE                          TRUE
_optimizer_full_outer_join_to_outer      FALSE                          TRUE
_optimizer_group_by_placement            FALSE                          TRUE
_optimizer_improve_selectivity           FALSE                          TRUE
_optimizer_interleave_jppd               FALSE                          TRUE
_optimizer_join_factorization            FALSE                          TRUE
_optimizer_multi_level_push_pred         FALSE                          TRUE
_optimizer_native_full_outer_join        OFF                            FORCE
_optimizer_null_aware_antijoin           FALSE                          TRUE
_optimizer_table_expansion               FALSE                          TRUE
_optimizer_try_st_before_jppd            FALSE                          TRUE
_optimizer_undo_cost_change              10.2.0.4                       11.2.0.3
_optimizer_unnest_corr_set_subq          FALSE                          TRUE
_optimizer_unnest_disjunctive_subq       FALSE                          TRUE
_optimizer_use_cbqt_star_transformation  FALSE                          TRUE
_optimizer_use_feedback                  FALSE                          TRUE
_px_partition_scan_enabled               FALSE                          TRUE
_replace_virtual_columns                 FALSE                          TRUE
optimizer_features_enable                10.2.0.4                       11.2.0.3

40 rows selected.

4.再瞭解這些引數的含義,就知道oracle某個版本做了那些改進.