【年终推出】11G new SQL hints大全

viadeazhu · 发表于 2009-12-30 16:20

心血来潮，准备写一篇所有11G新的SQL hint的整理性文章。
11G有了一个新的视图v$sql_hint,我们可以通过它找到所有的新的SQL hints。
故名思意，INVERSE列代表这个hint相反操作的hint，VERSION列代表着这个hint正式公布引入的版本。
（注意，VERSION并不代表这个hint从哪个版本开始可以使用。因为以下有些hint，例如INDEX_RS_ASC就在10G就可以用了。所以我认为，VERSION代表着“正式公布引入”的意思。）
（蓝色的是已经是本文介绍的，其他的要么是不常用，要么是我不知道怎么使用的（undocumented））
SQL> select NAME,CLASS,INVERSE,VERSION from v$sql_hint where version like '11%' order by class,name;
NAME                         CLASS                   INVERSE                      VERSION
------------------------------ -------------------------- ------------------------------ --------------------
INDEX_RS_ASC                ACCESS                                                 11.1.0.6
INDEX_RS_DESC                ACCESS                                                 11.1.0.6
NLJ_BATCHING                ACCESS                   NO_NLJ_BATCHING             11.1.0.6
NLJ_PREFETCH                ACCESS                   NO_NLJ_PREFETCH             11.1.0.6
NO_NLJ_BATCHING             ACCESS                   NLJ_BATCHING                11.1.0.6
NO_NLJ_PREFETCH             ACCESS                   NLJ_PREFETCH                11.1.0.6
APPEND_VALUES                APPEND_VALUES             NOAPPEND                      11.2.0.1
BIND_AWARE                   BIND_AWARE                NO_BIND_AWARE                11.1.0.7
NO_BIND_AWARE                BIND_AWARE                BIND_AWARE                   11.1.0.7
CHANGE_DUPKEY_ERROR_INDEX    CHANGE_DUPKEY_ERROR_INDEX                               11.1.0.7
CHECK_ACL_REWRITE             CHECK_ACL_REWRITE       NO_CHECK_ACL_REWRITE          11.1.0.6
COALESCE_SQ                   COALESCE_SQ             NO_COALESCE_SQ                11.2.0.1
NO_COALESCE_SQ                COALESCE_SQ             COALESCE_SQ                   11.2.0.1
CONNECT_BY_ELIM_DUPS          CONNECT_BY_ELIM_DUPS    NO_CONNECT_BY_ELIM_DUPS       11.2.0.1
NO_CONNECT_BY_ELIM_DUPS       CONNECT_BY_ELIM_DUPS    CONNECT_BY_ELIM_DUPS          11.2.0.1
COST_XML_QUERY_REWRITE       COST_XML_QUERY_REWRITE    NO_COST_XML_QUERY_REWRITE    11.1.0.6
DB_VERSION                   DB_VERSION                                              11.1.0.6
DOMAIN_INDEX_FILTER          DOMAIN_INDEX_FILTER       NO_DOMAIN_INDEX_FILTER       11.1.0.6
DST_UPGRADE_INSERT_CONV       DST_UPGRADE_INSERT_CONV NO_DST_UPGRADE_INSERT_CONV    11.2.0.1
NO_DST_UPGRADE_INSERT_CONV    DST_UPGRADE_INSERT_CONV DST_UPGRADE_INSERT_CONV       11.2.0.1
EXPAND_TABLE                EXPAND_TABLE             NO_EXPAND_TABLE             11.2.0.1
NO_EXPAND_TABLE             EXPAND_TABLE             EXPAND_TABLE                11.2.0.1
FACTORIZE_JOIN                FACTORIZE_JOIN          NO_FACTORIZE_JOIN             11.2.0.1
NO_FACTORIZE_JOIN             FACTORIZE_JOIN          FACTORIZE_JOIN                11.2.0.1
GBY_PUSHDOWN                GBY_PUSHDOWN             NO_GBY_PUSHDOWN             11.1.0.6
NO_GBY_PUSHDOWN             GBY_PUSHDOWN             GBY_PUSHDOWN                11.1.0.6
IGNORE_ROW_ON_DUPKEY_INDEX    IGNORE_ROW_ON_DUPKEY_INDEX                               11.1.0.7
USE_MERGE_CARTESIAN          JOIN                                                    11.1.0.6
MONITOR                      MONITOR                   NO_MONITOR                   11.1.0.6
NO_MONITOR                   MONITOR                   MONITOR                      11.1.0.6
NO_CHECK_ACL_REWRITE          NO_CHECK_ACL_REWRITE    CHECK_ACL_REWRITE             11.1.0.6
NO_COST_XML_QUERY_REWRITE    NO_COST_XML_QUERY_REWRITE  COST_XML_QUERY_REWRITE       11.1.0.6
NO_DOMAIN_INDEX_FILTER       NO_DOMAIN_INDEX_FILTER    DOMAIN_INDEX_FILTER          11.1.0.6
NO_LOAD                      NO_LOAD                                                 11.1.0.6
NO_SUBSTRB_PAD                NO_SUBSTRB_PAD                                           11.2.0.1
NO_OUTER_JOIN_TO_INNER       OUTER_JOIN_TO_INNER       OUTER_JOIN_TO_INNER          11.1.0.6
OUTER_JOIN_TO_INNER          OUTER_JOIN_TO_INNER       NO_OUTER_JOIN_TO_INNER       11.1.0.6
NO_PLACE_DISTINCT             PLACE_DISTINCT          PLACE_DISTINCT                11.2.0.1
PLACE_DISTINCT                PLACE_DISTINCT          NO_PLACE_DISTINCT             11.2.0.1
NO_PLACE_GROUP_BY             PLACE_GROUP_BY          PLACE_GROUP_BY                11.1.0.6
PLACE_GROUP_BY                PLACE_GROUP_BY          NO_PLACE_GROUP_BY             11.1.0.6
NO_RESULT_CACHE             RESULT_CACHE             RESULT_CACHE                11.1.0.6
RESULT_CACHE                RESULT_CACHE             NO_RESULT_CACHE             11.1.0.6
RETRY_ON_ROW_CHANGE          RETRY_ON_ROW_CHANGE                                     11.1.0.7
NO_STATEMENT_QUEUING          STATEMENT_QUEUING       STATEMENT_QUEUING             11.2.0.1
STATEMENT_QUEUING             STATEMENT_QUEUING       NO_STATEMENT_QUEUING          11.2.0.1
NO_SUBQUERY_PRUNING          SUBQUERY_PRUNING          SUBQUERY_PRUNING             11.1.0.6
SUBQUERY_PRUNING             SUBQUERY_PRUNING          NO_SUBQUERY_PRUNING          11.1.0.6
NO_TRANSFORM_DISTINCT_AGG    TRANSFORM_DISTINCT_AGG    TRANSFORM_DISTINCT_AGG       11.2.0.1
TRANSFORM_DISTINCT_AGG       TRANSFORM_DISTINCT_AGG    NO_TRANSFORM_DISTINCT_AGG    11.2.0.1
NO_USE_INVISIBLE_INDEXES    USE_INVISIBLE_INDEXES    USE_INVISIBLE_INDEXES       11.1.0.6
USE_INVISIBLE_INDEXES       USE_INVISIBLE_INDEXES    NO_USE_INVISIBLE_INDEXES    11.1.0.6
NO_XMLINDEX_REWRITE          XMLINDEX_REWRITE          XMLINDEX_REWRITE             11.1.0.6
NO_XMLINDEX_REWRITE_IN_SELECT  XMLINDEX_REWRITE          XMLINDEX_REWRITE_IN_SELECT    11.1.0.6
XMLINDEX_REWRITE             XMLINDEX_REWRITE          NO_XMLINDEX_REWRITE          11.1.0.6
XMLINDEX_REWRITE_IN_SELECT    XMLINDEX_REWRITE          NO_XMLINDEX_REWRITE_IN_SELECT  11.1.0.6
XMLINDEX_SEL_IDX_TBL          XMLINDEX_SEL_IDX_TBL                                     11.2.0.1
XML_DML_RWT_STMT             XML_DML_RWT_STMT                                        11.1.0.6
58 rows selected.
1.INDEX_RS_ASC，INDEX_RS_DESC
这两个hints其实10203就可以使用了，最开始我是从这个bug看到他们俩的：
Bug 4323868 - INDEX hints can lead to INDEX SCAN FULL [ID 4323868.8]
因为有时INDEX这个hint无法控制是走INDEX RANGE SCAN还是INDEX FULL SCAN。
并且优化器常常会很SB无法判断，于是引入他们俩强制走INDEX RANGE SCAN（RS就是这个意思）。
例如：
select /*+index_rs_desc(t)*/  count(id) from t where id=1;
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    |    1 |    3 | 188 (2)| 00:00:03 |
| 1 |  SORT AGGREGATE             |    |    1 |    3 |          |       |
|*  2 | INDEX RANGE SCAN DESCENDING| I | 104K| 304K| 188 (2)| 00:00:03 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
   filter("ID"=1)
