1014OPT_PARAM OPTIMIZER_FEATURES_ENABLE

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

时间： 2024-10-16 00:38:52

1014OPT_PARAM OPTIMIZER_FEATURES_ENABLE的相关文章

[20131111]参数optimizer_features_enable.txt

[20131111]参数optimizer_features_enable.txt optimizer_features_enable可以使系统升级后保持原来的执行计划.我自己很少做这种升级操作,昨天在给别人做优化时遇到一个问题, 自己做一个记录,用户改动了参数optimizer_features_enable,不知道什么原因要改这个参数,导致一些执行计划很慢! 举一个例子: 1.建立测试环境: SCOTT@test> @ver BANNER --------------------------

Oracle Optimizer:迁移到使用基于成本的优化器-----系列2.1

oracle|优化 Oracle Optimizer:迁移到使用基于成本的优化器-----系列2.1   系列之二包含影响优化器选择执行计划的初始化参数和Oracle内部隐藏参数,合理设置这些参数对于优化器是相当重要的.        6.影响优化器的初始化参数        除了生成统计资料之外,下面提及的参数设置在你的系统正常工作中扮演着极重要的角色.这些设置将大多依赖于你想创建何种类型的环境.联机,批处理,数据仓库或多于一个的组合.请注意优化器考虑这些参数以评估每一个在CBO生成的执行计划

SQL PROFILE的用法详解

一般只需要步骤三.四就可以完成执行计划的修改和固定,而outline和baseline则需要N多个步骤. SQL PROFILE使用简单,不区分大小写,回车,空格,但是对DBA写HINT的能力要求比较高,因为SQL PROFILE要求HINT必须写明查询块名, SQL PROFILE还有其他一些牛逼的特性. 我的示例里教了大家偷懒的做法,但是有时间我们还是最好认真把query block的东西学下. 步骤一-------------------------创建测试表,根据DBA_OBJECTS创

Oracle的SQL性能调整需要注意的问题

1.不要认为将optimizer_mode参数设为rule,就认为所有的语句都使用基于规则的优化器.不管optimizer_mode参数如何设置,只要满足下面3个条件,就一定使用CBO. 1) 如果使用Index Only Tables(IOTs), 自动使用CBO. 2) Oracle 7.3以后,如果表上的Paralle degree option设为>1,则自动使用CBO, 而不管是否用rule hints. 3) 除rlue以外的任何hints都将导致自动使用CBO来执行语句 总结一下,

Oracle优化器CBO的知识点

ORACLE 提供了基于成本(CostBased)和基于规则(RuleBased)两种优化器,简称为CBO和RBO,用于确定查询操作的执行计划. 一.如何使用CostBased优化器优化查询操作? 如何使用CBO,那么首先要理解这些概念 1.CBO的成本计算的依据 (1)统计信息:与SQL语句所引用的对象相关以及主机的CPU和IO (2)SQL语句本身 (3).环境:例如与优化器相关的参数设置 2.优化器目标:optimizer_mode (1)ALL_ROWS (2)FIRST_ROWS_N

The Query Optimizer

The Query Optimizer This chapter discusses SQL processing, optimization methods, and how the query optimizer (usually called the optimizer) chooses a specific plan to execute SQL. The chapter contains the following sections: Overview of the Query Opt

oracle 如何稳定执行计划

2.5.1  automatic sql profile 调整执行计划   适合sql无法改写或验证改写是否成功的情况   验证:  Oracel 账号SYS Conn /as sysdba;   1>   create table t1(n number);     2>   declare      begin      for i in 1 ..10000      loop      insert into t1 values(i);      commit;      end loo

oralce 12.1中出现大量Result Cache: RC Latch处理

昨天有个朋友找到我说他们的12.1的库在业务高峰期非常慢,希望我们给予优化支持,经过awr分析,定位到问题为latch free问题,具体定位为:Result Cache: RC Latch.优化之前awr部分信息 awr整体负载情况,证明当前这个库已经比较忙,业务反馈很慢 addr信息和top wait信息,确定是latch free问题比较突出 latch信息统计和ash信息,找出来突出的latch,定位为Result Cache: RC Latch引起该问题 补充大量异常sql 类似sql

浅析_optimizer_null_aware_antijoin引发的SQL性能问题

前几天某客户联系我说之前我们进行存储迁移的系统,有个SQL跑的极慢,根本跑不出来结果.通过VPN登录看了下,SQL确认跑的很慢.开始我很难理解,我们仅仅是进行了存储迁移,数据库基本上没动,为什么会有SQL性能问题呢?  我们先来看看有问题的SQL: SYS@rptdb1> set autot traceonly exp SYS@rptdb1> select a.*,b.rate TAX_RATE,round(a.charge*b.rate/(1+b.rate),0) tax,a.charge-