11G new SQL hints大全
转自 http://www.itpub.net/thread-1254018-1-1.html
心血来潮，准备写一篇所有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 INSERTcan 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
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