select /*+index_rs(t)*/  count(id) from t where id=1;
--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    |    1 |    3 | 188 (2)| 00:00:03 |
| 1 |  SORT AGGREGATE |    |    1 |    3 |          |       |
|*  2 | INDEX RANGE SCAN| I | 104K| 304K| 188 (2)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
2.NLJ_BATCHING，NO_NLJ_BATCHING
这是一种11G新的内部优化算法。目前在网上几乎没有任何参考资料讲如何实现的。
从《Troubleshooting Oracle Performance》书上的Chapter 10 Optimizing Joins我可以找到只言片语：
“As of Oracle Database 11g, the following execution plan might be observed instead of the
previous one. Note that even if the query is always the same (that is, a two-table join), the
execution plan contains two nested loop joins! A simple performance test showed an improvement
of about 10 percent using it. This is probably because of a new internal optimization
that applies only to the new execution plan. To control this new execution plan, the hints
nlj_batching and no_nlj_batching are available.”
让我举个例，一个很简单的两个表的neested loop join：
create table t1 as select * from dba_objects;
create index t1idx on t1(object_id);
create table t2 as select * from dba_objects;
create index t2idx on t2(object_id);
select /*+use_nl(t1 t2) index(t1) index(t2) ordered NO_NLJ_BATCHING(t2)*/
t2.* from t1,t2
where t1.object_id=999
and t2.object_id=t1.object_id;
-------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    | 19511 |  4191K| 282 (1)| 00:00:04 |
| 1 |  TABLE ACCESS BY INDEX ROWID| T2 | 140 | 28980 |    2 (0)| 00:00:01 |
| 2 |   NESTED LOOPS             |    | 19511 |  4191K| 282 (1)| 00:00:04 |
|*  3 | INDEX RANGE SCAN       | T1IDX | 140 |  1820 |    1 (0)| 00:00:01 |
|*  4 | INDEX RANGE SCAN       | T2IDX | 56 |    |    1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."OBJECT_ID"=999)
4 - access("T2"."OBJECT_ID"=999)

