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';