Oracle之Hint使用總結

一、關於hint

1、為什麼要引入hint？

Hint是Oracle資料庫提供的一種機制用來告訴優化器按照hint告訴它的方式生成執行計劃，是很多DBA優化中常用的一個手段。

為什麼Oracle引入優化器呢？

基於代價的優化器，在絕大多數情況下會選擇正確的優化器，減輕DBA的負擔。但是有時候會選擇效率很差的執行計劃，使某個語句變得很慢，此時就需要DBA認為干預，告訴優化器使用指定的存取路徑或者連線型別生成執行計劃，從而使語句高效地執行。

2、不能過分依賴hint

當遇到不好的執行計劃時，應該優先考慮統計資訊等問題，而非直接加hint。若統計資訊無誤，再考慮物理結構是否合理，即有沒有合適的索引。只有在最後任然不能按照優化的sql執行計劃執行時，才考慮hint。

因為使用hint，需要系統修改程式碼，hint只能解決一條sql的問題，並且由於資料分佈的變化或其他原因(如索引改名等)，會導致sql再次出現效能問題。

3、hint的弊端

(1)、hint是比較“暴力”的一種解決方式。需要開發人員手工修改程式碼。

(2)、hint不會去適應新的變化。比如資料結構、資料規模發生了重大變化，使用Hint的語句是不會感知變化併產生更優的執行計劃。

(3)、hint隨著資料庫版本的變化，可能會有一些差異、甚至廢棄的情況。此時，還需要人工取修改。

4、hint與註釋的關係

提示是Oracle為了不破壞和其他資料庫引擎之間對SQL語句的相容性而提供的一種擴充套件功能。

Oracle決定把提示作為一種特殊的註釋來新增。它的特殊性表現在提示必須緊跟著DELETE、INSERT、UPDATE或MERGE關鍵字。

如insert /*+append*/ into

5、hint的功能

通過hint可以靈活地調整語句的執行過程。

通過Hint，我們可以調整：優化器型別、優化器優化目標、資料讀取方式(訪問路徑)、查詢轉換型別、表間關聯的順序、表間關聯的型別、並行特性、其他特性。

二、hint的具體用法

1、hint語法：

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

or

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

（1）、關鍵字說明

hint提示只能出現在4個關鍵字後面；

“+”號表示此註釋是一個提示，必須緊跟“/*”，且中間不能有空格；

如果包含多個提示，則每個提示之間需要用一個或多個空格隔開；

text是其他說明hint的註釋性文字。

（2）、hint中的錯誤

hint中的語法錯誤不會報錯，如果解析器不能解析，就會把它看做一個普通的註釋處理。

explain plan + dbms_xplan ：使用dbms_xplan輸出中的note選項。

ora-10132事件：在10g中，這個事件產生的輸出文件的末尾有一部分內容專門講提示。

通過它可以檢查兩個方面：
一是每個用到的提示都會被列出來。如果漏掉了哪個，就說明這個提示沒有被識別；
二是檢查是否有一些資訊指明瞭出現提示錯誤(如果出錯，err值將大於0)。

（3）、hint中的物件

SELECT /*+ INDEX(TABLE_NAME INDEX_NAME)*/ ...

table_name是必須要寫的，且如果在查詢中使用了表的別名，在hint也要用表的別名來代替表名。

注:
a、index_name可以不必寫，Oracle會根據統計值選一個索引。

b、如果索引名或表名寫錯了，那這個hint就會被忽略。

c、如果指定物件是檢視，需要按此方法指定。/*+hint view.table ...*/，其中table是view中的表。

d、一個很常見的錯誤是，在使用提示的時候最易犯的錯誤是與表的別名有關。正確的規則是，當在提示中使用表時，只要表有別名就應該使用別名而不是表名。

2、hint的作用域

（1）、查詢塊

初始化引數提示對整個sql語句起作用，其他的提示僅對查詢塊起作用。

僅僅對單個查詢塊起作用的提示，必須在它控制的查詢塊內指定。

with temp_tb1 as {
    select /*+ full(temp)*/id,count(1) cnt from temp group by id
}