接着看看如果使用NLJ_BATCHING后SQL PLAN会变成什么样：
select /*+use_nl(t1 t2) index(t1) index(t2) ordered NLJ_BATCHING(t2)*/
t2.* from t1,t2
where t1.object_id=999
and t2.object_id=t1.object_id;
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    | 19511 |  4191K| 282 (1)| 00:00:04 |
| 1 |  NESTED LOOPS             |    |    |    |          |       |
| 2 | NESTED LOOPS             |    | 19511 |  4191K| 282 (1)| 00:00:04 |
|*  3 | INDEX RANGE SCAN       | T1IDX | 140 |  1820 |    1 (0)| 00:00:01 |
|*  4 | INDEX RANGE SCAN       | T2IDX | 56 |    |    1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 140 | 28980 |    2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."OBJECT_ID"=999)
4 - access("T2"."OBJECT_ID"=999)
可以看到，当我们使用NLJ_BATCHING后，一个两个表的neested loop join在执行计划里会显示两个neested loops。
经过我的若干大数据量的实验，并没有发现使用NLJ_BATCHING有特别大的如前面文档中所述的10%的improvement。
对于如下的小实验，使用NLJ_BATCHING反而有微小的performance degradation。
create table t1 as select * from dba_objects where rownum<=1000;
create table t2 as select * from dba_objects where rownum<=1000;
update t1 set object_id=999;
update t2 set object_id=999;
create index t1idx on t1(object_id);
create index t2idx on t2(object_id);
select /*+use_nl(t1 t2) index(t1) index(t2) ordered NO_NLJ_BATCHING(t2)*/
t2.* from t1,t2
where t1.object_id=999
and t2.object_id=t1.object_id;
Elapsed: 00:00:56.73

Statistics
----------------------------------------------------------
      324  recursive calls
      0  db block gets
   149706  consistent gets
      6  physical reads
      0  redo size
45993532  bytes sent via SQL*Net to client
   733849  bytes received via SQL*Net from client
   66668  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
1000000  rows processed
select /*+use_nl(t1 t2) index(t1) index(t2) ordered NLJ_BATCHING(t2)*/
t2.* from t1,t2
where t1.object_id=999
and t2.object_id=t1.object_id;

Elapsed: 00:00:57.15
Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
   149674  consistent gets
      0  physical reads
      0  redo size
45993532  bytes sent via SQL*Net to client
   733849  bytes received via SQL*Net from client
   66668  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
