HelloDBA [English]
搜索Internet 搜索 HelloDBABA
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

Oracle SQL提示含义与示例 --- 其它提示

[English]

作者: fuyuncat

来源: www.HelloDBA.com

日期: 2013-01-08 05:23:46

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

《Oracle 高性能SQL引擎剖析:Oracle SQL 优化与调优技术详解》一书的附录部分。作为对该书的补充,帮助读者理解和掌握“提示”这一项在SQL优化中使用的这一重要辅助手段。

其它类型提示

RELATIONAL

语法:RELATIONAL([<@查询块>] <>)

描述:将对象转换为关系型表进行查询,等同于在对象上增加了RELATIONAL函数。这一提示会导致查询结果的变化。

HELLODBA.COM>desc xmltable

 Name                                       Null?    Type

 ----------------------------------------- --------- ---------

 TABLE of PUBLIC.XMLTYPE

 

HELLODBA.COM>select * from xmltable;

 

SYS_NC_ROWINFO$

-------------------------------------------------------------------------------------------------------

<other_xml>

  <outline_data>

    <hint>

      <IGNORE_OPTIM_EMBEDDED_HINTS/>

    </hint>

    ... ...

  </outline_data>

</other_xml>

 

1 row selected.

 

HELLODBA.COM>select /*+ relational(x) */* from xmltable x;

 

SYS_NC_OID$                      XMLDATA

-------------------------------- ----------------------------------------------------------------------

5477ABC43C2D4A85917F7328AA961884 <other_xml><outline_data><hint><IGNORE_OPTIM_EMBEDDED_HINTS></IGNORE_OPTIM_EMBED

DED_HINTS></hint><hint><OPTIMIZER_FEATURES_ENABLE>10.2.0.3</OPTIMIZER_FEATURES_E

NABLE></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><OUTLINE_LEAF>"SEL$3BA1AD7C"

… …

 

直接使用该提示在DML语句上会导致抛出ORA-22837错误。

MONITOR

语法:MONITOR                     

描述:强制语句的运行状况被监控,不管它是否满足自动监控的前提条件(并行查询或者运行时间超过5秒);

HELLODBA.COM>show parameter CONTROL_MANAGEMENT_PACK_ACCESS

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_management_pack_access       string      DIAGNOSTIC+TUNING

HELLODBA.COM>select /*+ monitor */count(*) from t_users u;

 

  COUNT(*)

----------

        31

 

HELLODBA.COM>select sql_text, status from v$sql_monitor where sql_text like '%monitor%';

 

SQL_TEXT                                                     STATUS

------------------------------------------------------------ -------------------

select /*+ monitor */count(*) from t_users u                 DONE (ALL ROWS)

NO_MONITOR

语法:NO_MONITOR                  

描述:强制语句的运行状况不被监控,不管它是否满足自动监控的前提条件(并行查询或者运行时间超过5秒);

HELLODBA.COM>select /*+ no_monitor parallel(o 2) full(o) */ /*identifier*/ count(*) from t_objects o;

 

  COUNT(*)

----------

     72116

 

HELLODBA.COM>select sql_text, status from v$sql_monitor where sql_text like '%identifier%';

 

no rows selected

NESTED_TABLE_FAST_INSERT

语法:NESTED_TABLE_FAST_INSERT

描述:使用快速方式向嵌套表插入数据。通过10046跟踪可以看到,未使用提示时,是逐条记录插入;使用提示后,则是批量插入。

HELLODBA.COM>CREATE OR REPLACE TYPE simple_type AS TABLE OF VARCHAR2(30);

  2  /

 

Type created.

 

HELLODBA.COM>CREATE TABLE t_nt_table (a NUMBER, b simple_type) NESTED TABLE b STORE AS t_nt_b;

 

Table created.

 

HELLODBA.COM>INSERT /*+ */ INTO t_nt_table select object_id, simple_type(object_name) from t_objects;

 

72116 rows created.

 

Elapsed: 00:00:18.77

 

HELLODBA.COM>INSERT /*+ NESTED_TABLE_FAST_INSERT */ INTO t_nt_table select object_id, simple_type(object_name) from t_objects;

 

72116 rows created.

 

Elapsed: 00:00:07.79

NESTED_TABLE_GET_REFS

语法:NESTED_TABLE_GET_REFS

描述:该提示可以使用户直接访问嵌套对象。

