轉載--常見hints的基礎用法

-- 優化器名詞解釋 --

Oracle的優化器有兩種優化方式,即基於規則的優化方式(Rule-Based Optimization，簡稱為RBO)和基於代價的優化方式(Cost-Based Optimization，簡稱為CBO)，在Oracle8及以後的版本,Oracle強列推薦用CBO的方式

RBO方式：優化器在分析SQL語句時,所遵循的是Oracle內部預定的一些規則。比如我們常見的，當一個where子句中的一列有索引時去走索引。

CBO方式：它是看語句的代價(Cost),這裡的代價主要指Cpu和記憶體。優化器在判斷是否用這種方式時,主要參照的是表及索引的統計資訊。統計資訊給出表的大小、有少行、每行的長度等資訊。這些統計資訊起初在庫內是沒有的，是做analyze後才出現的，很多的時侯過期統計資訊會令優化器做出一個錯誤的執行計劃,因些應及時更新這些資訊，這時直接走rule要快得多。

注意：走索引不一定就是優的，比如一個表只有兩行資料，一次IO就可以完成全表的檢索,而此時走索引時則需要兩次IO,這時全表掃描(full table scan)是最好

優化模式包括Rule、Choose、First rows、All rows四種方式：

Rule：基於規則的方式。

Choolse：預設的情況下Oracle用的便是這種方式。指的是當一個表或或索引有統計資訊，則走CBO的方式，如果表或索引沒統計資訊，表又不是特別的小，而且相應的列有索引時，那麼就走索引，走RBO的方式。

First Rows：它與Choose方式是類似的，所不同的是當一個表有統計資訊時，它將是以最快的方式返回查詢的最先的幾行，從總體上減少了響應時間。

All Rows:也就是我們所說的Cost的方式，當一個表有統計資訊時，它將以最快的方式返回表的所有的行，從總體上提高查詢的吞吐量。沒有統計資訊則走RBO的方式。

設定選用哪種優化模式：

A、Instance級別我們可以通過在initSID.ora檔案中設定OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS如果沒設定OPTIMIZER_MODE引數則預設用的是Choose方式。

B、Sessions級別通過ALTER SESSION SET OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS來設定。

C、語句級別用Hint（/*+ ... */）來設定

為什麼表的某個欄位明明有索引，但執行計劃卻不走索引？

1、優化模式是all_rows的方式

2、表作過analyze，有統計資訊，CBO選擇走全部掃描

3、表很小，上文提到過的，Oracle的優化器認為不值得走索引。

Hint概述

基於代價的優化器是很聰明的，在絕大多數情況下它會選擇正確的優化器，減輕了DBA的負擔。但有時它也聰明反被聰明誤，選擇了很差的執行計劃，使某個語句的執行變得奇慢無比。

此時就需要DBA進行人為的干預，告訴優化器使用我們指定的存取路徑或連線型別生成執行計劃，從而使語句高效的執行。例如，如果我們認為對於一個特定的語句，執行全表掃描要比執行索引掃描更有效，則我們就可以指示優化器使用全表掃描。在Oracle 中，是通過為語句新增 Hints(提示)來實現干預優化器優化的目的。

不建議在程式碼中使用hint，在程式碼使用hint使得CBO無法根據實際的資料狀態選擇正確的執行計劃。畢竟資料是不斷變化的， 10g以後的CBO也越來越完善，大多數情況下我們該讓Oracle自行決定採用什麼執行計劃。

Oracle Hints是一種機制，用來告訴優化器按照我們的告訴它的方式生成執行計劃。我們可以用Oracle Hints來實現：

1) 使用的優化器的型別

2) 基於代價的優化器的優化目標，是all_rows還是first_rows。

3) 表的訪問路徑，是全表掃描，還是索引掃描，還是直接利用rowid。

4) 表之間的連線型別

5) 表之間的連線順序

6) 語句的並行程度

除了”RULE”提示外，一旦使用的別的提示，語句就會自動的改為使用CBO優化器，此時如果你的資料字典中沒有統計資料，就會使用預設的統計資料。所以建議大家如果使用CBO或Hints提示，則最好對錶和索引進行定期的分析。

如何使用Hints:

Hints只應用在它們所在sql語句塊(statement block，由select、update、delete關鍵字標識)上，對其它SQL語句或語句的其它部分沒有影響。如：對於使用union操作的2個sql語句，如果只在一個sql語句上有Hints，則該Hints不會影響另一個sql語句。

我們可以使用註釋(comment)來為一個語句新增Hints，一個語句塊只能有一個註釋，而且註釋只能放在SELECT, UPDATE, or DELETE關鍵字的後面

