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值来避免一些问题。
Master Note: Query Performance Degradation – Upgrade Related – Recommended Actions
Applies to:
Oracle Server – Enterprise Edition – Version: 9.0.1.0 to 11.1.0.8 – Release: 9.0.1 to 11.1
Information in this document applies to any platform.
Oracle Server Enterprise Edition – Version: 9.0.1.0 to 11.1.0.8
Purpose
Introduction
This article provides an action plan for gathering Query Performance Degradation After Upgrade diagnostics and suggests ideas for quickly recovering the situation.
Background
There are a number of reasons why the performance of a query or queries may degrade after an upgrade. The most likely cause is that the query being executed is now using a different access path than previously. Common causes are:
Note: This article covers a specific case where Oracle software has changed or has been upgraded. For cases that do not involve upgrades, See:
For more general Query Performance Issues see:
Last Review Date
April 27, 2010
Instructions for the Reader
Troubleshooting Details
Diagnostics
The following diagnostics at a minimum required for issue diagnosis. If possible, gather diagnostics that illustrate both the good and the bad case. This will speed up diagnosis.
Recovery Actions
Please investigate the possibility of quick fixes to restore performance. The following article provides a number of suggestions:
The most common solutions are:
For statistic gathering recommendations see:
Note:605439.1 Master Note: Recommendations for Gathering Optimizer Statistics on 10g
Note:749227.1 Master Note: Recommendations for Gathering Optimizer Statistics on 11g
Note:445126.1 – HOW TO: Create a Stored Outline Based Upon an Existing Cursor
Note:67536.1 Stored Outline Quick Reference
10g Release 2 (10.2)
Part Number B14211-03
Chapter 12 Automatic SQL Tuning
12.2 SQL Tuning Advisor
Note:271196.1 Automatic SQL Tuning – SQL Profiles.
If the query is newly created, or there are no previous example of ‘good’ performance, then see:
Note that Oracle Support is unable to undertake performance tuning consultancy. See the “Performance Tuning” section,In particular “When to Engage Support”, in
What are the differences between optimizer_features_enable settings ?
CONTENTS
——–
Explain the impact of the parameter OPTIMIZER_FEATURES_ENABLE
QUESTIONS & ANSWERS
——————-
What parameters are affected when OPTIMIZER_FEATURES_ENABLE=8.1.7 is changed on a 920
instance ?
The following parameters that are often referenced are changed:-
920 settings ====> 817 regressions
_COMPLEX_VIEW_MERGING = TRUE ====> FALSE
PARALLEL_BROADCAST_ENABLED = TRUE ====> FALSE
OPTIMIZER_MAX_PERMUTATIONS = 2000 ====> 80000
OPTIMIZER_DYNAMIC_SAMPLING = 1 ====> 0
_INDEX_JOIN_ENABLED = TRUE ====> FALSE
_NEW_INITIAL_JOIN_ORDERS = TRUE ====> FALSE
ALWAYS_ANTI_JOIN = CHOOSE ====> Removed
ALWAYS_SEMI_JOIN = CHOOSE ====> Removed
_UNNEST_SUBQUERY = TRUE ====> FALSE
_PUSH_JOIN_UNION_VIEW = TRUE ====> FALSE
_ORDERED_NESTED_LOOP = TRUE ====> FALSE
_TABLE_SCAN_COST_PLUS_ONE = TRUE ====> FALSE
_OPTIMIZER_COST_MODEL = CHOOSE ====> IO
_NEW_SORT_COST_ESTIMATE = TRUE ====> FALSE
_GS_ANTI_SEMI_JOIN_ALLOWED = TRUE ====> FALSE
_CPU_TO_IO = 0 ====> 100
_PRED_MOVE_AROUND = TRUE ====> FALSE
_BTREE_BITMAP_PLANS = TRUE ====> FALSE
_PUSH_JOIN_PREDICATE = TRUE ====> FALSE
FYI:This is not a complete list but a list that includes the parameters that
effect the optimizer most frequently.
Database has been upgraded from 9.2.0.6 to 10.2.0.4. What happens to statistics after modifying the Compatible parameter from 9.2.0 to 10.2.0? Do statistics need to be re-analyzed? This is a 6TB DB on production and takes 8-10 hours to analyze statistics.
I consulted your question with DBPERF team, here is the answer to your question:
1. when upgrading, stats should be collected in the new version.
2. the stats would not change due to this Compatible parameter