select /*+ full(temp_tb2)*/ id,name,cnt from temp_tb2,temp_tb1 where temp_tb2.id=temp_tb1.id;

（2）、例外-全域性提示

可以使用點號引用包含在其他塊(假設這些塊已命名)中的物件。類似Java中的靜態成員。

with temp_tb1 as {
    select id,count(1) cnt from temp group by id
    }
select /*+ full(temp) full(temp_tb1.temp)*/ id,name,cnt from temp_tb2,temp_tb1 where temp_tb2.id=temp_tb1.id;

（3）、命名查詢塊

既然where子句中的子查詢是沒有命名的，它們的物件就不能被全域性提示引用。為了解決這個問題，10g中使用了另一種方法來解決-命名查詢塊。查詢優化器可以給每個查

詢生成一個查詢塊名，而且還可以使用提示qb_name手工為每個查詢塊命名。大多數提示都可以通過引數來指定在那個查詢塊中有效。

with temp1 as {
    select /*+ qb_name(sq)*/ id,count(1)cnt from temp group by id
}

select /*+ qb_name(main) full(@main temp2) full(@sq temp) */ temp.name,temp1.cnt 
    from temp,temp1 where temp.id=temp1.id;

在hint中通過@來引用一個查詢塊。

3、hint資料字典

Oracle在11g的版本中提供了一個資料字典—V$SQL_HINT。通過這個資料字典可以看到提示的出現版本、概要資料版本、SQL特性以及相反提示等。

select * from v$sql_hint where version like '%11%';

三、hint的分類

1、優化器相關的

當對優化器為某個語句所制定的基本執行計劃不滿意時，最好的方法是通過提示來改變執行計劃，並觀察改變後是否達到 期望的程度。

OPT_PARAM：作用是使某條語句中指定某個系統引數值；

ALL_EOWS：實現查詢語句整體最優化而引導優化器制定最少成本的執行計劃。優化器會選擇一條可最快檢索所有查詢行的路徑，代價就是檢索一行時，速度會很慢。

FIRST_ROWS：為獲得最佳響應時間而引導優化器制定最少成本的執行計劃。這個提示會使優化器選擇可最快檢索出查詢的第一行(或指定行)資料的路徑，而代價就是檢索很

多行時速度就會很慢。利用FIRST_ROWS來優化的行數，預設值為1，這個值介於10到1000之間，這個使用FIRST_ROWS(n)的新方法是完全基於代價的方法。它對n很敏感，

如果n值很小，CBO就會生成包含巢狀迴圈以及索引查詢的計劃；如果n很大，CBO會生成由雜湊連線和全表掃描組成的計劃(類似ALL_ROWS)。

CHOOSE：依據SQL中所使用到的表的統計資訊存在與否，來決定使用RBO還是CBO。在CHOOSE模式下，如果能夠參考表的統計資訊， 則將按照ALL_ROWS方式執行。除非在查詢中的所有表都沒有經過分析，否則choose提示會對整個查詢使用基於代價的優化。如果在多表連線中有一個表經過分析過，那麼就會對整個查詢進行基於代價的優化。

RULE：使用基於規則的優化器來實現最優化執行，即引導優化器根據優先順序規則來決定查詢條件中所使用到的索引或運算子的執行 順序來制定執行計劃。這個提示強制

oracle優先使用預定義的一組規則，而不是對資料進行統計；同時該提示還會使這個語句避免使用其他提示，除了DRIVING_SITE和ORDERED(不管是否進行基於規則的優化，這兩個提示都可使用)。

2、和訪問路徑相關的

FULL：告訴優化器通過全表掃描方式訪問資料。這個提示只對所指定的表進行全表掃描，而不是查詢中的所有表。FULL提示可以改善效能。這主要是因為它改變了查詢中的驅動表，而不是因為全表掃描。在使用其他某些提示時，也必須使用FULL提示。只有訪問整個表時，才可利用CACHE提示將表進行快取。並行組中的某些提示也必須使用全表掃描。

CLUSTER：引導優化器通過掃描聚簇索引來從索引表中讀取資料。

HASH：引導優化器按照雜湊掃描的方式從表中讀取資料。

