[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.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某个版本做了那些改进.