1000000  rows processed
BTW，这里有个隐藏参数控制是否默认开启NLJ_BATCHING：
_nlj_batching_enabled ： enable batching of the RHS IO in NLJ
3.APPEND_VALUES，NOAPPEND
11G的APPEND_VALUES能让普通的insert...VALUES语句使用直接路径INSERT。
在以前我们常常使用insert /*+append*/ into <table> select...子句来使用直接路径INSERT。
从Oracle文档中看到：
“In direct-path INSERT, data is appended to the end of the table, rather than using existing space currently allocated to the table. As a result, direct-path INSERT can be considerably faster than conventional INSERT.”
这个hint对于我们需要批量insert特定的数据时，可以不用先sqlldr进一个临时表，然后使用insert+append+select子句来实现了。我们现在可以直接在程序里加上APPEND_VALUES hint来实现。
以下实验验证明普通insert和insert /*+APPEND_VALUES*/的区别（以一个ASSM tablespace的表为例）：
create table testhao(n1 char(2000),n2 char(2000),n3 char(2000));
insert into testhao values('1','2','3');
commit;
SQL> exec hao_show_space('TESTHAO');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "HAOZHU_USER.TESTHAO"
##################################################
UNUSED BLOCKS...........................0
UNUSED Bytes............................0
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................1
FS2 Bytes ..............................8192
--------------------------------------------------
FS3 Blocks..............................0
FS3 Bytes ..............................0
--------------------------------------------------
FS4 Blocks..............................4
FS4 Bytes ..............................32768
--------------------------------------------------
FULL BLOCKS.............................0
FULL_BYTES .............................0
##################################################
Data Blocks(under HWM)..................5
All Blocks (under HWM)..................8
Total Blocks............................8
Total Bytes.............................65536
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................224
Last Used Block.........................8
PL/SQL procedure successfully completed.
SQL> insert /*+APPEND_VALUES*/ into testhao values('1','2','3');
1 row created.
SQL> exec hao_show_space('TESTHAO');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "HAOZHU_USER.TESTHAO"
##################################################
UNUSED BLOCKS...........................8
UNUSED Bytes............................65536
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................1
FS2 Bytes ..............................8192
--------------------------------------------------
FS3 Blocks..............................0
FS3 Bytes ..............................0
--------------------------------------------------
FS4 Blocks..............................4
FS4 Bytes ..............................32768
--------------------------------------------------
FULL BLOCKS.............................0
FULL_BYTES .............................0
##################################################
Data Blocks(under HWM)..................5
All Blocks (under HWM)..................8
Total Blocks............................16
Total Bytes.............................131072
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................224
Last Used Block.........................8
PL/SQL procedure successfully completed.
注释：以上可见直接路径insert后，HWM以下的blocks数目没变，但是HWM以上的UNUSED BLOCKS增加了，这是因为此时一个新的extent被分配进来了。并且直接路径insert的数据在HWM之上。
接着，我们commit。
SQL> commit;
Commit complete.
SQL>  exec hao_show_space('TESTHAO');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "HAOZHU_USER.TESTHAO"
##################################################
UNUSED BLOCKS...........................7
UNUSED Bytes............................57344
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................1
FS2 Bytes ..............................8192
--------------------------------------------------
FS3 Blocks..............................0
FS3 Bytes ..............................0
--------------------------------------------------
FS4 Blocks..............................4
FS4 Bytes ..............................32768
--------------------------------------------------
FULL BLOCKS.............................1
FULL_BYTES .............................8192
##################################################
Data Blocks(under HWM)..................6
All Blocks (under HWM)..................9
Total Blocks............................16
Total Bytes.............................131072
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................232
Last Used Block.........................1
PL/SQL procedure successfully completed.
注释：从上可见，commit之后，直接路径insert的数据才可见，表现在HWM下多了一个data block。
接着，我们比较下普通insert。
SQL> insert into testhao values('1','2','3');
1 row created.
SQL> exec hao_show_space('TESTHAO');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "HAOZHU_USER.TESTHAO"
##################################################
UNUSED BLOCKS...........................7
UNUSED Bytes............................57344
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................2
FS2 Bytes ..............................16384
--------------------------------------------------
FS3 Blocks..............................0
FS3 Bytes ..............................0
--------------------------------------------------
FS4 Blocks..............................3
FS4 Bytes ..............................24576
--------------------------------------------------
FULL BLOCKS.............................1
FULL_BYTES .............................8192
##################################################
Data Blocks(under HWM)..................6
All Blocks (under HWM)..................9
Total Blocks............................16
Total Bytes.............................131072
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................232
Last Used Block.........................1
PL/SQL procedure successfully completed.
注释：以上可见，普通insert直接使用了当前可用的一个block，并没有使用HWM以上的block。
APPEND_VALUES和APPEND这两个hints的反向hint即是NO_APPEND。
那什么时候我们需要用NO_APPEND呢？
既然我们需要显示加上hint才能走直接路径INSERT，那不加这些hints不就可以不走直接路径么？
上面这句话只说对了一半，因为我们走并行insert时，默认是开启直接路径INSERT的。
所以，在这个时候我们需要显示加上NO_APPEND表明不希望走直接路径。
“Conventional INSERT is the default in serial mode, and direct-path INSERT is the default in parallel mode.”
4.BIND_AWARE，NO_BIND_AWARE
跟11G Adaptive Cursor Sharing相关的hint。
具体参见我的另一篇研究ACS的文章：
http://space.itpub.net/15415488/viewspace-621535

