[20160318]了解oracle版本升级的一些参数变化.txt
--曾经写过一篇了解oracle版本升级后一些参数变化,可以通过如下连接了解:
http://blog.itpub.net/267265/viewspace-1655594/
--实际上还有一个简单的方法就是提示opt_param('optimizer_features_enable' '11.2.0.1') ,然后顺便执行一条sql语句,查看执行计
--划就可以那些参数发生了变化.通过例子说明:
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> show parameter OPTIMIZER_FEATURES_ENABLE
NAME TYPE VALUE
------------------------------------ ------- ---------
optimizer_features_enable string 11.2.0.4
2.简单执行某个语句:
SCOTT@book> select /*+ opt_param('optimizer_features_enable' '10.2.0.4') */ * from dept where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SCOTT@book> @ &r/dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID by919k30x9mas, child number 0
-------------------------------------
select /*+ opt_param('optimizer_features_enable' '10.2.0.4') */ * from
dept where deptno=10
Plan hash value: 2852011669
----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DEPT@SEL$1
2 - SEL$1 / DEPT@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
DB_VERSION('11.2.0.4')
OPT_PARAM('_optimizer_undo_cost_change' '11.2.0.4')
OPT_PARAM('_optimizer_null_aware_antijoin' 'true')
OPT_PARAM('_optimizer_extend_jppd_view_types' 'true')
OPT_PARAM('_replace_virtual_columns' 'true')
OPT_PARAM('_first_k_rows_dynamic_proration' 'true')
OPT_PARAM('_bloom_pruning_enabled' 'true')
OPT_PARAM('_optimizer_multi_level_push_pred' 'true')
OPT_PARAM('_optimizer_group_by_placement' 'true')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'simple')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'true')
OPT_PARAM('_optimizer_improve_selectivity' 'true')
OPT_PARAM('_optimizer_enable_density_improvements' 'true')
OPT_PARAM('_optimizer_native_full_outer_join' 'force')
OPT_PARAM('_optimizer_enable_extended_stats' 'true')
OPT_PARAM('_nlj_batching_enabled' 1)
OPT_PARAM('_optimizer_extended_stats_usage_control' 192)
OPT_PARAM('_bloom_folding_enabled' 'true')
OPT_PARAM('_optimizer_coalesce_subqueries' 'true')
OPT_PARAM('_optimizer_fast_pred_transitivity' 'true')
OPT_PARAM('_optimizer_fast_access_pred_analysis' 'true')
OPT_PARAM('_optimizer_unnest_disjunctive_subq' 'true')
OPT_PARAM('_optimizer_unnest_corr_set_subq' 'true')
OPT_PARAM('_optimizer_distinct_agg_transform' 'true')
OPT_PARAM('_aggregation_optimization_settings' 0)
OPT_PARAM('_optimizer_connect_by_elim_dups' 'true')
OPT_PARAM('_optimizer_eliminate_filtering_join' 'true')
OPT_PARAM('_connect_by_use_union_all' 'true')
OPT_PARAM('_optimizer_join_factorization' 'true')
OPT_PARAM('_optimizer_use_cbqt_star_transformation' 'true')
OPT_PARAM('_optimizer_table_expansion' 'true')
OPT_PARAM('_and_pruning_enabled' 'true')
OPT_PARAM('_optimizer_distinct_placement' 'true')
OPT_PARAM('_optimizer_use_feedback' 'true')
OPT_PARAM('_optimizer_try_st_before_jppd' 'true')
OPT_PARAM('_optimizer_interleave_jppd' 'true')
OPT_PARAM('_px_partition_scan_enabled' 'true')
OPT_PARAM('_optimizer_false_filter_pred_pullup' 'true')
OPT_PARAM('_optimizer_enable_table_lookup_by_nl' 'true')
OPT_PARAM('_optimizer_full_outer_join_to_outer' 'true')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=10)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
--如果 /*+ opt_param('optimizer_features_enable' '11.2.0.1') */ ,明显少许多.
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.4')
OPT_PARAM('_optimizer_undo_cost_change' '11.2.0.4')
OPT_PARAM('_optimizer_extended_stats_usage_control' 192)
OPT_PARAM('_px_partition_scan_enabled' 'true')
OPT_PARAM('_optimizer_false_filter_pred_pullup' 'true')
OPT_PARAM('_optimizer_enable_table_lookup_by_nl' 'true')
OPT_PARAM('_optimizer_full_outer_join_to_outer' 'true')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))
END_OUTLINE_DATA
*/