INDEX：告訴優化器對指定表通過索引的方式訪問資料。當訪問資料會導致結果集不完整時，優化器將忽略這個Hint。

NO_INDEX：告訴優化器對指定表不允許使用索引。這個提示會禁止優化器使用指定索引。可以在刪除不必要的索引之前在許多查詢中禁止索引。如果使用了NO_INDEX，但是沒有指定任何索引，則會執行全表掃描。如果對某個索引同時使用了NO_INDEX和會之產生衝突的提示(如INDEX)，這時兩個提示都會被忽略掉。

INDEX_ASC：利用索引從表中讀取資料時，引導優化器對提示中所指定索引的索引列值按照升序使用範圍掃描。

INDEX_COMBINE：告訴優化器強制選擇點陣圖索引。這個提示會使優化器合併表上的多個點陣圖索引，而不是選擇其中最好的索引(這是INDEX提示的用途)。還可以使用index_combine指定單個索引(對於指定點陣圖索引，該提示優先於INDEX提示)。對於B樹索引，可以使用AND_EQUAL提示而不是這個提示。

INDEX_JOIN：索引關聯，當謂詞中引用的列上都有索引的時候，可以通過索引關聯的方式來訪問資料。這個提示可以將同一個表的各個不同索引進行合併，這樣就只需要訪問這些索引就可以了，節省了回表查詢的時間。但只能在基於代價的優化器中使用該提示。這個提示不僅允許只訪問表上的索引， 這樣可以掃描更少的程式碼塊，並且它比使用索引並通過rowid掃描整個錶快5倍。

INDEX_DESC：利用索引從表中讀取資料時，引導優化器對提示中所指定索引的索引列值按照降序使用範圍掃描。

INDEX_FFS：告訴優化器以INDEX FFS(index fast full scan)的方式訪問資料。INDEX_FFS提示會執行一次索引的快速全域性掃描。這個提示只訪問索引，而不是對應的表。只有查詢需要檢索的資訊都在索引上時，才使用這個提示。特別在表有很多列時，使用該提示可以極大地改善效能。

INDEX_SS：強制使用index skip scan的方式訪問索引。當在一個聯合索引中，某些謂詞條件並不在聯合索引的第一列時(或者謂詞並不在聯合索引的第一列時)， 可以通過index skip scan來訪問索引獲得資料。當聯合索引第一列的唯一值很少時，使用這種方式比全表掃描的方式效率要高。

3、和查詢轉換相關的

USE_CONCAT：將含有多個OR或者IN運算子所連線起來的查詢語句分解為多個單一查詢語句，併為每個單一查詢語句選擇最優化查詢路徑，然後再將這些最優化查詢路徑結合在一起，以實現整體查詢語句的最優化目的。只有在驅動查詢條件中包含OR的時候，才可以使用該提示。

NO_EXPAND：引導優化器不要為使用OR運算子號(或IN運算子)的條件制定相互結合的執行計劃。正好和USE_CONCAT相反。

REWRITE：當表連線的物件是資料量比較大的表或者需要獲得使用統計函式處理過的結果時，為了提高執行速度可預先建立物化檢視。當使用者要求查詢某個查詢語句時，優化器會在從表中和從物化檢視中讀取資料的兩種方法中選擇一個更有效的方法來讀取資料。該執行方法稱之為查詢重寫。使用REWRITE提示引導優化器按照該方式執行。

MERGE：為了能以最優方式從檢視或者巢狀檢視中讀取資料，通過變換查詢語句來直接讀取檢視使用的基表資料，該過程被稱之為檢視合併。不同的情況其具體使用型別也有所不同。該提示主要在檢視未發生合併時被使用。尤其是對比較複雜的檢視或者巢狀檢視(比如使用了GROUP BY或DISTINC的檢視)使用該提示，有時會取得非常好的效果。

UNNEST：提示優化器將子查詢轉換為連線的方式。也就是引導優化器合併子查詢和主查詢並且將其向連線型別轉換。

NO_UNNEST：引導優化器讓子查詢能夠獨立地執行完畢之後再跟外圍的查詢做FILTER。

PUSH_PRED：使用該提示可以將檢視或巢狀檢視以外的查詢條件推入到檢視之內。

