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([<@查询块>] <> [(<索引字段列表>)])