【微信公眾號● DBAplus】Oracle原廠老兵：從負面案例看Hint的最佳使用方式

作者介紹

羅敏，從事Oracle技術研究、開發和服務工作20餘年，在Oracle中國公司的10多年，分別在顧問諮詢部、技術服務部擔任資深技術顧問。曾參與國內銀行、電信、政府等多個行業大型IT系統的建設和運維服務工作，為國內主要軟體開發商和整合商進行過多場Oracle高階技術應用培訓和交流活動。著有書籍《品悟效能優化》、《感悟Oracle核心技術》、《Oracle資料庫技術服務案例精選》。

Oracle真靈活

某銀行廣泛採用了Oracle和IBM DB2兩種資料庫，該銀行很多開發人員也遊走在兩種平臺之間，感受了不同的產品特性和風格。某天，我在為該銀行提供服務過程中，與幾位開發人員閒聊時好奇地問道：

“你們覺得DB2怎麼樣？”

“還是Oracle好用，更靈活。” 他們一致的回答。

“體現在哪些方面？”我繼續問。

“Oracle可以通過Hint控制SQL語句執行計劃，而DB2沒有為開發人員提供這種可控制的空間和手段，DB2自己想怎麼執行就怎麼執行。”

哦，原來如此，Oracle的確非常靈活，可以通過各種Hint來控制優化器產生不同的執行計劃。例如，你想讓Oracle走哪個索引，就可以用Hint: /*+ USE_INDEX(表名 索引名) */，你想讓Oracle走全表掃描，就可以用Hint: /*+ FULL(表名) */。

的確，這幾位銀行的開發人員水平非常高，非常熟悉自己的資料分佈情況和訪問方式，廣泛使用了多種Hint，有效保證了SQL語句執行計劃的最優化，也確保了系統的響應速度和吞吐量。例如他們通過Hint：/*+ ORDERED USE_NL(B C) INDEX(B IDX_TRANSSTBL_RETURN) */，就是指定了按From順序進行連線操作，並且連線方式為Nested_Loop，同時按IDX_TRANSSTBL_RETURN索引訪問B表。

曾幾何時，能廣泛、深入使用Hint的開發人員的確是高手，他們甚至比Oracle優化器自己產生的SQL語句執行計劃還要好，執行效率還要高。可是，成也蕭何、敗也蕭何，10多年之後，尤其很多系統升級到11g之後，大量Hint的使用，尤其是原有9i、10g版本SQL語句中的Hint反而成了導致效能問題的罪魁禍首！

這就是本文要深入探討的話題。我們不妨先回顧一下Oracle Hint的知識，再介紹一些Hint的負面案例，然後介紹Oracle公司對Hint的官方觀點，以及個人的實施經驗。

回顧Hint

所謂Hint，就是開發人員在SQL語句中編寫一段一定格式的註釋，目的就是強制Oracle優化器按自己的意願去產生執行計劃。例如，如下語句就是強制Oracle優化器按索引PRODUCTS_PROD_CAT_IX去訪問products表。

UPDATE /*+ INDEX(p PRODUCTS_PROD_CAT_IX)*/

products p

SET   p.prod_min_price =

        (SELECT

         (pr.prod_list_price*.95)

FROM products pr

WHERE p.prod_id = pr.prod_id)

WHERE p.prod_category = 'Men'

AND   p.prod_status = 'available, on stock'

/

Oracle到底有多少種Hint，主要作用是什麼呢？我們不妨進行如下歸類：

序號	大類	Hint	Hint含義
1	優化器方式	All_ROWS	指定按最大吞吐量為目標的CBO模式
		FIRST_ROWS(n)	制定Oracle優化器按最快返回n條記錄產生執行計劃
		RULE	強制Oracle採用RBO模式
2	訪問路徑	FULL	強制Oracle對制定表進行全表掃描訪問
		CLUSTER	強制Oracle對指定表按cluster scan方式進行訪問
		HASH	強制Oracle對指定表按hash scan方式進行訪問
		ROWID	強制Oracle對指定表按ROWID方式進行訪問
		INDEX	強制Oracle對指定表按指定索引進行訪問
		INDEX_ASC	強制Oracle對指定索引按升序進行訪問
		INDEX_DESC	強制Oracle對指定索引按降序進行訪問
		INDEX_COMBINE	強制Oracle對指定表按指定的Bitmap索引進行訪問
		INDEX_JOIN	強制Oracle對指定索引進行合併操作，並訪問指定的表。
		INDEX_FFS	強制Oracle對指定表按指定索引進行Fast  Full Scan方式訪問
		INDEX_SS	強制Oracle對指定表按指定索引進行skip scan方式訪問
		NO_INDEX	強制Oracle對指定表不按指定索引進行訪問
