SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production SQL> 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.0 optimizer_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 compatible NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 10.2.0.3.0 SQL> 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.trc SQL> 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 ~]$ exit exit SQL> 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 bytes Fixed Size 2086288 bytes Variable Size 754977392 bytes Database Buffers 1862270976 bytes Redo Buffers 14688256 bytes Database 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.trc SQL> 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_.tmp creation 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.trc SQL> 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 found SQL> oradebug dump redohdr 3; Statement processed. SQL> oradebug tracefile_name; /s01/10gdb/admin/YOUYUS/udump/youyus_ora_5739.trc SQL> 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 compatible NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 10.2.0.4 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 2634022912 bytes Fixed Size 2086288 bytes Variable Size 754977392 bytes Database Buffers 1862270976 bytes Redo Buffers 14688256 bytes ORA-00201: control file version 10.2.0.4.0 incompatible with ORACLE version 10.2.0.3.0 ORA-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 mounted ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 2634022912 bytes Fixed Size 2086288 bytes Variable Size 754977392 bytes Database Buffers 1862270976 bytes Redo Buffers 14688256 bytes Database mounted. SQL> alter database open; Database altered.
一般情况下我们无需十分关注Compatible参数(默认值往往是恰当的),不过如果您的系统中需要用到传输表空间,复杂的DataGuard环境等时Compatible参数需要我们特别留意。
接下来我们要看看OPTIMIZER_FEATURES_ENABLE是如何影响优化器的Optimizer:
SQL> show parameter optimizer_features_enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_features_enable string 10.2.0.4 SQL> 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.7 optimizer_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 80000 query_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 FALSE optimizer_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 FALSE skip_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 FALSE 77 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.8 optimizer_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 FALSE query_rewrite_enabled TRUE FALSE _mmv_query_rewrite_enabled TRUE FALSE _local_communication_costing_enabled TRUE FALSE optimizer_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 FALSE skip_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 48 rows selected SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production SQL> 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 TRUE 35 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值来避免一些问题。
Leave a Reply