HELLODBA.COM>select /*+ */count(*) from T_NT_B;

select /*+ */count(*) from T_NT_B

                           *

ERROR at line 1:

ORA-22812: cannot reference nested table column's storage table

 

HELLODBA.COM>select /*+ nested_table_get_refs */count(*) from T_NT_B;

 

  COUNT(*)

----------

     72116

NESTED_TABLE_SET_SETID

语法:NESTED_TABLE_SET_SETID

描述:该提示可以使用户直接访问嵌套对象。NESTED_TABLE_GET_REFSNESTED_TABLE_SET_SETID应该是通过两种不同的技术使得嵌套对象可以被直接访问。

HELLODBA.COM>select /*+ NESTED_TABLE_SET_SETID */count(*) from T_NT_B;

 

  COUNT(*)

----------

     72116

NO_MONITORING

语法:NO_MONITORING               

描述:禁止监控语句中的谓词字段使用情况,即数据字典col_usage$不会因该语句而被更新。

HELLODBA.COM>select like_preds from sys.SQLT$_DBA_COL_USAGE_V where owner ='DEMO' and table_name = 'T_TABLES' and column_name = 'TABLE_NAME';

 

LIKE_PREDS

----------

        18

 

HELLODBA.COM>select /*run(7)*/count(*) from t_tables where 7=7 and table_name like 'T%';

 

  COUNT(*)

----------

        30

 

HELLODBA.COM>exec dbms_stats.gather_table_stats('DEMO', 'T_TABLES');

 

PL/SQL procedure successfully completed.

 

HELLODBA.COM>select like_preds from sys.SQLT$_DBA_COL_USAGE_V where owner ='DEMO' and table_name = 'T_TABLES' and column_name = 'TABLE_NAME';

 

LIKE_PREDS

----------

        19

 

HELLODBA.COM>select /*+ no_monitoring *//*run(8)*/count(*) from t_tables where 8=8 and table_name like 'T%';

 

  COUNT(*)

----------

        30

 

HELLODBA.COM>exec dbms_stats.gather_table_stats('DEMO', 'T_TABLES');

 

PL/SQL procedure successfully completed.

 

HELLODBA.COM>select like_preds from sys.SQLT$_DBA_COL_USAGE_V where owner ='DEMO' and table_name = 'T_TABLES' and column_name = 'TABLE_NAME';

 

LIKE_PREDS

----------

        19

NO_SQL_TUNE

语法:NO_SQL_TUNE                 

描述:禁止自动调优组件对语句进行调优;

HELLODBA.COM>select /* No_TUNE(2) *//*+NO_SQL_TUNE*/count(*) from t_users;

 

  COUNT(*)

----------

        31

 

… …

HELLODBA.COM>exec :exec_name := dbms_sqltune.execute_tuning_task (:task_name, 'EXEC_'||substr(:task_name, length(:task_name)-4));

 

PL/SQL procedure successfully completed.

 

HELLODBA.COM>select dbms_sqltune.report_tuning_task (:task_name) from dual;

... ...

ADDITIONAL INFORMATION SECTION

--------------------------------------------------------------------------

- 不支持的 SQL 语句类型。

 

RESTRICT_ALL_REF_CONS

语法:RESTRICT_ALL_REF_CONS       

描述:在事务中暂时限制所有外键约束的级联(CASCADE)递归方法;

HELLODBA.COM>select owner, table_name, constraint_name, r_owner, r_constraint_name, delete_rule from dba_constraints where constraint_name = 'T_C_FK';

 

OWNER          TABLE_NAME        CONSTRAINT_NAME      R_OWNER        R_CONSTRAINT_NAME    DELETE_RULE

-------------- ----------------- -------------------- -------------- -------------------- ---------

DEMO           T_C               T_C_FK               DEMO           T_P_PK               CASCADE

 

HELLODBA.COM>delete /*+RESTRICT_ALL_REF_CONS*/from t_p where a=3;

 

1 row deleted.

 

HELLODBA.COM>select count(a) from t_c where a=3;

 

  COUNT(A)

----------

         1

 

HELLODBA.COM>commit;

commit

*

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-02292: integrity constraint (DEMO.T_C_FK) violated - child record found

USE_HASH_AGGREGATION

语法:USE_HASH_AGGREGATION([<@查询块>])

描述:指示优化器使用哈希进行聚合计算。

