SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64biPL/SQL Release 10.2.0.4.0 - ProductionCORE 10.2.0.4.0 ProductionTNS for Linux: Version 10.2.0.4.0 - ProductionNLSRTL Version 10.2.0.4.0 - ProductionSQL> set linesize 200;SQL> col name for a30;SQL> col value for a20;SQL> select name,value 2 from v$system_parameter 3 where name in ('compatible', 'optimizer_features_enable');NAME VALUE------------------------------ --------------------compatible 10.2.0.3.0optimizer_features_enable 10.2.0.4/* 10.2.0.4升级完毕后compatible参数默认值为10.2.0.3，不同于optimizer_features_enable */ 有人会问这2个参数不都是用来描绘数据库兼容性的吗？为什么在默认情况下这2个参数的值还会不同于实际的版本号10.2.0.4呢？这2个参数有什么依存关系吗？在我的系统中，我该如何设置它们？简单的2个参数，其实蕴藏着大量的问题(不管是对初学者，还是有一定经验的人)。 不如我们来深入的分析一下: SQL> show parameter compatibleNAME TYPE VALUE------------------------------------ ----------- ------------------------------compatible string 10.2.0.3.0SQL> oradebug setmypid;Statement processed.SQL> oradebug dump file_hdrs 3;Statement processed./* 利用oradebug dump file_hdrs命令转储数据文件头信息， level 1 列出Record of datafiles in controlfile ( for practice compare with controlfile dump) level 2 列出Level 1 + generic information level 3 列出Level 2 + additional datafile header information*/SQL> oradebug tracefile_name;/s01/10gdb/admin/YOUYUS/udump/youyus_ora_5328.trcSQL> host[maclean@rh2 ~]$ cat /s01/10gdb/admin/YOUYUS/udump/youyus_ora_5328.trc|grep "Compatibility" Compatibility Vsn = 169870080=0xa200300 Compatibility Vsn = 169870080=0xa200300 Compatibility Vsn = 169870080=0xa200300 Compatibility Vsn = 169870080=0xa200300 Compatibility Vsn = 169870080=0xa200300 Compatibility Vsn = 169870080=0xa200300 Compatibility Vsn = 169870080=0xa200300 Compatibility Vsn = 169870080=0xa200300/* 可以看到在compatible为10.2.0.3的情况下，各创建的数据文件的兼容性Compatibility为0xa2003也就是十进制的102003 */[maclean@rh2 ~]$ exitexitSQL> alter system set compatible='10.2.0.4' scope=spfile;System altered.SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup;ORACLE instance started.Total System Global Area 2634022912 bytesFixed Size 2086288 bytesVariable Size 754977392 bytesDatabase Buffers 1862270976 bytesRedo Buffers 14688256 bytesDatabase mounted.Database opened.SQL> oradebug setmypid;Statement processed.SQL> oradebug dump file_hdrs 3;Statement processed.SQL> oradebug tracefile_name;/s01/10gdb/admin/YOUYUS/udump/youyus_ora_5562.trcSQL> host[maclean@rh2 ~]$ cat /s01/10gdb/admin/YOUYUS/udump/youyus_ora_5562.trc|grep Compatibility Compatibility Vsn = 169870336=0xa200400 Compatibility Vsn = 169870336=0xa200400 Compatibility Vsn = 169870336=0xa200400 Compatibility Vsn = 169870336=0xa200400 Compatibility Vsn = 169870336=0xa200400 Compatibility Vsn = 169870336=0xa200400 Compatibility Vsn = 169870336=0xa200400 Compatibility Vsn = 169870080=0xa200300/* 可以看到出去临时文件外的所有数据文件的兼容性都上升到了0xa2004也就是102004 */TEMP FILE #1: External File #201 (name #11) /s01/10gdb/oradata/YOUYUS/datafile/o1_mf_temp_65psr6r1_.tmpcreation size=2560 block size=8192 status=0x1e head=11 tail=11 dup=1 tablespace 3, index=5 krfil=1 prev_file=0 unrecoverable scn: 0x0000.00093305 08/05/2010 21:36:06 V10 STYLE FILE HEADER: Compatibility Vsn = 169870080=0xa200300 Db ID=3962573566=0xec3012fe, Db Name='YOUYUS' Activation ID=0=0x0 Control Seq=4394=0x112a, File size=218496=0x35580 File Number=1, Blksiz=8192, File Type=6 TEMP FILE/* 我们也可以通过转储控制文件信息来了解实际的情况 */SQL> oradebug setmypid;Statement processed.SQL> oradebug unlimit;Statement processed.SQL> oradebug dump controlf 3;Statement processed./* 关于如何转储控制文件我在以前的文章中已经介绍过了 * /[maclean@rh2 ~]$ cat /s01/10gdb/admin/YOUYUS/udump/youyus_ora_5615.trc|grep -A 4 -B 2 "Compatibility"DUMP OF CONTROL FILES, Seq # 6348 = 0x18cc V10 STYLE FILE HEADER: Compatibility Vsn = 169870336=0xa200400 Db ID=3962573566=0xec3012fe, Db Name='YOUYUS' Activation ID=0=0x0 Control Seq=6348=0x18cc, File size=430=0x1ae File Number=0, Blksiz=16384, File Type=1 CONTROL/* 控制文件的兼容性也提升到了10.2.0.4 * /SQL> select member from v$logfile;MEMBER--------------------------------------------------------------------------------/s01/10gdb/oradata/YOUYUS/onlinelog/o1_mf_5_67xszwst_.log/s01/10gdb/flash_recovery_area/YOUYUS/onlinelog/o1_mf_5_67xt0bnr_.log/s01/10gdb/oradata/YOUYUS/onlinelog/o1_mf_7_67xt17xq_.log/s01/10gdb/flash_recovery_area/YOUYUS/onlinelog/o1_mf_7_67xt1nnd_.log/s01/10gdb/oradata/YOUYUS/onlinelog/o1_mf_10_67xt2w5p_.log/s01/10gdb/flash_recovery_area/YOUYUS/onlinelog/o1_mf_10_67xt39c2_.log/s01/10gdb/oradata/YOUYUS/onlinelog/o1_mf_6_67xsmb1s_.log/s01/10gdb/flash_recovery_area/YOUYUS/onlinelog/o1_mf_6_67xsmrnt_.log..........SQL> oradebug setmypid;Statement processed.SQL> oradebug unlimit;Statement processed.SQL> oradebug dump redohdr 3;Statement processed./* 这里利用oradebug dump redohdr命令转储日志文件头， level 1 转储Record of log file records in controlfile level 2 转储Level 1 + generic information level 3 转储Level 2 + additional log file header information. */SQL> oradebug tracefile_name;/s01/10gdb/admin/YOUYUS/udump/youyus_ora_5694.trcSQL> host[maclean@rh2 ~]$ cat /s01/10gdb/admin/YOUYUS/udump/youyus_ora_5694.trc|grep "Compatibility" Compatibility Vsn = 169870080=0xa200300 Compatibility Vsn = 169870336=0xa200400 Compatibility Vsn = 169870080=0xa200300 Compatibility Vsn = 169870080=0xa200300 Compatibility Vsn = 169870080=0xa200300 Compatibility Vsn = 169870080=0xa200300 Compatibility Vsn = 169870080=0xa200300 Compatibility Vsn = 169870080=0xa200300/* 既有的联机日志文件中的Compatibility并未变化；仔细想想，这很合理！ */SQL> alter database add logfile group 15 size 20M;Database altered.SQL> oradebug setmypid;Statement processed.SQL> oradebug dump redohdrs 3;ORA-00076: dump REDOHDRS not foundSQL> oradebug dump redohdr 3;Statement processed.SQL> oradebug tracefile_name;/s01/10gdb/admin/YOUYUS/udump/youyus_ora_5739.trcSQL> host[maclean@rh2 ~]$ cat /s01/10gdb/admin/YOUYUS/udump/youyus_ora_5739.trc|grep "Compatibility" Compatibility Vsn = 169870080=0xa200300 Compatibility Vsn = 169870336=0xa200400 Compatibility Vsn = 169870080=0xa200300 Compatibility Vsn = 169870080=0xa200300 Compatibility Vsn = 169870080=0xa200300 Compatibility Vsn = 169870080=0xa200300 Compatibility Vsn = 169870080=0xa200300 Compatibility Vsn = 169870080=0xa200300 Compatibility Vsn = 169870336=0xa200400/* 加入的新的日志文件，Compatibility兼容性为10.2.0.4 */SQL> alter system archive log current;System altered.SQL> /System altered.SQL> /System altered./* 归档后也能起到同样的作用 */[maclean@rh2 ~]$ cat /s01/10gdb/admin/YOUYUS/udump/youyus_ora_5772.trc|grep "Compatibility" Compatibility Vsn = 169870336=0xa200400 Compatibility Vsn = 169870336=0xa200400 Compatibility Vsn = 169870336=0xa200400 Compatibility Vsn = 169870336=0xa200400 Compatibility Vsn = 169870336=0xa200400 Compatibility Vsn = 169870336=0xa200400 Compatibility Vsn = 169870336=0xa200400 Compatibility Vsn = 169870336=0xa200400 Compatibility Vsn = 169870336=0xa200400
COMPATIBLE参数指定了Oracle会写出怎样的内容到磁盘上，当我们以较低的COMPATIBLE值创建数据库时，Oracle不会采用最新的重做日志格式，Oracle不会采用新版本中的数据文件格式；我们在使用Oracle 10g，如果我们设置compatible为”9.2.0″，那么Oracle创建的数据文件不会同实际9ir2版本中的有什么不同。通过这种配置，您仍可以利用10g软件中的各种特性和优化特性，compatible参数不会影响到它们!
“compatible simply tells us what we can write to disk. We won’t use new redo log formats, we won’t write new block level information, we won’t create datafiles that have anything 9ir2 didn’t have.But you would be entirely using the 10g software, optimizer, set of features.compatible would not change this all. ” 但是请你注意，compatible参数是无法降级的(downgrade)，也就是说该参数只能增大而无法缩小:SQL> show parameter compatibleNAME TYPE VALUE------------------------------------ ----------- ------------------------------compatible string 10.2.0.4SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area 2634022912 bytesFixed Size 2086288 bytesVariable Size 754977392 bytesDatabase Buffers 1862270976 bytesRedo Buffers 14688256 bytesORA-00201: control file version 10.2.0.4.0 incompatible with ORACLE version10.2.0.3.0ORA-00202: control file:'/s01/10gdb/oradata/YOUYUS/controlfile/o1_mf_65psqyh6_.ctl'/* Oracle会比较控制文件中的Compatibility和Compatible参数，若不兼容则出现ORA-00201错误 */SQL> alter system set compatible='10.2.0.4' scope=spfile;System altered.SQL> shutdown immediate;ORA-01507: database not mountedORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area 2634022912 bytesFixed Size 2086288 bytesVariable Size 754977392 bytesDatabase Buffers 1862270976 bytesRedo Buffers 14688256 bytesDatabase mounted.SQL> alter database open;Database altered.
一般情况下我们无需十分关注Compatible参数(默认值往往是恰当的)，不过如果您的系统中需要用到传输表空间，复杂的DataGuard环境等时Compatible参数需要我们特别留意。
接下来我们要看看OPTIMIZER_FEATURES_ENABLE是如何影响优化器的Optimizer:
SQL> show parameter optimizer_features_enableNAME TYPE VALUE------------------------------------ ----------- ------------------------------optimizer_features_enable string 10.2.0.4SQL> drop table YOUYUS;Table dropped.SQL> create table YOUYUS as select * from dba_objects;Table created.SQL> exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'YOUYUS',estimate_percent => 100,cascade => TRUE);PL/SQL procedure successfully completed.SQL> explain plan for select owner,count(*) from YOUYUS group by owner;Explained.SQL> set pagesize 1400;SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 2940504347-----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 26 | 156 | 158 (3)| 00:00:02 || 1 | HASH GROUP BY | | 26 | 156 | 158 (3)| 00:00:02 || 2 | TABLE ACCESS FULL| YOUYUS | 50775 | 297K| 155 (1)| 00:00:02 |-----------------------------------------------------------------------------9 rows selected./* 这里采用了HASH GROUP BY 算法替代从前的SORT GROUP BY */SQL> alter system set optimizer_features_enable='8.1.7.4'; alter system set optimizer_features_enable='8.1.7.4'*ERROR at line 1:ORA-00096: invalid value 8.1.7.4 for parameter optimizer_features_enable, must be from among 10.2.0.4.1, 10.2.0.4, 10.2.0.3, 10.2.0.2,10.2.0.1, 10.1.0.5, 10.1.0.4, 10.1.0.3, 10.1.0, 9.2.0.8, 9.2.0, 9.0.1, 9.0.0, 8.1.7, 8.1.6, 8.1.5, 8.1.4, 8.1.3, 8.1.0, 8.0.7, 8.0.6,8.0.5, 8.0.4, 8.0.3, 8.0.0/* optimizer_features_enable所有能设置的值 */SQL> alter system set optimizer_features_enable='8.1.7';System altered.SQL> explain plan for select owner,count(*) from YOUYUS group by owner;Explained.SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 1349668650---------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |---------------------------------------------------------------------| 0 | SELECT STATEMENT | | 26 | 156 | | 334 || 1 | SORT GROUP BY | | 26 | 156 | 616K| 334 || 2 | TABLE ACCESS FULL| YOUYUS | 50775 | 297K| | 68 |---------------------------------------------------------------------Note----- - cpu costing is off (consider enabling it)13 rows selected./* optimizer_features_enable设置为8.1.7，采用CBO的情况下优化器采用了SORT GROUP BY 方式，成本要高出HASH GROUP BY一倍 是什么阻止了优化器使用HASH算法呢？*/SQL> create table optimizer_817 as 2 SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ 3 FROM SYS.x$ksppi x, SYS.x$ksppcv y 4 WHERE x.inst_id = USERENV ('Instance') 5 AND y.inst_id = USERENV ('Instance') 6 AND x.indx = y.indx;Table created.SQL> alter system set optimizer_features_enable='10.2.0.4';System altered.SQL> create table optimizer_10204 as 2 SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ 3 FROM SYS.x$ksppi x, SYS.x$ksppcv y 4 WHERE x.inst_id = USERENV ('Instance') 5 AND y.inst_id = USERENV ('Instance') 6 AND x.indx = y.indx;Table created.SQL> select a.name "parameter", a.value "10.2.0.4", b.value "8.1.7" 2 from optimizer_10204 a, optimizer_817 b 3 where a.name = b.name 4 and a.value != b.value;parameter 10.2.0.4 8.1.7-------------------------------------------------------------------------------- -------------------- --------------------optimizer_features_enable 10.2.0.4 8.1.7optimizer_mode ALL_ROWS CHOOSE_always_anti_join CHOOSE OFF_partition_view_enabled TRUE FALSE_b_tree_bitmap_plans TRUE FALSE_cpu_to_io 0 100_optimizer_extended_cursor_sharing UDO NONE_optimizer_cost_model CHOOSE IO_optimizer_undo_cost_change 10.2.0.4 8.1.7_optimizer_system_stats_usage TRUE FALSE_new_sort_cost_estimate TRUE FALSE_complex_view_merging TRUE FALSE_unnest_subquery TRUE FALSE_pred_move_around TRUE FALSE_px_ual_serial_input TRUE FALSE_remove_aggr_subquery TRUE FALSE_optimizer_cost_based_transformation LINEAR OFF_optimizer_squ_bottomup TRUE FALSE_push_join_predicate TRUE FALSE_push_join_union_view TRUE FALSE_push_join_union_view2 TRUE FALSE_optimizer_skip_scan_enabled TRUE FALSE_optimizer_join_sel_sanity_check TRUE FALSE_parallel_broadcast_enabled TRUE FALSE_always_semi_join CHOOSE OFF_ordered_nested_loop TRUE FALSE_optimizer_max_permutations 2000 80000query_rewrite_enabled TRUE FALSE_mmv_query_rewrite_enabled TRUE FALSE_local_communication_costing_enabled TRUE FALSE_index_join_enabled TRUE FALSE_table_scan_cost_plus_one TRUE FALSE_cost_equality_semi_join TRUE FALSE_new_initial_join_orders TRUE FALSE_optim_peek_user_binds TRUE FALSE_gs_anti_semi_join_allowed TRUE FALSE_optim_new_default_join_sel TRUE FALSEoptimizer_dynamic_sampling 2 0_pre_rewrite_push_pred TRUE FALSE_optimizer_new_join_card_computation TRUE FALSE_union_rewrite_for_gs YES_GSET_MVS OFF_generalized_pruning_enabled TRUE FALSE_optim_adjust_for_part_skews TRUE FALSE_optimizer_compute_index_stats TRUE FALSE_optimizer_push_pred_cost_based TRUE FALSE_optimizer_filter_pred_pullup TRUE FALSE_optimizer_connect_by_cost_based TRUE FALSE_optimizer_connect_by_combine_sw TRUE FALSE_right_outer_hash_enable TRUE FALSEskip_unusable_indexes TRUE FALSE_optimizer_correct_sq_selectivity TRUE FALSE_optimizer_dim_subq_join_sel TRUE FALSE_query_rewrite_setopgrw_enable TRUE FALSE_optimizer_join_order_control 3 0_bloom_filter_enabled TRUE FALSE_optimizer_join_elimination_enabled TRUE FALSE_gby_hash_aggregation_enabled TRUE FALSE_globalindex_pnum_filter_enabled TRUE FALSE_sql_model_unfold_forloops RUN_TIME COMPILE_TIME_optimizer_cost_hjsmj_multimatch TRUE FALSE_optimizer_transitivity_retain TRUE FALSE_px_pwg_enabled TRUE FALSE_optimizer_cbqt_no_size_restriction TRUE FALSE_optimizer_enhanced_filter_push TRUE FALSE_optimizer_rownum_pred_based_fkr TRUE FALSE_optimizer_better_inlist_costing ALL OFF_optimizer_or_expansion DEPTH BREADTH_optimizer_outer_to_anti_enabled TRUE FALSE_optimizer_order_by_elimination_enabled TRUE FALSE_optimizer_star_tran_in_with_clause TRUE FALSE_selfjoin_mv_duplicates TRUE FALSE_dimension_skip_null TRUE FALSE_optimizer_complex_pred_selectivity TRUE FALSE_optimizer_rownum_bind_default 10 0_first_k_rows_dynamic_proration TRUE FALSE_optimizer_fkr_index_cost_bias 10 2_optimizer_sortmerge_join_inequality TRUE FALSE77 rows selected/* 惊讶吗?仅仅修改一个optimizer_features_enable会造成那么多隐式参数的变化， 造成优化器只能选择SORT GROUP BY方式的原因很简单，8.1.7版本中还没有HASH GROUP BY算法， 所以在optimizer_features_enable为8.1.7时_gby_hash_aggregation_enabled默认是FALSE(817中可并没有这个参数)*/SQL> create table optimizer_9208 as 2 SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ 3 FROM SYS.x$ksppi x, SYS.x$ksppcv y 4 WHERE x.inst_id = USERENV ('Instance') 5 AND y.inst_id = USERENV ('Instance') 6 AND x.indx = y.indx;Table created./* 9208与10.2.0.4间，优化参数细微的变化 */SQL> select a.name "parameter", a.value "10.2.0.4", b.value "9.2.0.8" 2 from optimizer_10204 a, optimizer_9208 b 3 where a.name = b.name 4 and a.value != b.value;parameter 10.2.0.4 9.2.0.8-------------------------------------------------------------------------------- -------------------- --------------------optimizer_features_enable 10.2.0.4 9.2.0.8optimizer_mode ALL_ROWS CHOOSE_partition_view_enabled TRUE FALSE_optimizer_extended_cursor_sharing UDO NONE_optimizer_undo_cost_change 10.2.0.4 9.2.0.8_px_ual_serial_input TRUE FALSE_remove_aggr_subquery TRUE FALSE_optimizer_cost_based_transformation LINEAR OFF_optimizer_squ_bottomup TRUE FALSE_push_join_union_view2 TRUE FALSE_optimizer_join_sel_sanity_check TRUE FALSEquery_rewrite_enabled TRUE FALSE_mmv_query_rewrite_enabled TRUE FALSE_local_communication_costing_enabled TRUE FALSEoptimizer_dynamic_sampling 2 1_optimizer_compute_index_stats TRUE FALSE_optimizer_push_pred_cost_based TRUE FALSE_optimizer_filter_pred_pullup TRUE FALSE_optimizer_connect_by_cost_based TRUE FALSE_optimizer_connect_by_combine_sw TRUE FALSE_right_outer_hash_enable TRUE FALSEskip_unusable_indexes TRUE FALSE_optimizer_correct_sq_selectivity TRUE FALSE_optimizer_dim_subq_join_sel TRUE FALSE_query_rewrite_setopgrw_enable TRUE FALSE_optimizer_join_order_control 3 0_bloom_filter_enabled TRUE FALSE_optimizer_join_elimination_enabled TRUE FALSE_gby_hash_aggregation_enabled TRUE FALSE_globalindex_pnum_filter_enabled TRUE FALSE_sql_model_unfold_forloops RUN_TIME COMPILE_TIME_optimizer_cost_hjsmj_multimatch TRUE FALSE_optimizer_transitivity_retain TRUE FALSE_px_pwg_enabled TRUE FALSE_optimizer_cbqt_no_size_restriction TRUE FALSE_optimizer_enhanced_filter_push TRUE FALSE_optimizer_rownum_pred_based_fkr TRUE FALSE_optimizer_better_inlist_costing ALL OFF_optimizer_or_expansion DEPTH BREADTH_optimizer_outer_to_anti_enabled TRUE FALSE_optimizer_order_by_elimination_enabled TRUE FALSE_optimizer_star_tran_in_with_clause TRUE FALSE_selfjoin_mv_duplicates TRUE FALSE_dimension_skip_null TRUE FALSE_optimizer_complex_pred_selectivity TRUE FALSE_optimizer_rownum_bind_default 10 0_first_k_rows_dynamic_proration TRUE FALSE_optimizer_fkr_index_cost_bias 10 248 rows selectedSQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionPL/SQL Release 11.2.0.1.0 - ProductionCORE 11.2.0.1.0 ProductionTNS for Linux: Version 11.2.0.1.0 - ProductionNLSRTL Version 11.2.0.1.0 - ProductionSQL> select a.name "parameter", a.value "10.2.0.4", b.value "11.2.0.1" 2 from optimizer_10204 a, optimizer_11201 b 3 where a.name = b.name 4 and a.value != b.value;parameter 10.2.0.4 11.2.0.1-------------------------------------------------------------------------------- -------------------- --------------------optimizer_features_enable 10.2.0.4 11.2.0.1_optimizer_null_aware_antijoin FALSE TRUE_optimizer_extended_cursor_sharing_rel NONE SIMPLE_optimizer_adaptive_cursor_sharing FALSE TRUE_optimizer_undo_cost_change 10.2.0.4 11.2.0.1_nlj_batching_enabled 0 1_optimizer_extend_jppd_view_types FALSE TRUE_optimizer_connect_by_elim_dups FALSE TRUE_connect_by_use_union_all OLD_PLAN_MODE TRUE_replace_virtual_columns FALSE TRUE_bloom_folding_enabled FALSE TRUE_bloom_pruning_enabled FALSE TRUE_first_k_rows_dynamic_proration FALSE TRUE_optimizer_multi_level_push_pred FALSE TRUE_optimizer_group_by_placement FALSE TRUE_optimizer_distinct_placement FALSE TRUE_optimizer_coalesce_subqueries FALSE TRUE_optimizer_enable_density_improvements FALSE TRUE_optimizer_improve_selectivity FALSE TRUE_optimizer_native_full_outer_join OFF FORCE_optimizer_enable_extended_stats FALSE TRUE_optimizer_extended_stats_usage_control 255 224_optimizer_fast_pred_transitivity FALSE TRUE_optimizer_fast_access_pred_analysis FALSE TRUE_optimizer_unnest_disjunctive_subq FALSE TRUE_optimizer_unnest_corr_set_subq FALSE TRUE_optimizer_distinct_agg_transform FALSE TRUE_aggregation_optimization_settings 32 0_optimizer_eliminate_filtering_join FALSE TRUE_optimizer_join_factorization FALSE TRUE_optimizer_use_cbqt_star_transformation FALSE TRUE_optimizer_table_expansion FALSE TRUE_and_pruning_enabled FALSE TRUE_optimizer_use_feedback FALSE TRUE_optimizer_try_st_before_jppd FALSE TRUE35 rows selected/* 以上为11.2.0.1与10.2.0.4间，优化参数细微的变化；值得注意的有_optimizer_enable_extended_stats(是否采用扩展统计信息)， _bloom_folding_enabled(看的出来11g中加强了布隆算法)等等 */ 大多数时候我们无需修改optimizer_features_enable参数，只有在Oracle Support建议的前提下，经过对应用测试后，我们才会尝试使用衰减(downgrade)的optimizer_features_enable值来避免一些问题。