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

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

分享到

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