Oracle SQL Hints --- Other Hints

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2013-01-08 05:23:46

Share to 

Other Hints RELATIONAL Usage: RELATIONAL([<@Block>] <Table>) Description: Instructs the optimizer to convert the Object to a relational table, similar to RELATIONAL function. 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" … …   MONITOR Usage: MONITOR                      Description: Instruct Oracle monitor the running status of the statement, regardless of whether it fulfills the criteria (Parallel Query or Running for more than 5 seconds) or not. 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 Usage: NO_MONITOR                   Description: Prevent Oracle monitor the running status of the statement, regardless of whether it fulfills the criteria (Parallel Query or Running for more than 5 seconds) or not. 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 Usage: NESTED_TABLE_FAST_INSERT Description: Instructs SQL executor to insert data into nested table in fast mode. From the trace content of 10046 event, the data was inserted in batch. 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 Usage: NESTED_TABLE_GET_REFS Description: With this hint, user can access to nested table directly. 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 Usage: NESTED_TABLE_SET_SETID Description: With this hint, user can access to nested table directly. HELLODBA.COM>select /*+ NESTED_TABLE_SET_SETID */count(*) from T_NT_B;     COUNT(*) ----------      72116 NO_MONITORING Usage: NO_MONITORING                Description: Prevent Oracle monitor the column usage in predication, consequently, the dictionary table col_usage$ will not be updated by the execution of the statement. 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 Usage: NO_SQL_TUNE                  Description: Prevent the optimizer to do SQL tuning on the statement. 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 Usage: RESTRICT_ALL_REF_CONS        Description: Restricts all cascaded operations caused by referencing constraints in the transactions. 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 Usage: USE_HASH_AGGREGATION([<@Block>]) Description: Instructs the optimizer using hash algorithm to perform aggregation operations. 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 Usage: NO_USE_HASH_AGGREGATION([<@Block>]) Description: Prevents the optimizer using hash algorithm to perform aggregation operations. 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 Usage: BYPASS_RECURSIVE_CHECK       Description: Unknown. It might instruct the parser not do recursive checking. It could be observed in the internal statement generated by Materialized View updating. Demo: HELLODBA.COM>alter session set sql_trace=true;   Session altered.   HELLODBA.COM>exec dbms_mview.refresh(list => 'MV_TABLES');   PL/SQL procedure successfully completed.   We got below statement from the tracing content. 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   BYPASS_UJVC Usage: BYPASS_UJVC                  Description: Unknown. It might instruct parser not check unique constraint for the join view. It could be observed in the internal statement generated by Materialized View updating.                             We got below statement from the trace file generated in previous demo. 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 Usage: DOMAIN_INDEX_FILTER([<@Block>] <Table> [( <Index>)]) or DOMAIN_INDEX_FILTER([<@Block>] <Table> [( <Indexed Columns>)]) Description: Instructs the optimizer the push filter to 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 Usage: NO_DOMAIN_INDEX_FILTER([<@Block>] <Table> [( <Index>)]) or NO_DOMAIN_INDEX_FILTER([<@Block>] <Table> [( <Indexed Columns>)]) Description: Prevents the optimizer the push filter to Composite 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 Usage: DOMAIN_INDEX_SORT            Description: Instructs the optimizer the push sorting columns to Composite 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 Usage: NO_DOMAIN_INDEX_ SORT         Description: Prevents the optimizer the push sorting columns to Composite 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 Usage: DST_UPGRADE_INSERT_CONV      Description: With this hint, Oracle will add an internal function (ORA_DST_CONVERT(INTERNAL_FUNCTION())) to modify the column defined as TIMESTAMP WITH TIME ZONE when using package DBMS_DST to upgrade the time zone of the database. NO_DST_UPGRADE_INSERT_CONV Usage: NO_DST_UPGRADE_INSERT_CONV   Description: With this hint, Oracle will not add an internal function (ORA_DST_CONVERT(INTERNAL_FUNCTION())) to modify the column defined as TIMESTAMP WITH TIME ZONE when using package DBMS_DST to upgrade the time zone of the database. STREAMS Usage: STREAMS Description: Unknown. It might instructs the SQL execution to transfer the data in stream. DEREF_NO_REWRITE Usage: DEREF_NO_REWRITE(<@Block>) Description: Unknown. It might prevent the optimizer to rewrite the Materialized View with  BUILD DEFERRED option. MV_MERGE Usage: MV_MERGE Description: Unknown. It might be used for CUBE. EXPR_CORR_CHECK Usage: EXPR_CORR_CHECK Description: Unknown. It might instruct the parser to do referencing checking where analyzing Expression Filter. INCLUDE_VERSION Usage: INCLUDE_VERSION               Description: Unknown. It could be observed from the internal statement generated by Advanced Replication. It might be used to keep the compatibility when replicating data among databases with different versions. VECTOR_READ Usage: VECTOR_READ Description: Unknown. It might be used for Vector Filter in hash join. VECTOR_READ_TRACE Usage: VECTOR_READ_TRACE            Description: Unknown. It might be used for Vector Filter in hash join. USE_WEAK_NAME_RESL Usage: USE_WEAK_NAME_RESL Description: Unknown. It might instructs the parser using the internal name instead the user-defined name to find Resource Location. It could be observed from the internal statements generated statistics data gathering and Expression Filter. 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 Usage: NO_PARTIAL_COMMIT            Description: Unknown. It might be used to prevent the commit of internal recursive transaction. It could be observed from the internal statements generated by maintenance of the table with nested object. 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;   ... ...   We got below statement from the trace file. INSERT /*+ NO_PARTIAL_COMMIT REF_CASCADE_CURSOR */ INTO  "DEMO"."T_NT_B" ("NESTED_TABLE_ID","COLUMN_VALUE")  VALUES(:1, :2) REF_CASCADE_CURSOR Usage: REF_CASCADE_CURSOR           Description: Unknown. It might be used to prevent the commit of internal recursive transaction. It could be observed from the internal statements generated by maintenance of the table with nested object. Refer to the demo of NO_PARTIAL_COMMIT NO_REF_CASCADE Usage: NO_REF_CASCADE Description: Unknown. It might prevent the internal recursive statement to use the cascade cursor. SQLLDR Usage: SQLLDR Description: Unknown. It might be used in the internal statements generated by SQL*Loader. SYS_RID_ORDER Usage: SYS_RID_ORDER                Description: Unknown. It might be used in the internal statements generated by maintenance of Materialized View. OVERFLOW_NOMOVE Usage: OVERFLOW_NOMOVE Description: Unknown. It might prevent Oracle to move the data of other segment when overflow occurs due to partition splitting. LOCAL_INDEXES Usage: LOCAL_INDEXES Description: Unknown MERGE_CONST_ON Usage: MERGE_CONST_ON               Description: Unknown QUEUE_CURR Usage: QUEUE_CURR                   Description:  Unknown. It might be used for Advanced Queue. CACHE_CB Usage: CACHE_CB([<@Block>] <Table>) Description: Unknown. It might be used for Advanced Queue. Trace the process of DBMS_AQ.DEQUEUUE (delivery_mode is PERSISTENT), we got below statement. delete /*+ CACHE_CB("QUETABLET") */ from "DEMO"."QUETABLET" where rowid = :1; QUEUE_ROWP Usage: QUEUE_ROWP                   Description:  Unknown. It might be used for Advanced Queue. BUFFER Usage: BUFFER                       Description: Unknown. It might be used for Advanced Queue. NO_BUFFER Usage: NO_BUFFER                    Description: Unknown. It might be used for Advanced Queue.                              BITMAP Usage: BITMAP Description: Unknown.。