viadeazhu · 发表于 2009-12-30 16:21

5.CHANGE_DUPKEY_ERROR_INDEX，IGNORE_ROW_ON_DUPKEY_INDEX和RETRY_ON_ROW_CHANGE
这三个hints作用各不相同，将他们归于一类介绍是因为他们同属于一类--Online Application Upgrade Hints：“The CHANGE_DUPKEY_ERROR_INDEX, IGNORE_ROW_ON_DUPKEY_INDEX, and RETRY_ON_ROW_CHANGE hints are unlike other hints in that they have a semantic effect. The general philosophy explained in "Hints" does not apply for these three hints.”from 《SQL Language Reference 11.2》。
新的hint是因新的feature而产生，而他们三个是为了11GR2的一个革命性的feature服务的--Edition-Based Redefinition。简单的说，Oracle允许你在升级应用的时候，为了保证HA，可以让你同时运行新旧两个应用，为了不影响这两个应用，或者说更好的维护这两个在应用层面上的并发，Oracle引入了这三个hints。下面我就功能上介绍一下这三个hints，大家可根据自己的需要在其他时候考虑使用这三个hints。
首先介绍CHANGE_DUPKEY_ERROR_INDEX。
众所周知，当我们发生违反唯一约束时，会报ORA-00001，但使用CHANGE_DUPKEY_ERROR_INDEX这个hint后，会将错误号更改为ORA-38911。
SQL> create table testbyhao
  2  (id number primary key);
Table created.
SQL> insert into testbyhao values(1);
1 row created.
SQL> insert into testbyhao values(1);
insert into testbyhao values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (HAOZHU_USER.SYS_C004147) violated

SQL> insert /*+ change_dupkey_error_index (testbyhao(id)) */
  2  into testbyhao values (1);
insert /*+ change_dupkey_error_index (testbyhao(id)) */
*
ERROR at line 1:
ORA-38911: unique constraint (HAOZHU_USER.SYS_C004147) violated
我们查看这两个ERROR的解释：
> oerr ora 38911
38911, 00000, "unique constraint (%s.%s) violated"
// *Cause: An INSERT statement with a CHANGE_DUPKEY_ERROR_INDEX
//       hint tried to insert a duplicate key into a table.
// *Action: Either remove the unique restriction or do not insert the key.
> oerr ora 1
00001, 00000, "unique constraint (%s.%s) violated"
// *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
//       For Trusted Oracle configured in DBMS MAC mode, you may see
//       this message if a duplicate entry exists at a different level.
// *Action: Either remove the unique restriction or do not insert the key.
我们发现ORA-38911只能发生在insert上，不能发生在update上。我们来验证一下。
SQL> insert into testbyhao values (2);
1 row created.
SQL> select * from testbyhao;
      ID
----------
      1
      2
SQL> update testbyhao
  2  set id=1 where id=2;
update testbyhao
*
ERROR at line 1:
ORA-00001: unique constraint (HAOZHU_USER.SYS_C004147) violated

SQL> update /*+ change_dupkey_error_index (testbyhao(id)) */
  2  testbyhao set id=1 where id=2;
update /*+ change_dupkey_error_index (testbyhao(id)) */
*
ERROR at line 1:
ORA-00001: unique constraint (HAOZHU_USER.SYS_C004147) violated
果然，CHANGE_DUPKEY_ERROR_INDEX这个hint只能为insert服务。
接着介绍IGNORE_ROW_ON_DUPKEY_INDEX。
顾名思义，当我们使用这个hint，并且批量insert时，如果有违反唯一约束的Error发生，会有行级的rollback发生，而不是rollback整个insert SQL。
SQL> select * from testbyhao;
      ID
----------
      1
      2
SQL> insert into testbyhao
  2  select rownum from user_tables
  3  where rownum<=5;
insert into testbyhao
*
ERROR at line 1:
ORA-00001: unique constraint (HAOZHU_USER.SYS_C004147) violated
SQL> select * from testbyhao;
      ID