HELLODBA.COM>alter session set "_gby_hash_aggregation_enabled"=false;

 

Session altered.

 

HELLODBA.COM>exec sql_explain('select /*+qb_name(M) USE_CONCAT(@M)*/owner, count(1) from t_objects o group by owner', 'T

YPICAL OUTLINE');

Plan hash value: 87103648

 

----------------------------------------------------------------------------------------

| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |                |    23 |   138 |   196   (8)| 00:00:02 |

|   1 |  HASH GROUP BY        |                |    23 |   138 |   196   (8)| 00:00:02 |

|   2 |   INDEX FAST FULL SCAN| T_OBJECTS_IDX8 | 72116 |   422K|   185   (2)| 00:00:02 |

----------------------------------------------------------------------------------------

NO_USE_HASH_AGGREGATION

语法:NO_USE_HASH_AGGREGATION([<@查询块>])

描述:禁止优化器使用哈希进行聚合计算。

HELLODBA.COM>exec sql_explain('select /*+qb_name(M) NO_USE_HASH_AGGREGATION(@M)*/owner, count(1) from t_objects o group by owner', 'TYPICAL OUTLINE');

Plan hash value: 49003928

 

----------------------------------------------------------------------------------------

| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |                |    23 |   138 |   196   (8)| 00:00:02 |

|   1 |  SORT GROUP BY        |                |    23 |   138 |   196   (8)| 00:00:02 |

|   2 |   INDEX FAST FULL SCAN| T_OBJECTS_IDX8 | 72116 |   422K|   185   (2)| 00:00:02 |

----------------------------------------------------------------------------------------

BYPASS_RECURSIVE_CHECK

语法:BYPASS_RECURSIVE_CHECK      

描述:未知。可能是使编译器不做递归检查,我们观察到做物化视图刷新时,会加在递归调用语句。

示例:

HELLODBA.COM>alter session set sql_trace=true;

 

Session altered.

 

HELLODBA.COM>exec dbms_mview.refresh(list => 'MV_TABLES');

 

PL/SQL procedure successfully completed.

 

跟踪文件中可以找到相应语句。

INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "DEMO"."MV_TABLES"("OWNER","TABLE_NAME","TABLESPACE_NAME","CREATED","LAST_DDL_TIME") SELECT "T"."OWNER","T"."TABLE_NAME","T"."TABLESPACE_NAME","O"."CREATED","O"."LAST_DDL_TIME" FROM "T_TABLES" "T","T_OBJECTS" "O" WHERE "T"."OWNER"="O"."OWNER" AND "T"."TABLE_NAME"="O"."OBJECT_NAME" AND "O"."OBJECT_TYPE"=:"SYS_B_0" AND "T"."TABLESPACE_NAME" IS NOT NULL

由于对象物化视图是非FOR UPDATE属性的视图,直接执行上述语句会抛ORA-01732错误。

BYPASS_UJVC

语法:BYPASS_UJVC                 

描述:未知,可能是使编译器对更新语句不做关联视图唯一性约束检查。我们观察到做物化视图刷新时,会加在递归调用语句。直接使用不起作用,仍然会抛ORA-01779错误                            

从上例中的跟踪文件可以找到以下语句:

update  /*+ BYPASS_UJVC */      ( select s.status status        from snap$ s, snap_reftime$ r        where s.sowner = r.sowner and s.vname = r.vname and        r.mowner = :1 and r.master = :2 and s.mlink IS NULL        and bitand(s.status,16) = 0 and r.instsite =0 and s.instsite =0) v   set status = status + 16;

DOMAIN_INDEX_FILTER

语法:DOMAIN_INDEX_FILTER([<@查询块>] <> [(<索引>)]) 或者 DOMAIN_INDEX_FILTER([<@查询块>] <> [(<索引字段列表>)])

描述:指示优化器将过滤谓词推入复合域索引(Composite Domain Index)当中

HELLODBA.COM>exec sql_explain('SELECT /*+ domain_index_filter(t t_tables_dix03) */ * FROM t_tables t WHERE CONTAINS(owner, ''aaa'',1)>0 AND status = ''VALID''', 'TYPICAL');

 

----------------------------------------------------------------------------------------------

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                |     1 |   241 |     4   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T_TABLES       |     1 |   241 |     4   (0)| 00:00:01 |

|*  2 |   DOMAIN INDEX              | T_TABLES_DIX03 |       |       |     4   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("CTXSYS"."CONTAINS"("OWNER",'aaa',1)>0)

       filter("STATUS"='VALID')

NO_DOMAIN_INDEX_FILTER

语法:NO_DOMAIN_INDEX_FILTER([<@查询块>] <> [(<索引>)]) 或者 NO_DOMAIN_INDEX_FILTER([<@查询块>] <> [(<索引字段列表>)])

描述:禁止优化器将过滤谓词推入复合域索引(Compisite Domain Index)当中

HELLODBA.COM>exec sql_explain('SELECT /*+ no_domain_index_filter(t t_tables_dix03) */ * FROM t_tables t WHERE CONTAINS(owner, ''aaa'',1)>0 AND status = ''VALID''', 'TYPICAL');

 

----------------------------------------------------------------------------------------------

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                |     1 |   241 |     4   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| T_TABLES       |     1 |   241 |     4   (0)| 00:00:01 |

|*  2 |   DOMAIN INDEX              | T_TABLES_DIX03 |       |       |     4   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("STATUS"='VALID')

   2 - access("CTXSYS"."CONTAINS"("OWNER",'aaa',1)>0)

 

DOMAIN_INDEX_SORT

语法:DOMAIN_INDEX_SORT           

描述:指示优化器将排序字段推入复合域索引(Compisite Domain Index)当中

HELLODBA.COM>exec sql_explain('SELECT /*+ domain_index_sort */ * FROM t_tables t WHERE CONTAINS(tablespace_name, ''aaa'',1)>0 ORDER BY table_name, score(1) desc', 'TYPICAL');

Plan hash value: 991332243

 

----------------------------------------------------------------------------------------------

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                |     1 |   241 |     5  (20)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T_TABLES       |     1 |   241 |     5  (20)| 00:00:01 |

|*  2 |   DOMAIN INDEX              | T_TABLES_DIX02 |       |       |     4   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("CTXSYS"."CONTAINS"("TABLESPACE_NAME",'aaa',1)>0)

 

NO_DOMAIN_INDEX_ SORT

语法:NO_DOMAIN_INDEX_ SORT        

描述:禁止优化器将排序字段推入复合域索引(Compisite Domain Index)当中

HELLODBA.COM>exec sql_explain('SELECT /*+ no_domain_index_sort */ * FROM t_tables t WHERE CONTAINS(tablespace_name, ''aaa'',1)>0 ORDER BY table_name, score(1) desc', 'TYPICAL');

 

-----------------------------------------------------------------------------------------------

| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                |     1 |   241 |     5  (20)| 00:00:01 |

|   1 |  SORT ORDER BY               |                |     1 |   241 |     5  (20)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| T_TABLES       |     1 |   241 |     4   (0)| 00:00:01 |

|*  3 |    DOMAIN INDEX              | T_TABLES_DIX02 |       |       |     4   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("CTXSYS"."CONTAINS"("TABLESPACE_NAME",'aaa',1)>0)

 

DST_UPGRADE_INSERT_CONV

语法:DST_UPGRADE_INSERT_CONV     

描述:在使用DBMS_DST包对数据库时区进行升级过程中,如果存在含有带时区的时间戳类型(TIMESTAMP WITH TIME ZONE)的字段的表没有被升级,该提示会指示优化器在修改该字段时,向其添加一个内部函数(ORA_DST_CONVERT(INTERNAL_FUNCTION())

NO_DST_UPGRADE_INSERT_CONV

语法:NO_DST_UPGRADE_INSERT_CONV  

描述:在使用DBMS_DST包对数据库时区进行升级过程中,如果存在含有带时区的时间戳类型(TIMESTAMP WITH TIME ZONE)的字段的表没有被升级,该提示会禁止优化器在修改该字段时,向其添加一个内部函数(ORA_DST_CONVERT(INTERNAL_FUNCTION())

STREAMS

语法:STREAMS

描述:未知。可能是指示数据是以“流”的方式传输。

需要Oracle“流”(STREAMS)组件。

DEREF_NO_REWRITE

语法:DEREF_NO_REWRITE(<@查询块>)

描述:未知,可能是用于禁止对“BUILD DEFERRED”的物化视图进行查询重写,无法确定。

MV_MERGE

语法:MV_MERGE

描述:未知。可能是用于优化CUBE查询。

EXPR_CORR_CHECK

语法:EXPR_CORR_CHECK

描述:未知。可能是指示优化器在解析使用表达式过滤器(Expression Filter)的语句,做相互关联检查。

需要表达式过滤器组件

INCLUDE_VERSION

语法:INCLUDE_VERSION             

描述:未知。该提示出现在高级复制的内部SQL语句当中。可能是用于保持不同版本数据库之间的复制的兼容性。

VECTOR_READ

语法:VECTOR_READ

描述:未知。可能是用于控制哈希关联的位矢量图过滤。

VECTOR_READ_TRACE

语法:VECTOR_READ_TRACE           

描述:未知。可能是用于控制哈希关联的位矢量图过滤。

USE_WEAK_NAME_RESL

语法:USE_WEAK_NAME_RESL

描述:未知。可能是指示优化器解析语句时使用内部名字而非用户定义的名字来查找资源位置(Resource Location)。通常出现在内部递归调用(如收集统计数据、表达式过滤器)的语句上;

select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */ count(*)  from "DEMO"."T_OBJECTS" sample block (  9.1911764706,1) t

NO_PARTIAL_COMMIT

语法:NO_PARTIAL_COMMIT           

描述:未知。可能是用于阻止内部递归事务的局部提交,使其与当前事务同时提交。从对存在嵌套对象的表(如基于XML-Schemal的表、嵌套表等)的数据维护操作的递归调用跟踪中可以看到,该提示加在维护其嵌套的表的语句上;

HELLODBA.COM>alter session set events 'sql_trace wait=true, bind=true, plan_stat=all_executions';

 

Session altered.

 

HELLODBA.COM>INSERT /*+ NESTED_TABLE_FAST_INSERT */ INTO t_nt_table select object_id, simple_type(object_name) from t_objects;

 

... ...

 

跟踪记录中可以找到以下语句

INSERT /*+ NO_PARTIAL_COMMIT REF_CASCADE_CURSOR */ INTO  "DEMO"."T_NT_B" ("NESTED_TABLE_ID","COLUMN_VALUE")  VALUES(:1, :2)

REF_CASCADE_CURSOR

语法:REF_CASCADE_CURSOR          

描述:未知。可能是用于阻止内部递归事务的局部提交,使其与当前事务同时提交。从对存在嵌套对象的表(如基于XML-Schemal的表、嵌套表等)的数据维护操作的递归调用跟踪中可以看到,该提示加在维护其嵌套的表的语句上;

NO_PARTIAL_COMMIT示例

NO_REF_CASCADE

语法:NO_REF_CASCADE

描述:未知。可能是用于禁止内部递归语句使用级联游标。

SQLLDR

语法:SQLLDR

描述:未知。可能是运行SQL*Loader时加上的提示。

SYS_RID_ORDER

语法:SYS_RID_ORDER               

描述:未知。可能是用于物化视图维护操作时的递归调用语句上的。

OVERFLOW_NOMOVE

语法:OVERFLOW_NOMOVE

描述:未知。估计是用于禁止分区表分裂时或者索引组织表的非索引键字段溢出致其它存储段时的数据迁移。

LOCAL_INDEXES

语法:LOCAL_INDEXES

描述:未知

MERGE_CONST_ON

语法:MERGE_CONST_ON              

描述:未知

QUEUE_CURR

语法:QUEUE_CURR                  

描述: 未知。可能是用于Oracle高级队列(Advanced Queue)的提示。

CACHE_CB

语法:CACHE_CB([<@查询块>] <>)

描述:未知。用于高级队列(Advanced Queue)的提示

从对DBMS_AQ.DEQUEUUEdelivery_modePERSISTENT)过程跟踪可以看到以下语句:

delete /*+ CACHE_CB("QUETABLET") */ from "DEMO"."QUETABLET" where rowid = :1;

QUEUE_ROWP

语法:QUEUE_ROWP                  

描述: 未知。可能是用于Oracle高级队列(Advanced Queue)的提示。

BUFFER

语法:BUFFER                      

描述:未知。可能用于高级队列(Advanced Queue)的提示

NO_BUFFER

语法:NO_BUFFER                   

描述:未知。可能用于高级队列用于高级队列(Advanced Queue)的提示                           

BITMAP

语法:BITMAP

描述:未知。

Top

Copyright ©2005,HelloDBA.Com 保留一切权利

申明
by fuyuncat