3	查詢轉換	NO_QUERY_TRANSFORMATION	跳過所有查詢轉換，包括OR操作轉換、檢視合併、子查詢和主查詢合併、星型轉換、物化檢視語句重寫等
		USE_CONCAT	強制Oracle將OR操作按UNION ALL操作執行
		NO_EXPAND	阻止Oracle將OR操作按UNION ALL操作執行
		REWRITE	按物化檢視對語句進行重寫
		NO_REWRITE	關閉REWRITE功能
		UNNEST	強制Oracle將子查詢和主查詢合併
		NO_UNNEST	關閉UNNEST功能
		MERGE	將複雜的檢視與呼叫該檢視的語句合併
		NO_MERGE	阻止將複雜的檢視與呼叫該檢視的語句合併
		STAR_TRANSFORMATION	強制Oracle對星型模型的訪問轉換為子查詢，並按相關Bitmap索引進行訪問
		FACT	與STAR_TRANSFORMATION   Hint配合，指定哪個表為事實表
		NO_FACT	與STAR_TRANSFORMATION   Hint配合，指定哪個表不為事實表
4	表連線順序	ORDERED	強制Oracle按From短語中表的順序，進行表連線操作
		LEADING	強制Oracle在表連線操作時，先訪問指定的表
5	表連線操作	USE_NL	強制Oracle對指定表按Nest_Loop方式進行表連線操作
		NO_USE_NL	強制Oracle對指定表不按Nest_Loop方式進行表連線操作
		USE_NL_WITH_INDEX	與USE_NL類似，但是必須按指定索引訪問驅動表
		USE_MERGE	強制Oracle對指定表按Sort-Merge方式進行表連線操作
		NO_USE_MERGE	強制Oracle對指定表不按Sort-Merge方式進行表連線操作
		USE_HASH	強制Oracle對指定表按HASH方式進行表連線操作
		NO_USE_HASH	強制Oracle對指定表不按HASH方式進行表連線操作
		DRIVING_SITE	強制Oracle在SQL語句發起的另外一個節點上執行
6	其它類	APPEND	強制Oracle按Direct-path Insert方式插入資料
		NOAPPEND	強制Oracle按傳統方式插入資料
		CURSOR_SHARING_EXACT	阻止Oracle將SQL語句中的常量替換為繫結變數
		CACHE	強制Oracle將指定表快取在Buffer Cache中
		PUSH_PRED	強制Oracle將主語句中表與檢視中相關表進行連線操作
		PUSH_SUBQ	強制Oracle先執行非合併的子查詢模組
		DYNAMIC_SAMPLING	強制Oracle進行動態統計資料取樣，取樣率引數為0到10，值越大，動態取樣資料越多。
		MONITOR	強制Oracle啟動實時SQL語句效能監控功能
		NO_MONITOR	關閉實時SQL語句效能監控功能
		RESULT_CACHE	強制Oracle將當前查詢結果集快取在RESULT CACHE中
		NO_RESULT_CACHE	強制Oracle不將當前查詢結果集快取在RESULT CACHE中

限於篇幅，還有更多類的Hint沒有再一一羅列，例如與Parallel處理相關的Hint等。

有關Hint使用的負面案例

本文重點並不是要講解上述紛繁的Hint技術使用技巧，恰恰相反，本文將介紹若干Hint使用的負面案例。

• 錯誤使用/*+ use_nl(a)*/

某移動CRM系統中如下一條語句最消耗資源：

其實smtemplate、smnotify_kf表都非常小，因此全表掃描是正確執行路徑，但兩個表被語句的HINT：/*+ use_nl(a)*/強制按nested loop進行連線，導致記憶體消耗非常大：9171128。如果去掉上述HINT，語句執行情況如下：

可見，Oracle自動根據兩個表的統計資料情況，選擇按HASH_JOIN方式進行兩個表的連線。實際執行效果記憶體消耗非常小，從9171128下降為3385。

• 錯誤使用/*+ use_merge */

前不久的某天，我在一旁觀摩我的同事為某銀行一條Merge語句進行優化，可惜我沒有實際操作，無法將語句優化過程完整記錄下來，現在僅以文字形式進行描述。

該Merge語句涉及一大一小兩個表的連線，現有執行計劃為兩個表的全表掃描，顯然不合理，於是我的同事在大表的連線欄位上建了一個索引，執行計劃果然走新建索引了，但Cost依然非常高，執行效率不佳。我在一旁仔細分析執行計劃，發現兩個表走的是Sort-Merge連線方式，再仔細看語句，原來有一個/*+ use_merge */的Hint，於是我果斷建議把這個Hint去掉，結果Cost大大下降，實際效果是執行計劃變為兩個表按Nested-Loop進行連線，其中小表為驅動表，大表按新建索引進行訪問，語句執行時間為7秒，我的同事馬上回頭問旁邊的開發人員，7秒能滿足需求嗎？開發人員喜出望外：“啊，原來這條語句是20多分鐘呢！”