----------
      1
      2
SQL> insert /*+IGNORE_ROW_ON_DUPKEY_INDEX(testbyhao(id)) */
  2  into testbyhao
  3  select rownum from user_tables
  4  where rownum<=5;

3 rows created.
SQL> select * from testbyhao;
      ID
----------
      1
      2
      3
      4
      5
其他需要注意的是，这个hint也只能为普通的单表insert服务。
并且CHANGE_DUPKEY_ERROR_INDEX和IGNORE_ROW_ON_DUPKEY_INDEX不能同时使用，否则会报错。

SQL> insert /*+CHANGE_DUPKEY_ERROR_INDEX(testbyhao(id)) IGNORE_ROW_ON_DUPKEY_INDEX(testbyhao(id)) */
  2  into testbyhao
select rownum from user_tables
where rownum<=5;  3 4
select rownum from user_tables
               *
ERROR at line 3:
ORA-38914: Either mutate the error or ignore row on unique violation
> oerr ora 38914
38914, 00000, "Either mutate the error or ignore row on unique violation"
// *Cause: IGNORE_ROW_ON_DUPKEY_INDEX and CHANGE_DUPKEY_ERROR_INDEX were
//       both specified in the same statement.
// *Action: You cannot specify both hints in the same statement. Remove
//       one of these two hints, or both, from the statement.
最后介绍RETRY_ON_ROW_CHANGE。
提高这个hint之前，读者最好对10G的ORA_ROWSCN伪列有初步认识。
这个hint只对update和delete有效。
它的意思是当一条update或delete在执行时，这条语句可能涉及多个行，甚至多个block的行，当他执行到行A时，行A的ROW_SCN已经比这条语句开始执行时的SCN新了（也就是说在这条语句执行过程中，有其他语句对行A进行过修改），那么，这条语句重新再执行一遍。我们接下来从宏观上用实验了解下这个hint的作用。
http://rwijk.blogspot.com/2009/10/three-new-hints.html 这是一篇很不错的文章介绍这三个hints，对RETRY_ON_ROW_CHANGE从微观上分析了其作用。但我个人觉得他的实验并不能够从宏观也就是SQL结果上体现出他的一个作用。
于是我自己做了如下实验，可以很清楚看到加不加这个hint对于结果的不同影响。
准备实验所需DDL SQLs：
drop table testbyhao2;
create table testbyhao2
as
select level as id,level as id2 from
dual connect by level<=100000;
alter table testbyhao2 add primary key(id);
SQL> select count(*) from testbyhao2;
  COUNT(*)
----------
100000
我打开Session A，将所有行的id2都设成跟第99999行的id2一样。
由于初始时所有行的id1=id2，所以我可以猜想这条update执行完毕后，所有行的id2=99999。
--Session A:
UPDATE testbyhao2
SET id2 = (select id2 from testbyhao2 where id=99999);
(。。。进行中。。。)
这时，我打开Session B，将第99999行的id2改为0,并且commit。
这时，Session A的update还没有触碰到最后一个block。
--Session B:
SQL> UPDATE testbyhao2
  2  SET id2 = 0
  3  WHERE id = 99999;
1 row updated.
SQL> commit;
Commit complete.
接着返回Session A，等待其update完成，并且commit。
--Session A:
100000 rows updated.
SQL> commit;
Commit complete.
再查看所有行的id2是否都等于99999：
SQL> select count(*),id2 from testbyhao2 group by id2;
  COUNT(*)       ID2
---------- ----------
100000    99999
果然，这就是常规情况，在这种情况下，后执行的Session B中的update丢失了。
我们可以想象，Session A就是一个DBA的batch job，Session B是用户的一条更新。
虽然用户的那条更新的发出时间在batch job之后发生，但是由于batch job需要非常长的时间，所以，用户B的后来发出的更新丢失了。
怎么样让用户的更新先于同时正在进行的batch job呢？如果你需要这样的需求，就可以考虑加上这个hint。
重建同样的表，做同样的事情：
--Session A:
UPDATE  /*+RETRY_ON_ROW_CHANGE*/ testbyhao2
SET id2 = (select id2 from testbyhao2 where id=99999);
(。。。进行中。。。)
--Session B:
SQL> UPDATE testbyhao2
  2  SET id2 = 0
  3  WHERE id = 99999;
1 row updated.
SQL> commit;
Commit complete.
--Session A:
100000 rows updated.
SQL> commit;
Commit complete.
SQL>  select count(*),id2 from testbyhao2 group by id2;
  COUNT(*)       ID2
