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

lhrbest發表於2016-10-12
原創 2016-10-12 羅敏 

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已經越來越智慧、越來越先進了,我們作為使用者也要與時俱進,不斷適應新技術的發展,並採取合理的技術運用策略。


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



...............................................................................................................................

● 本文來自於微信公眾號轉載文章,若有侵權,請聯絡小麥苗及時刪除,非常感謝原創作者的無私奉獻

● 本文在itpubhttp://blog.itpub.net/26736162)、部落格園(http://www.cnblogs.com/lhrbest)和個人微信公眾號(xiaomaimiaolhr)上有同步更新

● QQ群:230161599  微信群:私聊

● 原文地址:http://mp.weixin.qq.com/s?__biz=MzI4NTA1MDEwNg==&mid=2650756982&idx=1&sn=dba22720fae1cf0cae0b20cad85b85ab&chksm=f3f9e2e3c48e6bf5d1bffee34db8c1bbd0a125599dbb03f4f6fa34868d65a2ec31e67eff09e1&mpshare=1&scene=1&srcid=1012FqDQGAGnUosMB35neLyV#rd

● 小麥苗分享的其它資料:http://blog.itpub.net/26736162/viewspace-1624453/

● 小麥苗雲盤地址:http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群: 230161599   微信群:私聊

● 聯絡我請加QQ好友(642808185),註明新增緣由

【版權所有,文章允許轉載,但須以連結方式註明源地址,否則追究法律責任】

...............................................................................................................................

手機長按下圖識別二維碼或微信客戶端掃描下邊的二維碼來關注小麥苗的微信公眾號:xiaomaimiaolhr,免費學習最實用的資料庫技術。





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

相關文章