《Oracle 高性能SQL引擎剖析:Oracle SQL 优化与调优技术详解》一书的附录部分。作为对该书的补充,帮助读者理解和掌握“提示”这一项在SQL优化中使用的这一重要辅助手段。
语法:MODEL_MIN_ANALYSIS
描述:用于模型化语句查询转换的提示。使得优化器对模型化语句的主查询做最少的查询转换分析。
分别对以下两条语句(有、无该提示)进行优化器过程跟踪:
HELLODBA.COM>alter session set events 'TRACE[RDBMS.SQL_Compiler.*]';
Session altered.
HELLODBA.COM>explain plan for
2SELECT /*+qb_name(m) no_merge(@inv) NO_MERGE(@"SEL$2")*/status, s
3FROM (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
4WHERE q.created < :A
5MODEL RETURN UPDATED ROWS
6PARTITION BY (status)
7DIMENSION BY (owner)
8MEASURES (object_name v, 1 s)
9RULES
10(s[any] = count(v) over (partition by status));
... ...
HELLODBA.COM>explain plan for
2SELECT /*+qb_name(m) MODEL_MIN_ANALYSIS no_merge(@inv) NO_MERGE(@"SEL$2")*/status, s
3FROM (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
4WHERE q.created < :A
5MODEL RETURN UPDATED ROWS
6PARTITION BY (status)
7DIMENSION BY (owner)
8MEASURES (object_name v, 1 s)
9RULES
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>
语法:MODEL_NO_ANALYSIS
描述:用于模型化语句查询转换的提示。使得优化器不对模型化语句的主查询做查询转换分析。
可以对比有无该提示的优化器跟踪记录观察其影响。
语法:MODEL_PUSH_REF
描述:未知。可能是用于模型化语句查询转换的提示,指示优化器将主模型中的谓词条件推入引用模型当中。
语法:NO_MODEL_PUSH_REF
描述:未知。可能是用于模型化语句查询转换的提示,禁止优化器将主模型中的谓词条件推入引用模型当中。
语法:MODEL_COMPILE_SUBQUERY
描述:未知。可能是用于模型化语句查询转换的提示。
语法:MODEL_DONTVERIFY_UNIQUENESS
描述:未知。可能是用于模型化语句查询转换的提示。
语法:MODEL_DYNAMIC_SUBQUERY
描述:未知。可能是用于模型化语句查询转换的提示。
语法: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([] 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([] )
描述:禁止优化器使用子查询对分区表进行分区裁剪
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|
----------------------------------------------------------