---------- ----------
100000       0
如上结果让我可以试着描述一下Session A的那条update做了什么事情：
首先取出id=99999的id2，即也为99999，然后用来开始更新整个表。
Session B的update将id=99999的id2设置为0。
Session A的update触碰到最后一个block时，发现最后一个block的ORA_ROWSCN伪列已经被其他Session更新过了，于是Session A的update选择先rollback，然后再重新做一次。
所以，最后的结果是所有的行的id2=0，而不是前面的实验那样等于99999。
6.MONITOR，NO_MONITOR
当AWR开启时（即statistics_level=TYPICAL或ALL时），SQL monitoring feature就打开了。简单的说，Oracle会自动monitor那些long running的SQL。
然而我们有了新的hint来强制或者强制避免被monitor，这两个hints就是MONITOR和NO_MONITOR。
这两个hints生效还有一个前提条件，就是CONTROL_MANAGEMENT_PACK_ACCESS这个参数设为DIAGNOSTIC+TUNING（这是默认值）。
当一个SQL被monitor，我们可以通过dbms_sqltune包来得到结果：
SQL> select /*testbyhao*/ /*+MONITOR*/ * from dual;
DUM
---
X
SQL>
SQL> set long 10000000
SQL> set longchunksize 10000000
SQL> set linesize 200
SQL> select dbms_sqltune.report_sql_monitor from dual;

REPORT_SQL_MONITOR
-------------------
SQL Monitoring Report
SQL Text
------------------------------
select /*testbyhao*/ /*+MONITOR*/ * from dual
Global Information
------------------------------
Status             :  DONE (ALL ROWS)
Instance ID       :  1
Session          :  SYS (138:485)
REPORT_SQL_MONITOR
------------------------------
SQL ID             :  bd6wgsp6gak64
SQL Execution ID :  16777221
Execution Started :  12/18/2009 20:52:39
First Refresh Time  :  12/18/2009 20:52:39
Last Refresh Time :  12/18/2009 20:52:39
Duration          :  .000634s
Module/Action    :  [email=sqlplus@xxx]sqlplus@xxx[/email] (TNS V1-V3)/-
Service          :  SYS$USERS
Program          :  [email=sqlplus@xxx]sqlplus@xxx[/email] (TNS V1-V3)
Fetch Calls       :  1

