心血來潮，準備寫一篇所有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

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

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       : (TNS V1-V3)/-
Service             : SYS$USERS
Program             : (TNS V1-V3)
Fetch Calls         : 1

REPORT_SQL_MONITOR
------------------------------

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)
--
-- Usage:       @lotspios
--              @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)
--
-- Usage:       @lotspios
--              @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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15415488/viewspace-624001/，如需轉載，請註明出處，否則將追究法律責任。

上一篇： ORA-01002 “fetch out of sequence”關於cursor的一個bug

下一篇：線上重定義表導致constraint變成novalidate

請登入後發表評論登入

全部評論

viadeazhu

註冊時間：2008-08-22

博文量
79
訪問量

380033

11G new SQL hint大全

最新文章

相關文章