NO_PUSH_PRED：使用該提示確保檢視或巢狀檢視以外的查詢條件不被推入到檢視內部。

PUSH_SUBQ：使用該提示引導優化器為不能合併的子查詢制定執行計劃。不能合併的子查詢被優先執行之後，該子查詢的執行結果將扮演縮減主查詢資料查詢範圍的提供者角色。通常在無法執行子查詢合併的情況下，子查詢扮演的都是檢驗者角色，所以子查詢一般被放在最後執行。在無法被合併的子查詢擁有較少的結果行，或者該子查詢可以縮減主查詢查詢範圍的情況下，可以使用該提示引導優化器最大程度地將該子查詢放在前面執行，以提高執行速度。但如果子查詢執行的是遠端表或者排序合併連線的一部分連線結果，則該提示將不起任何作用。

NO_PUSH_SUBQ：使用該提示將引導優化器將不能實現合併的子查詢放在最後執行。在子查詢無法縮減主查詢的查詢範圍，或者執行子查詢開銷較大的情況下，將這樣的子查詢放在最後執行可以在某種程度上提高整體的執行效率。也就是說，儘可能地使用其他查詢條件最大程度地縮減查詢範圍之後，再執行子查詢。

4、和表連線順序相關的

這些提示可以調整表連線的順序。調整表連線的順序並不是只能使用這些提示，在巢狀迴圈連線方式中也可以讓提示來引導優化器使用由驅動查詢條件所建立的索引。然而，該方法只有在使用的索引和表連線順序同時被調整的情況下才比較有效。一般而言，這些提示主要在執行多表連線和表之間的連線順序比較混亂的情況下才使用，也在排序合併連線或雜湊連線方式下，為引導優化器優先執行資料量比較少得表時使用。

LEADING：在一個多表關聯的查詢中，這個Hint指定由哪個表作為驅動表，即告訴優化器首先要訪問那個表上的資料。引導優化器使用LEADING指定的表作為表連線順序中的第一個表。該提示既與FROM中所描述的表的順序無關，也與作為調整表連線順序的ORDERED提示不同，並且在使用該提示時並不需要調整FROM中所描述的表的順序。當該提示與ORDERED提示同時使用時，該提示被忽略。

這個提示類似ORDERED提示，它允許指定驅動查詢的表，然後由優化器來判斷下一個要訪問的表。如果使用這個提示指定多張表，那麼就可以忽略這個提示。

ORDERED：引導優化器按照FROM中所描述的表的順序執行連線。如果和LEADING提示被一起使用，則LEADING提示將被忽略。由於ORDERED只能調整表連線的順序並不能改變表連線的方式，所以為了改變表的連線方式，經常將USE_NL、USE_MERGE提示與ORDERED提示放在一起使用。

5、和表連線操作相關的

USE_NL：使用該提示引導優化器按照巢狀迴圈連線方式執行表連線。它只是指出表連線的方式，對於表連線順序不會有任何影響。

USE_MERGE：引導優化器按照排序合併連線方式執行連線。在有必要的情況下，推薦將該提示與ORDERED提示一起使用。提示通常用於獲得查詢的最佳吞吐量。假設將兩個表連線在一起，從每個表返回的行集將被排序，然後再被合併(也就是合併排序)，從而組成最終的結果集。由於每個行先被排序之後才進行合併，所以在給定查詢中檢索所有行時，速度將會最快。如果需要以最快速度返回第一行，就應該使用USE_NL提示。

USE_HASH：該提示引導優化器按照雜湊連線方式執行連線。在執行雜湊連線時，如果由於某一邊的表比較小，從而可以在記憶體中實現雜湊連線，那麼就能夠獲得非常好的執行速度。由於在大部分情況下優化器會通過對統計資訊的分析來決定Build Input和Prove Input，所以建議不要使用ORDERED提示隨意改變表的連線順序。但是當優化器沒能做出正確判斷時，或者像從巢狀檢視中所獲得的結果集合那樣不具備統計資訊時，可以使用該提示。

6、和並行相關的