REPORT_SQL_MONITOR
------------------------------
Global Stats
=======================================
| Elapsed |  Other | Fetch | Buffer |
| Time(s) | Waits(s) | Calls |  Gets  |
=======================================
| 0.00 |    0.00 |    1 |    3 |
=======================================
SQL Plan Monitoring Details (Plan Hash Value=3543395131)
=========================================================================================================================
| Id |    Operation    | Name |  Rows | Cost | Time | Start  | Execs | Rows | Activity | Activity Detail |
REPORT_SQL_MONITOR
------------------------------------------------------------------------------------------------------------------------
| |                   |    | (Estim) |    | Active(s) | Active |    | (Actual) | (%) | (# samples) |
=========================================================================================================================
|  0 | SELECT STATEMENT |    |       |    |       1 |    +0 |    1 |       1 |       |                |
|  1 | TABLE ACCESS FULL | DUAL |       |    |       1 |    +0 |    1 |       1 |       |                |
=========================================================================================================================

也可以通过v$sql_monitor视图来得到其信息。
如果不加/*+MONITOR*/的话，像如上这种十分轻量级的SQL是不会被自动monitor的。
但是，如果我执行一行long running的SQL话，即使不加hint，也会被记录。
于是，我借用Tanel的一个能够产生非常多PIO的SQL来模拟这种情况。
（Tanel的许多SQL可以从他的官方网站上免费下载作测试）
cat lotspios.sql
--------------------------------------------------------------------------------
--
-- File name: lotspios.sql
-- Purpose:    Generate Lots of Physical IOs for testing purposes
--
-- Author:    Tanel Poder
-- Copyright: (c) http://www.tanelpoder.com
--
-- Usage:    @lotspios <number>
--             @lotspios 100
--             @lotspios 1000000
--
-- Other:    This script. just does a full table scan on all tables it can
--             see, thus it generates mainly scattered or direct path reads
--
--------------------------------------------------------------------------------
declare
str varchar2(1000);
x number;
begin
for i in 1..&1 loop
   for t in (select owner, table_name from all_tables) loop
            begin
                     execute immediate 'select /*+ FULL(t) */ count(*) from '||t.owner||'.'||t.table_name||' t' into x;
            exception
                     when others then null;
            end;
   end loop; -- t
end loop; -- i
end;
/
SQL> @lotspio 9999999
当我执行上述PLSQL时，没有加/*+MONITOR*/这个hint，但是不一会你就会看到v$sql_monitor有这条记录了，说明被我们的SQL monitor feature抓住了。
而后我在这个PLSQL里加上/*+NO_MONITOR*/ hint，如下：
cat lotspios2.sql
--------------------------------------------------------------------------------
--
-- File name: lotspios.sql
-- Purpose:    Generate Lots of Physical IOs for testing purposes
--
-- Author:    Tanel Poder
-- Copyright: (c) http://www.tanelpoder.com
--
-- Usage:    @lotspios <number>
--             @lotspios 100
--             @lotspios 1000000
--
-- Other:    This script. just does a full table scan on all tables it can
--             see, thus it generates mainly scattered or direct path reads
--
--------------------------------------------------------------------------------
declare
str varchar2(1000);
x number;
begin
for i in 1..&1 loop
   for t in (select owner, table_name from all_tables) loop
            begin
                     execute immediate 'select /*+ NO_MONITOR FULL(t) */ count(*) from '||t.owner||'.'||t.table_name||' t' into x;
            exception
                     when others then null;
            end;
   end loop; -- t
end loop; -- i
end;
/
如上，当加上如上hint后，就能成功躲过SQL monitor的监视。
7.RESULT_CACHE，NO_RESULT_CACHE
这跟11G result cache new feature相关。
具体参见我的：
《11G result cache新特性的一些发现和个人见解》
http://space.itpub.net/15415488/viewspace-615398
和
《11G result cache新特性的更多深入研究》
http://space.itpub.net/15415488/viewspace-615873 8.USE_INVISIBLE_INDEXES，NO_USE_INVISIBLE_INDEXES
11G新特性INVISIBLE INDEX相关hints。
他们用来强制到底使不使用INVISIBLE INDEX。
于是就谈到了另一个系统参数：OPTIMIZER_USE_INVISIBLE_INDEXES，这个参数和这两个hints共同影响着INVISIBLE INDEX的使用与否。
下面举例说明：
创建一个测试表和一个INVISIBLE INDEX。
SQL> create table testhao as select * from dba_objects;
SQL> CREATE INDEX idxhao ON
  2  testhao(object_id) INVISIBLE;
SQL> analyze table testhao compute statistics;
SQL> select VISIBILITY from user_indexes
  2  where INDEX_NAME='IDXHAO';
VISIBILITY
---------------------------
INVISIBLE
当OPTIMIZER_USE_INVISIBLE_INDEXES=false时，默认不使用INVISIBLE INDEX。
SQL> alter session set OPTIMIZER_USE_INVISIBLE_INDEXES=false;
Session altered.
SQL> select count(*) from testhao
  2  where object_id=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1249183362
------------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |       |    1 |    4 | 50 (2)| 00:00:01 |
| 1 |  SORT AGGREGATE |       |    1 |    4 |          |       |
|*  2 | TABLE ACCESS FULL| TESTHAO |    1 |    4 | 50 (2)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=1)
我们只有使用hint USE_INVISIBLE_INDEXES来强制走INVISIBLE INDEX。
SQL> select /*+USE_INVISIBLE_INDEXES*/
  2  count(*) from testhao
  3  where object_id=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1341972038
----------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |       |    1 |    4 |    1 (0)| 00:00:01 |
| 1 |  SORT AGGREGATE |       |    1 |    4 |          |       |
|*  2 | INDEX RANGE SCAN| IDXHAO |    1 |    4 |    1 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1)
当OPTIMIZER_USE_INVISIBLE_INDEXES=true时，默认使用INVISIBLE INDEX。
SQL> alter session set OPTIMIZER_USE_INVISIBLE_INDEXES=true;
Session altered.
SQL> select count(*) from testhao
  2  where object_id=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1341972038
----------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |       |    1 |    4 |    1 (0)| 00:00:01 |
| 1 |  SORT AGGREGATE |       |    1 |    4 |          |       |
|*  2 | INDEX RANGE SCAN| IDXHAO |    1 |    4 |    1 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1)
我们只有使用hint NO_USE_INVISIBLE_INDEXES来强制不走INVISIBLE INDEX。
SQL> select /*+NO_USE_INVISIBLE_INDEXES*/
  2  count(*) from testhao
  3  where object_id=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1249183362
------------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |       |    1 |    4 | 50 (2)| 00:00:01 |
| 1 |  SORT AGGREGATE |       |    1 |    4 |          |       |
|*  2 | TABLE ACCESS FULL| TESTHAO |    1 |    4 | 50 (2)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=1)
注意，经测试，RBO下这两个hints无效。

希望大家踊跃补充。

--ViadeaZhu