Oracle SQL提示含义与示例 --- 模型化语句查询和分区提示

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2013-01-08 05:18:21

分享到 

《Oracle 高性能SQL引擎剖析：Oracle SQL 优化与调优技术详解》一书的附录部分。作为对该书的补充，帮助读者理解和掌握“提示”这一项在SQL优化中使用的这一重要辅助手段。 模型化语句提示 MODEL_MIN_ANALYSIS 语法：MODEL_MIN_ANALYSIS 描述：用于模型化语句查询转换的提示。使得优化器对模型化语句的主查询做最少的查询转换分析。 分别对以下两条语句（有、无该提示）进行优化器过程跟踪： HELLODBA.COM>alter session set events 'TRACE[RDBMS.SQL_Compiler.*]';   Session altered.   HELLODBA.COM>explain plan for   2  SELECT /*+qb_name(m) no_merge(@inv) NO_MERGE(@"SEL$2")*/status, s   3    FROM (select /*+qb_name(inv) no_merge(v)*/o.owner, o.status, o.object_name, o.created, t.tablespace_name from v_objects_sys o, t_tables t where o.owner=t.owner and o.object_name=t.table_name) q   4   WHERE q.created < :A   5    MODEL RETURN UPDATED ROWS   6      PARTITION BY (status)   7      DIMENSION BY (owner)   8      MEASURES (object_name v, 1 s)   9      RULES  10      (s[any] = count(v) over (partition by status));   ... ...   HELLODBA.COM>explain plan for   2  SELECT /*+qb_name(m) MODEL_MIN_ANALYSIS no_merge(@inv) NO_MERGE(@"SEL$2")*/status, s   3    FROM (select /*+qb_name(inv) no_merge(v)*/o.owner, o.status, o.object_name, o.created, t.tablespace_name from v_objects_sys o, t_tables t where o.owner=t.owner and o.object_name=t.table_name) q   4   WHERE q.created < :A   5    MODEL RETURN UPDATED ROWS   6      PARTITION BY (status)   7      DIMENSION BY (owner)   8      MEASURES (object_name v, 1 s)   9      RULES  10      (s[any] = count(v) over (partition by status));   通过比较可以发现当使用该提示时，优化器为对模型化主语句做简单谓词推入的查询转换分析： FPD: Considering simple filter push (pre rewrite) in query block M (#0) FPD:  Current where clause predicates  ??   try to generate transitive predicate from check constraints for query block M (#0) finally:  ??   kkqfppRelFilter: Not pushing filter predicates in query block SEL$21876068 (#0) because no predicate to push FPD: Considering simple filter push (pre rewrite) in query block SEL$21876068 (#0) FPD:  Current where clause predicates "T_OBJECTS"."OWNER"="T"."OWNER" AND "T_OBJECTS"."OBJECT_NAME"="T"."TABLE_NAME" AND "T_OBJECTS"."OWNER"='SYS' AND "T_OBJECTS"."CREATED"<:B1 AND "T"."OWNER"='SYS' MODEL_NO_ANALYSIS 语法：MODEL_NO_ANALYSIS 描述：用于模型化语句查询转换的提示。使得优化器不对模型化语句的主查询做查询转换分析。 可以对比有无该提示的优化器跟踪记录观察其影响。 MODEL_PUSH_REF 语法：MODEL_PUSH_REF               描述：未知。可能是用于模型化语句查询转换的提示，指示优化器将主模型中的谓词条件推入引用模型当中。 NO_MODEL_PUSH_REF 语法：NO_MODEL_PUSH_REF 描述：未知。可能是用于模型化语句查询转换的提示，禁止优化器将主模型中的谓词条件推入引用模型当中。 MODEL_COMPILE_SUBQUERY 语法：MODEL_COMPILE_SUBQUERY 描述：未知。可能是用于模型化语句查询转换的提示。 MODEL_DONTVERIFY_UNIQUENESS 语法：MODEL_DONTVERIFY_UNIQUENESS  描述：未知。可能是用于模型化语句查询转换的提示。 MODEL_DYNAMIC_SUBQUERY 语法：MODEL_DYNAMIC_SUBQUERY 描述：未知。可能是用于模型化语句查询转换的提示。 分区提示 X_DYN_PRUNE 语法：X_DYN_PRUNE 描述：指示优化器通过运行时刻子查询的结果对分区进行动态裁剪。 HELLODBA.COM>alter session set tracefile_identifier = 'hash_X_DYN_PRUNE(10128)';   Session altered.   HELLODBA.COM>alter session set events '10128 trace name context forever, level 31';   Session altered.   HELLODBA.COM>select /*+use_hash(tr t2) X_DYN_PRUNE*/tr.* from t_objects_range tr, t_tables t2 where tr.owner=t2.owner and tr.object_name=t2.table_name and t2.tablespace_name='USERS'; … …   HELLODBA.COM>exec sql_explain('select /*+use_hash(tr t2) X_DYN_PRUNE*/tr.* from t_objects_range tr, t_tables t2 where tr.owner=t2.owner and tr.object_name=t2.table_name and t2.tablespace_name=''USERS''', 'BASIC OUTLINE');   --------------------------------------------------------- | Id  | Operation                     | Name            | --------------------------------------------------------- |   0 | SELECT STATEMENT              |                 | |   1 |  HASH JOIN                    |                 | |   2 |   PART JOIN FILTER CREATE     | :BF0000         | |   3 |    TABLE ACCESS BY INDEX ROWID| T_TABLES        | |   4 |     INDEX RANGE SCAN          | T_TABLES_IDX3   | |   5 |   PARTITION RANGE JOIN-FILTER |                 | |   6 |    TABLE ACCESS FULL          | T_OBJECTS_RANGE | --------------------------------------------------------- 上述语句的10128跟踪信息中，可以看到以下记录： kkpapDAExtSQuerySLvl strings sql1 SELECT distinct TBL$OR$IDX$PART$NUM("T_OBJECTS_RANGE", 0,  sql2 "OWNER", "OBJECT_NAME") FROM (SELECT "A1"."OWNER" "OWNER", "A1"."TABLE_NAME" "OBJECT_NAME" FROM "T_TABLES" "A1" WHERE "A1"."TABLESPACE_NAME"='USERS') ORDER BY 1 SUBQUERY_PRUNING 语法：SUBQUERY_PRUNING([<@查询块>] <表> PARTITION) 描述：指示优化器使用子查询对分区表进行分区裁剪。SUBQUERY_PRUNING/NO_SUBQUERY_PRUNING是用于控制执行计划中否采用子查询裁剪的概要数据，而X_DYN_PRUNE则用于执行时是否进行子查询裁剪。 HELLODBA.COM>exec sql_explain('select /*+ use_merge(tr t2) SUBQUERY_PRUNING(tr PARTITION)*/tr.* from t_objects_range tr, t_tables t2 where tr.owner=t2.owner and tr.object_name=t2.table_name and t2.tablespace_name=''SYSTEM''', 'BASIC');   ------------------------------------------------------- | Id  | Operation                  | Name             | ------------------------------------------------------- |   0 | SELECT STATEMENT           |                  | |   1 |  MERGE JOIN                |                  | |   2 |   SORT JOIN                |                  | |   3 |    VIEW                    | index$_join$_002 | |   4 |     HASH JOIN              |                  | |   5 |      INDEX RANGE SCAN      | T_TABLES_IDX3    | |   6 |      INDEX FAST FULL SCAN  | T_TABLES_PK      | |   7 |   SORT JOIN                |                  | |   8 |    PARTITION RANGE SUBQUERY|                  | |   9 |     TABLE ACCESS FULL      | T_OBJECTS_RANGE  | -------------------------------------------------------   从10128事件跟踪信息中可以看到以下记录： SQL text = text = SELECT distinct TBL$OR$IDX$PART$NUM("T_OBJECTS_RANGE", 0,  "OWNER", "OBJECT_NAME") FROM (SELECT "T2"."OWNER" "OWNER", "T2"."TABLE_NAME" "OBJECT_NAME" FROM "T_TABLES" "T2" WHERE "T2"."TABLESPACE_NAME"='SYSTEM') ORDER BY 1} NO_SUBQUERY_PRUNING 语法：NO_SUBQUERY_PRUNING([<@查询块>] <表>) 描述：禁止优化器使用子查询对分区表进行分区裁剪 HELLODBA.COM>exec sql_explain('select /*+NO_SUBQUERY_PRUNING(O PARTITION) LEADING(u)*/o.* from t_objects_range o, t_tables t, t_users u where o.owner=t.owner and o.object_name=t.table_name and o.owner=u.username and t.tablespace_name=''USERS'' and u.user_id<10', 'BASIC OUTLINE');   ---------------------------------------------------------- | Id  | Operation                      | Name            | ---------------------------------------------------------- |   0 | SELECT STATEMENT               |                 | |   1 |  NESTED LOOPS                  |                 | |   2 |   NESTED LOOPS                 |                 | |   3 |    HASH JOIN                   |                 | |   4 |     TABLE ACCESS BY INDEX ROWID| T_USERS         | |   5 |      INDEX RANGE SCAN          | T_USERS_PK      | |   6 |     PARTITION RANGE ALL        |                 | |   7 |      TABLE ACCESS FULL         | T_OBJECTS_RANGE | |   8 |    INDEX UNIQUE SCAN           | T_TABLES_PK     | |   9 |   TABLE ACCESS BY INDEX ROWID  | T_TABLES        | ----------------------------------------------------------