ITPub博客

首页 > 数据库 > Oracle > 1014OPT_PARAM OPTIMIZER_FEATURES_ENABLE

1014OPT_PARAM OPTIMIZER_FEATURES_ENABLE

原创 Oracle 作者:lfree 时间:2014-10-14 17:57:24 0 删除 编辑

[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<=1;

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了.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1298706/,如需转载,请注明出处,否则将追究法律责任。

全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2698
  • 访问量
    6480310