Difference between parameter COMPATIBLE and OPTIMIZER_FEATURES_ENABLE

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值来避免一些问题。


Posted

in

by

Tags:

Comments

3 responses to “Difference between parameter COMPATIBLE and OPTIMIZER_FEATURES_ENABLE”

  1. admin Avatar
    admin

    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:

    • Optimizer changes causing different plan selection -This can be due to bug fixes or new features
    • Optimizer changes causing the same plan to perform differently
    • Hardware/Environment changes causing the same plan to perform differently

    Note: This article covers a specific case where Oracle software has changed or has been upgraded. For cases that do not involve upgrades, See: 

    Note:742112.1 Master Note: Query Performance Degradation – Recommended Actions

    For more general Query Performance Issues see:

    Note:398838.1 Master Note: FAQ: Query Tuning Frequently Asked Questions

    Last Review Date

    April 27, 2010

    Instructions for the Reader

    A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

    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.

    • The query text
    • The execution plan – See: 
    Note:235530.1 Master Note: Recommended Method for Obtaining a Formatted Explain Plan
    • If possible, use SQLTXPLAIN with the “Xecute” option
    • If the query in question does not complete in a reasonable timeframe, please use the Xtract option rather than the Xecute option of SQLTXPLAIN for the bad plan

    NOTE: With Upgrade issues it is particularly important to have the ‘before’ and ‘after’ information for the query.

    • 10046 trace – See:
    Note:376442.1 Master Note: Recommended Method for Obtaining 10046 trace for Tuning
    • Workarounds: If workarounds for this issue have been found, please provide details as this will help to get to the root cause more quickly.
    • Changes: Similarly, details of any changes that have occurred since this worked will be very helpful. In particular, please provide details of the version changes that have occurred.
    • See the following note for comprehensive diagnostics details:
      Note:68735.1 Master Note: Diagnostics for Query Tuning Problems

    Recovery Actions

    Please investigate the possibility of quick fixes to restore performance. The following article provides a number of suggestions:

    Note:160089.1 TROUBLESHOOTING: Server Upgrade Results in Slow Query Performance

    The most common solutions are:

    • If the Oracle version has changed then performance should be tested against the previous optimizer version that was in place. The parameter <<Parameter:OPTIMIZER_FEATURES_ENABLE>> allows users to specify which optimizer level should be used, with prior versions being acceptable in the format X.X.X.X. For example:
      SQL> alter session set optimizer_features_enable = ‘X.X.X.X’;

    If this parameter improves performance, please advise support and outline whether this can be used as a workaround in the short term. See the following for more details:

    Note:62337.1 Init.ora Parameter “OPTIMIZER_FEATURES_ENABLE” Reference Note
    • Accurate statistics are vitally important to optimizer plan choices. Different versions may have different defaults which gather larger or smaller samples of data and thus produce different statistics. In some circumstances, this may lead to differing access paths. Remember that regathering statistics has the potential to adversely affect numerous queries and so should be embarked on with caution. From 10gR2 statistics can be restored using:
      Note:452011.1 Master Note: Restoring table statistics in 10G onwards

      For statistic gathering recommendations see:

      Note:388474.1 Master Note: Recommendations for Gathering Optimizer Statistics on 9i
      Note:605439.1 Master Note: Recommendations for Gathering Optimizer Statistics on 10g
      Note:749227.1 Master Note: Recommendations for Gathering Optimizer Statistics on 11g
    • If the previous version of the system is still available there are a number of options available to assist. These include:
      • Storing outlines of problem queries on the previous(working) version and applying to the new version. See:
        Note:728647.1 How to Transfer Stored Outlines from One Database to Another (9i and above)

        Note:445126.1 – HOW TO: Create a Stored Outline Based Upon an Existing Cursor
        Note:67536.1 Stored Outline Quick Reference

      • If problems have occurred following statistics gathering on the new version then transferring statistics from the previous version to the current version may be helpful. See:
        Note:117203.1 How to Use DBMS_STATS to Move Statistics to a Different Database

    • With the appropriate licenses, consider using SQL Tuning Advisor to tune and advise on queries. See:
      Oracle� Database Performance Tuning Guide
      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.

    • For more general solutions See:
      Note:742112.1 Master Note: Query Performance Degradation – Recommended Actions

    If the query is newly created, or there are no previous example of ‘good’ performance, then see:

    Note:372431.1 Master Note: TROUBLESHOOTING: Tuning a New Query

    Note that Oracle Support is unable to undertake performance tuning consultancy. See the “Performance Tuning” section,In particular “When to Engage Support”, in

    Note:166650.1 Working Effectively With Global Customer Support
  2. admin Avatar
    admin

    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.

  3. maclean Avatar

    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

Leave a Reply

Your email address will not be published. Required fields are marked *