PARALLEL：指定SQL執行的並行度，這個值將會覆蓋表自身設定的並行度。如果這個值為default，CBO使用系統引數。從表中讀取大量資料和執行DML操作時使用該提示來指定SQL的並行操作。一般情況下需要在該提示中指定將要使用的並行執行緒個數。如果在該提示中沒有指定並行度的個數，則優化器將使用PARALLEL_THREADS_PER_CPU 引數所指定的值進行自動計算。如果在定義表時指定了PARALLEL，那麼在能夠使用並行操作的情況下，即使沒有使用該提示，優化器也會按照指定的並行級別選擇並行操作。 但是如果想在DELETE、INSERT、UPDATE、MERGE等DML操作中使用並行操作，則必須要在會話中設定ALTER SESSION ENABLE PARALLEL DML。在某個會話中所設定 的並行級別也可以被引用在內部的GROUP BY或者排序操作中。在並行操作中如果出現了某個限制要素，則該提示將被忽略。

NOPARALLEL/NO_PARALLEL：在SQL語句禁止使用並行。在有些版本中用NO_PARALLEL提示來代替NOPARALLEL提示。

PQ_DISTRIBUTE：為了提高並行連線的執行速度，使用該提示來定義使用何種方法在主從程式之間(例如生產者程式和消費者程式)分配各連線表的資料行。

PARALLEL_INDEX：為了按照並行操作的方式對分割槽索引進行索引範圍掃描而使用該提示，並且可以指定程式的個數。

7、其他相關的

APPEND：讓資料庫以直接載入的方式(direct load)將資料載入入庫。這個提示不會檢查當前是否有插入所需要的塊空間，相反它會直接將資料新增到新塊中。這樣會浪費空間，但可以提高插入的效能。需要注意的是，資料將被儲存在HWM之上的位置。

APPEND_VALUES：在11.2中，Oracle新增了APPEND_VALUES提示，使得INSERT INTO VALUES語句也可以使用直接路徑插入。

CACHE：在全表掃描之後，資料塊將留在LRU列表的最活躍端。如果設定表的CACHE屬性，它的作用和HINT一樣。這個提示會將全表掃描全部快取到記憶體中。如果表很大，會佔用大量記憶體。因此適用於使用者經常訪問的較小的表。

NOCACHE：引導優化器將通過全表掃描方式獲取的資料塊快取在LRU列表的最後位置，這樣可以讓資料庫例項快取中的這些資料塊被優先清除。這是優化器在Buffer Cache中管理資料塊的預設方法(僅針對全表掃描)。

QB_NAME：使用該提示為查詢語句塊命名，在其他查詢語句塊可以直接使用該查詢語句塊的名稱。

DRIVING_SITE：這個提示在分散式資料庫操作中有用。指定表是處理連線所在的位置。可以限制通過網路處理的資訊量。此外，還可以建立遠端表的本地檢視來限制從遠端站點檢索的行。本地檢視應該有where子句，從而檢視可以在將行傳送回本地資料庫之前限制從遠端資料庫返回的行。

DYNAMIC_SAMPLING：提示SQL執行時動態取樣的級別。這個級別為0~10，它將覆蓋系統預設的動態取樣級別。等級越高，所獲得統計資訊的準確率越高。該提示的功能就是為了確保將動態取樣原理應用在單個SQL中。

AND_EQUAL：這個提示會使優化器合併表上的多個索引，而不是選擇其中最好的索引(這是INDEX提示的用途)。這個提示與前面的INDEX_JOIN提示有區別，以此指定的合併索引隨後需訪問表，而INDEX_JOIN提示則只需訪問索引。如果發現需經常用到這個提示，可能需要刪除這些單個索引而改用一個組合索引。需要查詢條件裡面包括所有索引列，然後取得每個索引中得到的rowid列表。然後對這些物件做merge join，過濾出相同的rowid後再去表中獲取資料或者直接從索引中獲得資料。在10g中，and_equal已經廢棄了，只能通過hint才能生效。

CARDINALITY：向優化器提供對某個查詢語句的整體或部分的預測基數值，並通過參考該基數值來為查詢語句制定執行計劃。如果在該提示中沒有指定表的名稱，則該基數值將被視為從該查詢語句所獲得的最終結果行數。