使用Oracle Hints的語法：

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

or

{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...

註解：

1) DELETE、INSERT、SELECT和UPDATE是標識一個語句塊開始的關鍵字，包含提示的註釋只能出現在這些關鍵字的後面，否則提示無效。

2) “+”號表示該註釋是一個Hints，該加號必須立即跟在”/*”的後面，中間不能有空格。

3) hint是下面介紹的具體提示之一，如果包含多個提示，則每個提示之間需要用一個或多個空格隔開。

4) text 是其它說明hint的註釋性文字

5)使用表別名。如果在查詢中指定了表別名，那麼提示必須也使用表別名。例如：select /*+ index(e,dept_idx) */ * from emp e;

6)不要在提示中使用模式名稱：如果在提示中指定了模式的所有者，那麼提示將被忽略。例如：

select /*+ index(scott.emp,dept_idx) */ * from emp

注意：如果你沒有正確的指定Hints，Oracle將忽略該Hints，並且不會給出任何錯誤。

hint被忽略

如果CBO認為使用hint會導致錯誤的結果時，hint將被忽略，詳見下例

SQL> select /*+ index(t t_ind) */ count(*) from t;

Execution Plan

----------------------------------------------------------

Plan hash value: 2966233522

-------------------------------------------------------------------

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 57 (2)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| T | 50366 | 57 (2)| 00:00:01 |

-------------------------------------------------------------------

因為我們是對記錄求總數，且我們並沒有在建立索引時指定不能為空，索引如果CBO選擇在索引上進行count時，但索引欄位上的值為空時，結果將不準確，故CBO沒有選擇索引。

SQL> select /*+ index(t t_ind) */ count(id) from t;

Execution Plan

----------------------------------------------------------

Plan hash value: 646498162

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 285 (1)| 00:00:04 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| 2 | INDEX FULL SCAN| T_IND | 50366 | 245K| 285 (1)| 00:00:04 |

--------------------------------------------------------------------------

因為我們只對id進行count，這個動作相當於count索引上的所有id值，這個操作和對錶上的id欄位進行count是一樣的(組函式會忽略null值)

Hint的具體用法

和優化器相關的hint

1、/*+ ALL_ROWS */

表明對語句塊選擇基於開銷的優化方法,並獲得最佳吞吐量,使資源消耗最小化.

SELECT /*+ ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

2、/*+ FIRST_ROWS(n) */

表明對語句塊選擇基於開銷的優化方法,並獲得最佳響應時間,使資源消耗最小化.

SELECT /*+FIRST_ROWS(20) */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

3、/*+ RULE*/

表明對語句塊選擇基於規則的優化方法.

SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

和訪問路徑相關的hint

1、/*+ FULL(TABLE)*/

表明對錶選擇全域性掃描的方法.

SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';

2、/*+ INDEX(TABLE INDEX_NAME) */

表明對錶選擇索引的掃描方法.

SELECT /*+INDEX(BSEMPMS SEX_INDEX) */ * FROM BSEMPMS WHERE SEX='M';

5、/*+ INDEX_ASC(TABLE INDEX_NAME)*/

表明對錶選擇索引升序的掃描方法.

SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ * FROM BSEMPMS WHERE DPT_NO='SCOTT';

6、/*+ INDEX_COMBINE*/

為指定表選擇點陣圖訪問路經,如果INDEX_COMBINE中沒有提供作為引數的索引,將選擇出點陣圖索引的布林組合方式.

SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI) */ * FROM BSEMPMS

WHERE SAL<5000000 AND HIREDATE

7、/*+ INDEX_JOIN(TABLE INDEX_NAME1 INDEX_NAME2) */

當謂詞中引用的列都有索引的時候，可以通過指定採用索引關聯的方式，來訪問資料

select /*+ index_join(t t_ind t_bm) */ id from t where id=100 and object_name='EMPLOYEES'

8、/*+ INDEX_DESC(TABLE INDEX_NAME)*/

表明對錶選擇索引降序的掃描方法.

SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ * FROM BSEMPMS WHERE DPT_NO='SCOTT';

9、/*+ INDEX_FFS(TABLE INDEX_NAME) */

對指定的表執行快速全索引掃描,而不是全表掃描的辦法.

SELECT /* + INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';

10、/*+ INDEX_SS(T T_IND) */

從9i開始，oracle引入了這種索引訪問方式。當在一個聯合索引中，某些謂詞條件並不在聯合索引的第一列時，可以通過Index Skip Scan來訪問索引獲得資料。當聯合索引第一列的唯一值個數很少時，使用這種方式比全表掃描效率高。