可見，Oracle完全可以根據統計資料準確判斷出最優的執行計劃，即通過Nested-Loop進行表連線，並按新建的索引訪問大表。而開發人員的/*+ use_merge */強制Oracle按Sort-Merge進行表連線，實在是弄巧成拙。

這就是錯誤使用Hint的典型負面案例！

有關Hint的Oracle官方觀點

和最佳實踐經驗

• Hint是優化過程中的最後一招

這意味著Oracle公司對自己的CBO優化器非常自信，Oracle認為只要統計資料準確， CBO絕大部分情況下是沒有問題的。若統計資料已經準確，甚至已經採集了SQL Profile技術，執行計劃依然不合理，Oracle這時候才建議使用Hint。

我們再回顧一下Oracle自動化優化工具的使用：Oracle自動化優化工具一般會給出四個方面的分析建議，而第一條建議就是分析是否有統計資料，以及統計資料是否過期了。這就是Oracle對效能優化的理念，也再次驗證上述觀點：只要統計資料準確， CBO絕大部分情況下是沒有問題的，而Hint只是優化過程中的最後一招。

• 穩定和固化並不代表著最優

很多Oracle同仁們使用Hint的一個重要目的就是為了執行計劃的穩定和固化，防止SQL語句因執行計劃變異而導致效能衰減。可是，這個世界上沒有一成不變的事情，唯一不變的事情就是變。

SQL語句是訪問資料的，而資料庫中的資料是在不斷動態變化之中，因此SQL語句執行計劃應該是根據資料變化情況不斷演變的，關鍵是要不斷變好，而不是出現效能衰減。而Oracle的Hint、Stored Outline等技術只追求穩定和固化，是一種落後、淘汰、簡單、粗暴的技術，並不能適應客戶資料的動態變化，因此，Oracle在11g、12c中推出了SPM、Adaptive Cursor Sharing、Adaptive Query Optimization等更多動態、自適應的優化技術。

• Hint將導致昂貴的維護成本

大量使用Hint，將要求開發人員非常熟諳被訪問表的資料分佈情況，而一旦資料量發生陡變，或者表結構發生變化，或者資料庫版本升級，都可能導致Hint失去作用，甚至適得其反。為此，開發人員不得不根據這些變化情況去實施有效的Hint，這將導致昂貴的維護成本。

• 在檢視上和檢視內謹慎使用Hint

由於對檢視的訪問取決於呼叫檢視的語句環境，Oracle很可能因為上下文環境不一樣，對檢視的訪問路徑是不同的，因此在檢視上和檢視內使用Hint，強制Oracle產生某一種執行計劃，很可能並不是最優的執行路徑。更何況，Oracle對檢視的訪問可能是將檢視定義與主語句合併，也可能是將主語句的謂詞條件推送到檢視之中。這些不確定情況，若再加上Hint的使用，將導致更多不可預知的結果。

儘管Oracle也推出了Global Table Hint技術，即將語句中表的Hint推送到被訪問的檢視之中，但本人認為仍然要謹慎使用這樣的技術。

本人更有這樣的觀點：對檢視本身尤其是多層巢狀檢視的使用一定要慎重，因為這都會導致Oracle難以確保最優的執行計劃。

• 隱含引數_OPTIMIZER_IGNORE_HINTS引數的使用

Oracle推出了一個隱含引數_OPTIMIZER_IGNORE_HINTS，取值為TRUE/FALSE，預設值是FALSE，也就是說Oracle可以通過將該隱含引數設定為TRUE，使得Oracle優化器忽略語句中所有的Hint。

顯然，Oracle提供此引數的目的就是在不修改應用前提下，忽略所有Hint，讓Oracle優化器自己來選擇執行路徑。Oracle也是認為在資料庫版本升級之後，原有Hint可能不僅起不到好作用，反而會起到反作用了。Oracle也是認為在新版本下，沒有這些Hint，Oracle可能會執行得更好。

• 並非一刀切

Oracle公司和本文並非一刀切地否定所有Hint的使用，Oracle某些技術的運用還是依賴於Hint的，例如11g新的Result Cache技術的使用等。另外，根據最佳實踐經驗，通過Hint使用某些技術效果更好。例如通過/*+ parallel */ Hint比11g的自動並行處理技術更為有效。

本文最後要總結的是：當年大家被迫採用Hint的一個重要原因是Oracle優化器包括CBO還存在一定缺陷，而現在隨著Oracle新版本的不斷推出，CBO已經越來越智慧、越來越先進了，我們作為使用者也要與時俱進，不斷適應新技術的發展，並採取合理的技術運用策略。

這就是事物的螺旋式上升發展和綜合平衡。