Oracle SQL提示含义与示例 --- 语句运行和数据操作提示

[English]

作者： fuyuncat

来源： www.HelloDBA.com

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

分享到 

《Oracle 高性能SQL引擎剖析：Oracle SQL 优化与调优技术详解》一书的附录部分。作为对该书的补充，帮助读者理解和掌握“提示”这一项在SQL优化中使用的这一重要辅助手段。 语句运行提示 APPEND 语法：APPEND                       描述：指示优化器以追加方式向表直接插入数据； HELLODBA.COM>exec sql_explain('insert /*+append*/ into t_objects_bak select * from t_objects', 'BASIC OUTLINE')   -------------------------------------------- | Id  | Operation          | Name          | -------------------------------------------- |   0 | INSERT STATEMENT   |               | |   1 |  LOAD AS SELECT    | T_OBJECTS_BAK | |   2 |   TABLE ACCESS FULL| T_OBJECTS     | -------------------------------------------- APPEND_VALUES 语法：APPEND_VALUES                描述：指示优化器以追加方式向表直接插入数据；该提示仅支持INSERT ... VALUES 形式的语句； HELLODBA.COM>exec sql_explain('insert /*+append_values*/ into t_objects(object_id, owner, object_name, status) values(:1, :2, :3, :4)', 'BASIC OUTLINE')   -------------------------------------- | Id  | Operation        | Name      | -------------------------------------- |   0 | INSERT STATEMENT |           | |   1 |  LOAD AS SELECT  | T_OBJECTS | |   2 |   BULK BINDS GET |           | -------------------------------------- NOAPPEND 描述：禁止优化器以追加方式向表直接插入数据； HELLODBA.COM>exec sql_explain('insert /*+noappend*/ into t_objects_bak select * from t_objects', 'BASIC OUTLINE')   -------------------------------------------------- | Id  | Operation                | Name          | -------------------------------------------------- |   0 | INSERT STATEMENT         |               | |   1 |  LOAD TABLE CONVENTIONAL | T_OBJECTS_BAK | |   2 |   TABLE ACCESS FULL      | T_OBJECTS     | -------------------------------------------------- NLJ_BATCHING 语法：NLJ_BATCHING                 描述：指示优化器以嵌套循环关联批量读取的方式访问表 HELLODBA.COM>exec sql_explain('select /*+ use_nl(o t) index(o T_OBJECTS_M_IDX8) index(t T_TABLES_IDX3) LEADING(t) NLJ_BATCHING(o)*/count(LIO) from T_OBJECTS_M O, T_TABLES t where o.owner=t.owner and o.object_name=t.table_name and t.tablespace_name=:A', 'TYPICAL OUTLINE');   --------------------------------------------------------------------------------------------------- | Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT               |                  |     1 |    68 |   914   (1)| 00:00:10 | |   1 |  SORT AGGREGATE                |                  |     1 |    68 |            |          | |   2 |   NESTED LOOPS                 |                  |       |       |            |          | |   3 |    NESTED LOOPS                |                  |  7985 |   530K|   914   (1)| 00:00:10 | |   4 |     TABLE ACCESS BY INDEX ROWID| T_TABLES         |   299 | 10465 |    16   (0)| 00:00:01 | |*  5 |      INDEX RANGE SCAN          | T_TABLES_IDX3    |   299 |       |     1   (0)| 00:00:01 | |*  6 |     INDEX RANGE SCAN           | T_OBJECTS_M_IDX8 |     1 |       |     2   (0)| 00:00:01 | |   7 |    TABLE ACCESS BY INDEX ROWID | T_OBJECTS_M      |    27 |   891 |     3   (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- NO_NLJ_BATCHING 语法：NO_NLJ_BATCHING([<@查询块>] <表>) 描述：禁止优化器以嵌套循环关联批量读取的方式访问表 HELLODBA.COM>exec sql_explain('select /*+ use_nl(o t) index(o T_OBJECTS_M_IDX8) index(t T_TABLES_IDX3) LEADING(t) NO_NLJ_BATCHING(o)*/count(LIO) from T_OBJECTS_M O, T_TABLES t where o.owner=t.owner and o.object_name=t.table_name and t.tablespace_name=:A', 'TYPICAL OUTLINE');   --------------------------------------------------------------------------------------------------- | Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT               |                  |     1 |    68 |   914   (1)| 00:00:10 | |   1 |  SORT AGGREGATE                |                  |     1 |    68 |            |          | |   2 |   TABLE ACCESS BY INDEX ROWID  | T_OBJECTS_M      |    27 |   891 |     3   (0)| 00:00:01 | |   3 |    NESTED LOOPS                |                  |  7985 |   530K|   914   (1)| 00:00:10 | |   4 |     TABLE ACCESS BY INDEX ROWID| T_TABLES         |   299 | 10465 |    16   (0)| 00:00:01 | |*  5 |      INDEX RANGE SCAN          | T_TABLES_IDX3    |   299 |       |     1   (0)| 00:00:01 | |*  6 |     INDEX RANGE SCAN           | T_OBJECTS_M_IDX8 |     1 |       |     2   (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- NLJ_PREFETCH 语法：NLJ_PREFETCH([<@查询块>] <表>) 描述：指示优化器以嵌套循环关联预提取的方式访问表 HELLODBA.COM>exec sql_explain('select /*+ use_nl(o t) index(o T_OBJECTS_M_IDX8) index(t T_TABLES_IDX3) LEADING(t) NLJ_PREFETCH(o)*/count(LIO) from T_OBJECTS_M O, T_TABLES t where o.owner=t.owner and o.object_name=t.table_name and t.tablespace_name=:A', 'TYPICAL OUTLINE');   --------------------------------------------------------------------------------------------------- | Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT               |                  |     1 |    68 |   914   (1)| 00:00:10 | |   1 |  SORT AGGREGATE                |                  |     1 |    68 |            |          | |   2 |   TABLE ACCESS BY INDEX ROWID  | T_OBJECTS_M      |    27 |   891 |     3   (0)| 00:00:01 | |   3 |    NESTED LOOPS                |                  |  7985 |   530K|   914   (1)| 00:00:10 | |   4 |     TABLE ACCESS BY INDEX ROWID| T_TABLES         |   299 | 10465 |    16   (0)| 00:00:01 | |*  5 |      INDEX RANGE SCAN          | T_TABLES_IDX3    |   299 |       |     1   (0)| 00:00:01 | |*  6 |     INDEX RANGE SCAN           | T_OBJECTS_M_IDX8 |     1 |       |     2   (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- NO_NLJ_PREFETCH 语法：NO_NLJ_PREFETCH([<@查询块>] <表>) 描述：禁止优化器以嵌套循环关联预提取的方式访问表 HELLODBA.COM>exec sql_explain('select /*+ use_nl(o t) index(o T_OBJECTS_M_IDX8) index(t T_TABLES_IDX3) LEADING(t) NO_NLJ_PREFETCH(o)*/count(LIO) from T_OBJECTS_M O, T_TABLES t where o.owner=t.owner and o.object_name=t.table_name and t.tablespace_name=:A', 'TYPICAL OUTLINE');   --------------------------------------------------------------------------------------------------- | Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT               |                  |     1 |    68 |   914   (1)| 00:00:10 | |   1 |  SORT AGGREGATE                |                  |     1 |    68 |            |          | |   2 |   NESTED LOOPS                 |                  |       |       |            |          | |   3 |    NESTED LOOPS                |                  |  7985 |   530K|   914   (1)| 00:00:10 | |   4 |     TABLE ACCESS BY INDEX ROWID| T_TABLES         |   299 | 10465 |    16   (0)| 00:00:01 | |*  5 |      INDEX RANGE SCAN          | T_TABLES_IDX3    |   299 |       |     1   (0)| 00:00:01 | |*  6 |     INDEX RANGE SCAN           | T_OBJECTS_M_IDX8 |     1 |       |     2   (0)| 00:00:01 | |   7 |    TABLE ACCESS BY INDEX ROWID | T_OBJECTS_M      |    27 |   891 |     3   (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- CACHE 语法：CACHE([<@查询块>] <表>) 描述：指示优化器将全表扫描读取的数据块放在LRU链表的MRU端 HELLODBA.COM>select name, value from v$mystat m, v$statname n where m.statistic# = n.statistic# and name like 'table scans% tables)';   NAME                                VALUE ------------------------------ ---------- table scans (short tables)             37 table scans (long tables)               0   HELLODBA.COM>select /*+full(t) cache(t)*/count(*) from t_objects t; … …   HELLODBA.COM>select name, value from v$mystat m, v$statname n where m.statistic# = n.statistic# and name like 'table scans% tables)';   NAME                                VALUE ------------------------------ ---------- table scans (short tables)             37 table scans (long tables)               1 NOCACHE 语法：NOCACHE([<@查询块>] <表>)                      描述：指示优化器将全表扫描读取的数据块放在LRU链表的LRU端                              HELLODBA.COM>alter system flush buffer_cache;   System altered.   HELLODBA.COM>select name, value from v$mystat m, v$statname n where m.statistic# = n.statistic# and name like 'table scans% tables)';   NAME                                VALUE ------------------------------ ---------- table scans (short tables)             35 table scans (long tables)               1   HELLODBA.COM>select /*+full(t) nocache(t)*/count(*) from t_tables t; … …   HELLODBA.COM>select name, value from v$mystat m, v$statname n where m.statistic# = n.statistic# and name like 'table scans% tables)';   NAME                                VALUE ------------------------------ ---------- table scans (short tables)             36 table scans (long tables)               1 CACHE_TEMP_TABLE 语法：CACHE_TEMP_TABLE([<@查询块>] <表>) 描述：指示优化器将扫描读取的临时表的数据块放在LRU链表的MRU端，通常这种提示出现在递归调用的语句当中。 HELLODBA.COM>begin   2     sql_explain('WITH   3                     A AS (SELECT /*+qb_name(AV)*/''x'' M FROM DUAL),   4                     B AS (SELECT /*+qb_name(BV)*/DISTINCT LEVEL L FROM A CONNECT BY LEVEL <= 10),   5                     C AS (SELECT /*+qb_name(CV)*/LPAD(LEVEL-1,(SELECT COUNT(*) FROM B),''0'') N FROM DUAL CONNECT BY LEVEL <= POWER(10,(SELECT COUNT(*) FROM B)))   6                     SELECT /*+qb_name(M)*/* FROM A, B, C where rownum>=1', 'BASIC PREDICATE');   7  end;   8  /   … …   Predicate Information (identified by operation id): ---------------------------------------------------      7 - filter(LEVEL<=10)   10 - filter(ROWNUM>=1)   18 - filter(LEVEL<=POWER(10, (SELECT /*+ */ COUNT(*) FROM  (SELECT               /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "L" FROM               "SYS"."SYS_TEMP_0FD9D663A_F1E95AE7" "T1") "B"))) NO_LOAD 语法：NO_LOAD                       描述：禁止将数据直接载入表 HELLODBA.COM>alter session enable parallel dml;   Session altered.   HELLODBA.COM>alter session set "_disable_parallel_conventional_load"=true;   Session altered.   HELLODBA.COM>exec sql_explain('insert /*+ no_load(d) parallel(d 2) */ into t_objects_dummy2 d select /*+parallel(o 2)*/* from t_objects o', 'BASIC');   ----------------------------------------------------- | Id  | Operation                | Name             | ----------------------------------------------------- |   0 | INSERT STATEMENT         |                  | |   1 |  LOAD TABLE CONVENTIONAL | T_OBJECTS_DUMMY2 | |   2 |   PX COORDINATOR         |                  | |   3 |    PX SEND QC (RANDOM)   | :TQ10000         | |   4 |     PX BLOCK ITERATOR    |                  | |   5 |      TABLE ACCESS FULL   | T_OBJECTS        | ----------------------------------------------------- NO_SUBSTRB_PAD 语法：NO_SUBSTRB_PAD 描述：对多字节字符集（如UTF8）中的多字节字符（如中文）串使用函数SUBSTRB时，禁止进行字节补齐。这一提示会导致逻辑结果的变化。 HELLODBA.COM>select /*+  */substrb(chistr,1,2) str, lengthb(substrb(chistr,1,2)) len from (select '多字节字符串' chistrfrom dual ) v;   STR         LEN ---- ----------               2   HELLODBA.COM>select /*+ NO_SUBSTRB_PAD */substrb(chistr,1,2) str, lengthb(substrb(chistr,1,2)) len from (select '多字节字符串' chistr from dual ) v;   STR        LEN --- ----------   RESULT_CACHE 语法：RESULT_CACHE                 描述：指示数据库将查询（或者查询片段）结果保存在缓存当中，使得缓存中数据能被后续的查询使用； HELLODBA.COM>show parameter result_cache   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ client_result_cache_lag              big integer 3000 client_result_cache_size             big integer 0 result_cache_max_result              integer     5 result_cache_max_size                big integer 1M result_cache_mode                    string      MANUAL result_cache_remote_expiration       integer     0   HELLODBA.COM>exec sql_explain('select * from t_tables t, (select /*+ result_cache */* from t_objects o where object_type = :A) v where v.owner = t.owner and v.object_name = t.table_name', 'BASIC PREDICATE');   --------------------------------------------------------------------- | Id  | Operation                      | Name                       | --------------------------------------------------------------------- |   0 | SELECT STATEMENT               |                            | |*  1 |  HASH JOIN                     |                            | |   2 |   VIEW                         |                            | |   3 |    RESULT CACHE                | b6zzbhhgh4knw21npw13q564wn | |   4 |     TABLE ACCESS BY INDEX ROWID| T_OBJECTS                  | |*  5 |      INDEX RANGE SCAN          | T_OBJECTS_IDX7             | |   6 |   TABLE ACCESS FULL            | T_TABLES                   | ---------------------------------------------------------------------   Result Cache Information (identified by operation id): ------------------------------------------------------      3 - column-count=16; dependencies=(DEMO.T_OBJECTS); attributes=(ordered); parameters=(nls, :A); name="select /*+ result_cache */* from t_objects o where object_type = :A" NO_RESULT_CACHE 语法：NO_RESULT_CACHE 描述：禁止数据库将查询（或者查询片段）结果保存在缓存当中； HELLODBA.COM>alter session set result_cache_mode=force;   Session altered.   HELLODBA.COM>exec sql_explain('select /*+ no_result_cache */* from t_tables t', 'TYPICAL');   ------------------------------------------------------------------------------ | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT  |          |  2696 |   634K|    28   (4)| 00:00:01 | |   1 |  TABLE ACCESS FULL| T_TABLES |  2696 |   634K|    28   (4)| 00:00:01 | ------------------------------------------------------------------------------ SYS_DL_CURSOR 语法：SYS_DL_CURSOR 描述：这个提示在运行SQL*Loader直接（Direct=TRUE）加载数据时，会在相关INSERT语句上加上。指示采用直接加载（Direct Load）游标，对数据进行批量插入。 示例（在运行SQL*Loader直接加载数据后，从共享缓存中可以看到以下语句）： HELLODBA.COM>select sql_text, module from v$sql where sql_text like 'INSERT /*+ SYS_DL_CURSOR */%';   SQL_TEXT                                                                                         MODULE ------------------------------------------------------------------------------------------------ ------ INSERT /*+ SYS_DL_CURSOR */ INTO "DEMO"."T_TABLES_LD" ("OWNER","TABLE_NAME") VALUES (NULL,NULL)  SQL Loader Direct Path Load TRACING 语法：TRACING(verify <数字> strip <数字>) 描述：未知。可能是用于跟踪ASM的条带化过程的提示。使用该提示后，执行计划会多出一个“MONITORING STRIP”操作，并且不会有数据返回。 HELLODBA.COM>exec sql_explain('select /*+qb_name(M) full(t_objects) TRACING(verify 1, strip 1) */* from t_objects', 'TYPICAL OUTLINE');   -------------------------------------------------------------------------------- | Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |           | 72116 |  7042K|   297   (3)| 00:00:03 | |   1 |  MONITORING STRIP  |           | 72116 |  7042K|   297   (3)| 00:00:03 | |   2 |   TABLE ACCESS FULL| T_OBJECTS | 72116 |  7042K|   297   (3)| 00:00:03 | -------------------------------------------------------------------------------- 数据操作提示 CHANGE_DUPKEY_ERROR_INDEX 语法：CHANGE_DUPKEY_ERROR_INDEX(<表>, <索引>) 或者 CHANGE_DUPKEY_ERROR_INDEX(<表>, <索引字段列表>) 描述：插入记录违反唯一性约束时，抛出ORA-38911而非ORA-00001错误。 HELLODBA.COM>insert into t_tables(owner, table_name) values ('SYS', 'TAB$'); insert into t_tables(owner, table_name) values ('SYS', 'TAB$') * ERROR at line 1: ORA-00001: unique constraint (DEMO.T_TABLES_PK) violated     HELLODBA.COM>insert /*+CHANGE_DUPKEY_ERROR_INDEX(t_tables t_tables_pk)*/into t_tables(owner, table_name) values ('SYS','TAB$'); insert /*+CHANGE_DUPKEY_ERROR_INDEX(t_tables t_tables_pk)*/into t_tables(owner, table_name) values ('SYS', 'TAB$') * ERROR at line 1: ORA-38911: unique constraint (DEMO.T_TABLES_PK) violated IGNORE_ROW_ON_DUPKEY_INDEX 语法：IGNORE_ROW_ON_DUPKEY_INDEX(<表> <唯一索引>) 或者 IGNORE_ROW_ON_DUPKEY_INDEX(<表> <唯一索引字段列表>) 描述：指示SQL执行器向存在唯一约束的表中插入数据时，忽略重复数据 HELLODBA.COM>create table t(a number primary key);   Table created.     HELLODBA.COM>insert into t values(2);   1 row created.   HELLODBA.COM>commit;   Commit complete.   HELLODBA.COM>insert /*+IGNORE_ROW_ON_DUPKEY_INDEX(t (a))*/into t select level from dual connect by level <= 3;   2 rows created.   HELLODBA.COM>commit;   Commit complete.   HELLODBA.COM>select * from t;            A ----------          1          2          3 RETRY_ON_ROW_CHANGE 语法：RETRY_ON_ROW_CHANGE          描述：当UPDATE或DELETE操作的事务在提交之前，如果其缓存住的数据发生了变化，该提示使得事务被重新启动————这可能会导致表上的触发器被再次执行； HELLODBA.COM>-- 会话 1 HELLODBA.COM>create table t(a number primary key);   Table created.   HELLODBA.COM>insert into t values(1);   1 row created.   HELLODBA.COM>insert into t values(2);   1 row created.   HELLODBA.COM>commit;   Commit complete.   HELLODBA.COM>exec dbms_stats.gather_table_stats('DEMO', 'T');   PL/SQL procedure successfully completed.   HELLODBA.COM>select name, value from v$mystat m, v$statname n where m.statistic#=n.statistic# and name in ('consistent gets', 'no work - consistent read gets', 'transaction rollbacks');   NAME                                                                  VALUE ---------------------------------------------------------------- ---------- consistent gets                                                        1684 no work - consistent read gets                                          549 transaction rollbacks                                                     0   HELLODBA.COM>update /*+RETRY_ON_ROW_CHANGE*/t set a=a+10 where a>=2;   1 rows updated.   HELLODBA.COM>-- 会话 2 HELLODBA.COM>update t set a=a+100 where a=1;   1 row updated.   HELLODBA.COM>commit;   Commit complete.   HELLODBA.COM>-- 会话 1 HELLODBA.COM>commit;   Commit complete.   HELLODBA.COM>select name, value from v$mystat m, v$statname n where m.statistic#=n.statistic# and name in ('consistent gets', 'no work - consistent read gets', 'transaction rollbacks');   NAME                                                                  VALUE ---------------------------------------------------------------- ---------- consistent gets                                                        1686 no work - consistent read gets                                          550 transaction rollbacks                                                     0   当不使用提示时，重复上述操作，得到以下结果： HELLODBA.COM>select name, value from v$mystat m, v$statname n where m.statistic#=n.statistic# and name in ('consistent gets', 'no work - consistent read gets', 'transaction rollbacks');   NAME                                                                  VALUE ---------------------------------------------------------------- ---------- consistent gets                                                        1708 no work - consistent read gets                                          555 transaction rollbacks                                                     4 ... ...   HELLODBA.COM>select name, value from v$mystat m, v$statname n where m.statistic#=n.statistic# and name in ('consistent gets', 'no work - consistent read gets', 'transaction rollbacks');   NAME                                                                  VALUE ---------------------------------------------------------------- ---------- consistent gets                                                        1709 no work - consistent read gets                                          555 transaction rollbacks                                                     4   以上三个提示会引起逻辑结果变化的提示。