[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

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

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.valueb.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某個版本做了那些改進.