SQL> create table t as select 1 id,object_name from dba_objects;

Table created.

SQL> insert into t select 2,object_name from dba_objects;

50366 rows created.

SQL> insert into t select 3,object_name from dba_objects;

50366 rows created.

SQL> insert into t select 4,object_name from dba_objects;

50366 rows created.

SQL> commit;

Commit complete.

SQL> create index t_ind on t(id,object_name);

Index created.

SQL> exec dbms_stats.gather_table_stats('HR','T',cascade=>true);

PL/SQL procedure successfully completed.

執行全表掃描

SQL> select /*+ full(t) */ * from t where object_name='EMPLOYEES';

6 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 5 | 135 | 215 (3)| 00:00:03 |

|* 1 | TABLE ACCESS FULL| T | 5 | 135 | 215 (3)| 00:00:03 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_NAME"='EMPLOYEES')

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

942 consistent gets

0 physical reads

0 redo size

538 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

6 rows processed

不採用hint

SQL> select * from t where object_name='EMPLOYEES';

6 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2869677071

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 5 | 135 | 5 (0)| 00:00:01 |

|* 1 | INDEX SKIP SCAN | T_IND | 5 | 135 | 5 (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("OBJECT_NAME"='EMPLOYEES')

filter("OBJECT_NAME"='EMPLOYEES')

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

17 consistent gets

1 physical reads

0 redo size

538 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

6 rows processed

當全表掃描掃描了942個塊，聯合索引只掃描了17個資料塊。可以看到聯合索引的第一個欄位的值重複率很高時，即使謂詞中沒有聯合索引的第一個欄位，依然會使用index_ss方式，效率遠遠高於全表掃描效率。但當第一個欄位的值重複率很低時，使用 index_ss的效率要低於全表掃描，讀者可以自行實驗

和表的關聯相關的hint

/*+ leading(table_1,table_2) */

在多表關聯查詢中，指定哪個表作為驅動表，即告訴優化器首先要訪問哪個表上的資料。

select /*+ leading(t,t1) */ t.* from t,t1 where t.id=t1.id;

/*+ order */

讓Oracle根據from後面表的順序來選擇驅動表，oracle建議使用leading，他更為靈活

select /*+ order */ t.* from t,t1 where t.id=t1.id;

/*+ use_nl(table_1,table_2) */

在多表關聯查詢中，指定使用nest loops方式進行多表關聯。

select /*+ use_nl(t,t1) */ t.* from t,t1 where t.id=t1.id;

/*+ use_hash(table_1,table_2) */

在多表關聯查詢中，指定使用hash join方式進行多表關聯。

select /*+ use_hash(t,t1) */ t.* from t,t1 where t.id=t1.id;

/*+ use_merge(table_1,table_2) */

在多表關聯查詢中，指定使用merge join方式進行多表關聯。

select /*+ use_merge(t,t1) */ t.* from t,t1 where t.id=t1.id;

/*+ no_use_nl(table_1,table_2) */

在多表關聯查詢中，指定不使用nest loops方式進行多表關聯。

select /*+ no_use_nl(t,t1) */ t.* from t,t1 where t.id=t1.id;

/*+ no_use_hash(table_1,table_2) */

在多表關聯查詢中，指定不使用hash join方式進行多表關聯。

select /*+ no_use_hash(t,t1) */ t.* from t,t1 where t.id=t1.id;

/*+ no_use_merge(table_1,table_2) */

在多表關聯查詢中，指定不使用merge join方式進行多表關聯。

select /*+ no_use_merge(t,t1) */ t.* from t,t1 where t.id=t1.id;

其他常用的hint

/*+ parallel(table_name n) */

在sql中指定執行的並行度，這個值將會覆蓋自身的並行度

select /*+ parallel(t 4) */ count(*) from t;

/*+ no_parallel(table_name) */

在sql中指定執行的不使用並行

select /*+ no_parallel(t) */ count(*) from t;

/*+ append */以直接載入的方式將資料載入入庫

insert into t /*+ append */ select * from t;

/*+ dynamic_sampling(table_name n) */

設定sql執行時動態採用的級別，這個級別為0~10

select /*+ dynamic_sampling(t 4) */ * from t where id > 1234

/*+ cache(table_name) */

進行全表掃描時將table置於LRU列表的最活躍端，類似於table的cache屬性

select /*+ full(employees) cache(employees) */ last_name from employees

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

轉載--常見hints的基礎用法

相關文章