分享程序员开发的那些事...
更新时间：2022-09-11 15:14:01
[20141014]OPT_PARAM与OPTIMIZER_FEATURES_ENABLE.txt
http://www.itpub.net/thread-1495845-1-1.html
http://space.itpub.net/267265/viewspace-723066
http://blog.itpub.net/267265/viewspace-752117/
http://blog.itpub.net/267265/viewspace-1298186/
--上午写了一篇blog,关于11G 长时间分析的问题.在测试使用提示OPT_PARAM与OPTIMIZER_FEATURES_ENABLE参数,遇到一些诡异的问题,
--做一个记录.
$ cat a.sql
SELECT f.constraint_name, f.owner, f.r_owner, p.table_name, SYS.all_cons_columns.column_name, f.delete_rule
FROM SYS.all_constraints f, SYS.all_cons_columns, SYS.all_constraints p
WHERE f.owner = 'SCOTT'
AND f.table_name = 'EMP'
AND f.constraint_type = 'R'
AND SYS.all_cons_columns.constraint_name = f.constraint_name
AND SYS.all_cons_columns.table_name = 'EMP'
AND SYS.all_cons_columns.owner = 'SCOTT'
AND p.owner = f.r_owner
AND p.constraint_name = f.r_constraint_name
ORDER BY f.constraint_name, SYS.all_cons_columns.POSITION ;
$ cat ver1.sql
column port_string format a30
column version format a14
select dbms_utility.port_string port_string, version,v$version.* from v$instance,v$version where rownum
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
--上午的分析在第1次执行是需要大约4秒.
SCOTT@test> set timing on
SCOTT@test> alter system flush SHARED_POOL;
System altered.
Elapsed: 00:00:00.24
SCOTT@test> @a
CONSTRAINT_NAME OWNER R_OWNER TABLE_NAME COLUMN_NAME DELETE_RU
------------------------------ ------ -------- ---------- -------------------- ---------
FK_DEPTNO SCOTT SCOTT DEPT DEPTNO NO ACTION
Elapsed: 00:00:03.79
--如果在会话级设置参数OPTIMIZER_FEATURES_ENABLE='8.0.4'[注:上午的测试使用'9.0.0'].
SCOTT@test> alter session set optimizer_features_enable='8.0.4';
Session altered.
Elapsed: 00:00:00.02
SCOTT@test> alter system flush SHARED_POOL;
System altered.
Elapsed: 00:00:00.13
SCOTT@test> @a
CONSTRAINT_NAME OWNER R_OWNER TABLE_NAME COLUMN_NAME DELETE_RU
------------------------------ ------ -------- ---------- -------------------- ---------
FK_DEPTNO SCOTT SCOTT DEPT DEPTNO NO ACTION
Elapsed: 00:00:00.28
--可以发现设置optimizer_features_enable='8.0.4',马上快许多.
--但是当我使用提示时情况如何呢?
$ cat a1.sql
SELECT /*+ OPTIMIZER_FEATURES_ENABLE('8.0.4') */ f.constraint_name, f.owner, f.r_owner, p.table_name, SYS.all_cons_columns.column_name, f.delete_rule
FROM SYS.all_constraints f, SYS.all_cons_columns, SYS.all_constraints p
WHERE f.owner = 'SCOTT'
AND f.table_name = 'EMP'
AND f.constraint_type = 'R'
AND SYS.all_cons_columns.constraint_name = f.constraint_name
AND SYS.all_cons_columns.table_name = 'EMP'
AND SYS.all_cons_columns.owner = 'SCOTT'
AND p.owner = f.r_owner
AND p.constraint_name = f.r_constraint_name
ORDER BY f.constraint_name, SYS.all_cons_columns.POSITION ;
--退出在执行:
SCOTT@test> alter system flush SHARED_POOL;
System altered.
SCOTT@test> set timing on
SCOTT@test> @a1
CONSTRAINT_NAME OWNER R_OWNER TABLE_NAME COLUMN_NAME DELETE_RU
------------------------------ ------ -------- ---------- -------------------- ---------
FK_DEPTNO SCOTT SCOTT DEPT DEPTNO NO ACTION
Elapsed: 00:00:00.25
--可以发现执行正常!而且很快.
$ cat a2.sql
SELECT /*+ opt_param('optimizer_features_enable' '8.0.4') */ f.constraint_name, f.owner, f.r_owner, p.table_name, SYS.all_cons_columns.column_name, f.delete_rule
FROM SYS.all_constraints f, SYS.all_cons_columns, SYS.all_constraints p
WHERE f.owner = 'SCOTT'
AND f.table_name = 'EMP'
AND f.constraint_type = 'R'
AND SYS.all_cons_columns.constraint_name = f.constraint_name
AND SYS.all_cons_columns.table_name = 'EMP'
AND SYS.all_cons_columns.owner = 'SCOTT'
AND p.owner = f.r_owner
AND p.constraint_name = f.r_constraint_name
ORDER BY f.constraint_name, SYS.all_cons_columns.POSITION ;
SCOTT@test> alter system flush SHARED_POOL;
System altered.
SCOTT@test> set timing on
SCOTT@test> @a2
CONSTRAINT_NAME OWNER R_OWNER TABLE_NAME COLUMN_NAME DELETE_RU
------------------------------ ------ -------- ---------- -------------------- ---------
FK_DEPTNO SCOTT SCOTT DEPT DEPTNO NO ACTION
Elapsed: 00:00:04.45
--执行很慢,可以发现提示没效果.我第一次就是这样使用的:-(.
SCOTT@test> @dpc '' outline
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('8.0.4')
DB_VERSION('11.2.0.3')
OPT_PARAM('_optimizer_max_permutations' 2000)
OPT_PARAM('_cpu_to_io' 0)
OPT_PARAM('_optimizer_undo_cost_change' '11.2.0.3')
OPT_PARAM('_always_anti_join' 'choose')
OPT_PARAM('_always_semi_join' 'choose')
OPT_PARAM('_optimizer_mode_force' 'true')
OPT_PARAM('_partition_view_enabled' 'true')
OPT_PARAM('_optimizer_cost_model' 'choose')
OPT_PARAM('_new_sort_cost_estimate' 'true')
OPT_PARAM('_unnest_subquery' 'true')
OPT_PARAM('_eliminate_common_subexpr' 'true')
OPT_PARAM('_pred_move_around' 'true')
OPT_PARAM('_optim_enhance_nnull_detection' 'true')
OPT_PARAM('_parallel_broadcast_enabled' 'true')
OPT_PARAM('query_rewrite_enabled' 'true')
OPT_PARAM('_left_nested_loops_random' 'true')
OPT_PARAM('_improved_row_length_enabled' 'true')
OPT_PARAM('_index_join_enabled' 'true')
OPT_PARAM('_enable_type_dep_selectivity' 'true')
OPT_PARAM('_improved_outerjoin_card' 'true')
OPT_PARAM('_use_column_stats_for_function' 'true')
OPT_PARAM('_subquery_pruning_enabled' 'true')
OPT_PARAM('_or_expand_nvl_predicate' 'true')
OPT_PARAM('_table_scan_cost_plus_one' 'true')
OPT_PARAM('_cost_equality_semi_join' 'true')
OPT_PARAM('_new_initial_join_orders' 'true')
OPT_PARAM('_optim_peek_user_binds' 'true')
OPT_PARAM('_minimal_stats_aggregation' 'true')
OPT_PARAM('_gs_anti_semi_join_allowed' 'true')
OPT_PARAM('_optim_new_default_join_sel' 'true')
OPT_PARAM('optimizer_dynamic_sampling' 2)
OPT_PARAM('_pre_rewrite_push_pred' 'true')
OPT_PARAM('_optimizer_new_join_card_computation' 'true')
OPT_PARAM('_union_rewrite_for_gs' 'yes_gset_mvs')
OPT_PARAM('_generalized_pruning_enabled' 'true')
OPT_PARAM('_optim_adjust_for_part_skews' 'true')
OPT_PARAM('_optimizer_system_stats_usage' 'true')
OPT_PARAM('_remove_aggr_subquery' 'true')
OPT_PARAM('_local_communication_costing_enabled' 'true')
OPT_PARAM('_optimizer_cost_based_transformation' 'linear')
OPT_PARAM('_right_outer_hash_enable' 'true')
OPT_PARAM('_optimizer_squ_bottomup' 'true')
OPT_PARAM('_optimizer_skip_scan_enabled' 'true')
OPT_PARAM('_optimizer_join_sel_sanity_check' 'true')
OPT_PARAM('_mmv_query_rewrite_enabled' 'true')
OPT_PARAM('_optimizer_dim_subq_join_sel' 'true')
OPT_PARAM('_optimizer_compute_index_stats' 'true')
OPT_PARAM('_push_join_union_view2' 'true')
OPT_PARAM('_query_rewrite_setopgrw_enable' 'true')
OPT_PARAM('_optimizer_correct_sq_selectivity' 'true')
OPT_PARAM('_optimizer_join_order_control' 3)
OPT_PARAM('_optimizer_push_pred_cost_based' 'true')
OPT_PARAM('_optimizer_null_aware_antijoin' 'true')
OPT_PARAM('_optimizer_extend_jppd_view_types' 'true')
OPT_PARAM('_sql_model_unfold_forloops' 'run_time')
OPT_PARAM('_bloom_filter_enabled' 'true')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'udo')
OPT_PARAM('_optimizer_cost_hjsmj_multimatch' 'true')
OPT_PARAM('_optimizer_transitivity_retain' 'true')
OPT_PARAM('_px_pwg_enabled' 'true')
OPT_PARAM('_optimizer_join_elimination_enabled' 'true')
OPT_PARAM('_optimizer_cbqt_no_size_restriction' 'true')
OPT_PARAM('_optimizer_enhanced_filter_push' 'true')
OPT_PARAM('_optimizer_filter_pred_pullup' 'true')
OPT_PARAM('_optimizer_rownum_pred_based_fkr' 'true')
OPT_PARAM('_optimizer_better_inlist_costing' 'all')
OPT_PARAM('_optimizer_or_expansion' 'depth')
OPT_PARAM('_optimizer_order_by_elimination_enabled' 'true')
OPT_PARAM('_optimizer_outer_to_anti_enabled' 'true')
OPT_PARAM('_selfjoin_mv_duplicates' 'true')
OPT_PARAM('_dimension_skip_null' 'true')
OPT_PARAM('_optimizer_star_tran_in_with_clause' 'true')
OPT_PARAM('_optimizer_complex_pred_selectivity' 'true')
OPT_PARAM('_optimizer_connect_by_cost_based' 'true')
OPT_PARAM('_gby_hash_aggregation_enabled' 'true')
OPT_PARAM('_globalindex_pnum_filter_enabled' 'true')
OPT_PARAM('_px_minus_intersect' 'true')
OPT_PARAM('_replace_virtual_columns' 'true')
OPT_PARAM('_first_k_rows_dynamic_proration' 'true')
OPT_PARAM('_optimizer_sortmerge_join_inequality' 'true')
OPT_PARAM('_bloom_pruning_enabled' 'true')
OPT_PARAM('_px_ual_serial_input' 'true')
OPT_PARAM('_optimizer_distinct_elimination' 'true')
OPT_PARAM('_optimizer_multi_level_push_pred' 'true')
OPT_PARAM('_optimizer_group_by_placement' 'true')
OPT_PARAM('_optimizer_rownum_bind_default' 10)
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_connect_by_combine_sw' 'true')
OPT_PARAM('_optimizer_native_full_outer_join' 'force')
OPT_PARAM('_optimizer_enable_extended_stats' 'true')
OPT_PARAM('_optimizer_fkr_index_cost_bias' 10)
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')
.... */
--如果对比a1.sql的执行计划.
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('8.0.4')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$9F331807")
UNNEST(@"SEL$30")
OUTLINE_LEAF(@"SEL$DD46E77B")
MERGE(@"SEL$9CF1E98E")
OUTLINE_LEAF(@"SEL$28")
OUTLINE_LEAF(@"SEL$28294604")
UNNEST(@"SEL$18")
OUTLINE_LEAF(@"SEL$A422EF13")
MERGE(@"SEL$5EC70623")
OUTLINE_LEAF(@"SEL$16")
OUTLINE_LEAF(@"SEL$F6521A81")
UNNEST(@"SEL$10")
OUTLINE_LEAF(@"SEL$5ED1C707")
MERGE(@"SEL$61262C81")
OUTLINE_LEAF(@"SEL$8")
OUTLINE_LEAF(@"SEL$6")
OUTLINE_LEAF(@"SEL$693A5C0E")
OLD_PUSH_PRED(@"SEL$1AB1DE6C" "RO"@"SEL$2" ("OBJ$"."OBJ#"))
OUTLINE_LEAF(@"SEL$26")
OUTLINE_LEAF(@"SEL$FB557CB0")
OLD_PUSH_PRED(@"SEL$1AB1DE6C" "RO"@"SEL$22" ("OBJ$"."OBJ#"))
... */
--可以看出/*+ opt_param('optimizer_features_enable' '8.0.4') */视乎把许多内部参数进行了设置,但是下面的执行计划并没有使用.
$ cat a3.sql
SELECT /*+
OLD_PUSH_PRED(@"SEL$1AB1DE6C" "RO"@"SEL$2" ("OBJ$"."OBJ#"))
OLD_PUSH_PRED(@"SEL$1AB1DE6C" "RO"@"SEL$22" ("OBJ$"."OBJ#"))
*/ f.constraint_name, f.owner, f.r_owner, p.table_name, SYS.all_cons_columns.column_name, f.delete_rule
FROM SYS.all_constraints f, SYS.all_cons_columns, SYS.all_constraints p
WHERE f.owner = 'SCOTT'
AND f.table_name = 'EMP'
AND f.constraint_type = 'R'
AND SYS.all_cons_columns.constraint_name = f.constraint_name
AND SYS.all_cons_columns.table_name = 'EMP'
AND SYS.all_cons_columns.owner = 'SCOTT'
AND p.owner = f.r_owner
AND p.constraint_name = f.r_constraint_name
ORDER BY f.constraint_name, SYS.all_cons_columns.POSITION ;
SCOTT@test> alter system flush SHARED_POOL;
System altered.
Elapsed: 00:00:00.16
SCOTT@test> @a3
CONSTRAINT_NAME OWNER R_OWNER TABLE_NAME COLUMN_NAME DELETE_RU
------------------------------ ------ -------- ---------- -------------------- ---------
FK_DEPTNO SCOTT SCOTT DEPT DEPTNO NO ACTION
Elapsed: 00:00:01.80
--Elapsed: 00:00:01.80 ,视乎快了一点.但是还不够快!
$ cat a4.sql
SELECT /*+
opt_param('optimizer_features_enable' '8.0.4')
OLD_PUSH_PRED(@"SEL$1AB1DE6C" "RO"@"SEL$2" ("OBJ$"."OBJ#"))
OLD_PUSH_PRED(@"SEL$1AB1DE6C" "RO"@"SEL$22" ("OBJ$"."OBJ#"))
*/ f.constraint_name, f.owner, f.r_owner, p.table_name, SYS.all_cons_columns.column_name, f.delete_rule
FROM SYS.all_constraints f, SYS.all_cons_columns, SYS.all_constraints p
WHERE f.owner = 'SCOTT'
AND f.table_name = 'EMP'
AND f.constraint_type = 'R'
AND SYS.all_cons_columns.constraint_name = f.constraint_name
AND SYS.all_cons_columns.table_name = 'EMP'
AND SYS.all_cons_columns.owner = 'SCOTT'
AND p.owner = f.r_owner
AND p.constraint_name = f.r_constraint_name
ORDER BY f.constraint_name, SYS.all_cons_columns.POSITION ;
--多加入opt_param('optimizer_features_enable' '8.0.4')看看.
SCOTT@test> alter system flush SHARED_POOL;
System altered.
Elapsed: 00:00:00.15
SCOTT@test> @a4
CONSTRAINT_NAME OWNER R_OWNER TABLE_NAME COLUMN_NAME DELETE_RU
------------------------------ ------ -------- ---------- -------------------- ---------
FK_DEPTNO SCOTT SCOTT DEPT DEPTNO NO ACTION
Elapsed: 00:00:04.47
--打回原形,看来不能使用opt_param('optimizer_features_enable' '8.0.4')参数,这样无效.
--而是直接使用提示OPTIMIZER_FEATURES_ENABLE('8.0.4')就ok了.