解決CBO的SQL最佳化問題(圖文詳解)

怪我咯
發布: 2017-04-05 11:26:33
原創
6329 人瀏覽過

本次分享大綱:

  1. CBO優化器存在哪些坑

  2. CBO優化器坑的解決之道

  3. 加強SQL審核,將效能問題扼殺於襁褓之中

  4. 分享現場FAQ

解決CBO的SQL最佳化問題(圖文詳解)

##CBO( Cost Based Optimizer)優化器是目前Oracle廣泛使用的優化器,其使用統計資訊、查詢轉換等計算各種可能的存取路徑成本,並產生多種備選執行計劃,最終Oracle會選擇成本最低的作為最優執行計劃。與「遠古」時代的RBO(Rule Based Optimizer)相比,顯然更符合資料庫實際情況,能夠適應更多的應用場景。但是,由於其自身非常複雜,CBO並未解決的實際問題以及存在的BUG非常多,在日常優化過程中,你可能會遇到一些,不管怎麼收集統計信息,都無法走正確執行計劃的情形,這時候,你可能踩坑CBO了。

這次分享,主要以日常常見優化器問題作為引子,一起探討CBO的那些坑的解決之道。

一、CBO優化器存在哪些坑

先來看一下,CBO優化器的元件:

##從上圖可以看出,一條SQL進入ORACLE中,實際上經過解析會將各部分進行分離,每個分離的部分獨立成為一個查詢塊(query blocks),比如子查詢會成為一個查詢塊,外部查詢又是一個查詢區塊,那麼ORACLE優化器要做的工作就是各查詢區塊內部走什麼樣的存取路徑更好(走

索引

、全表、分區?),其次就是各查詢區塊之間應該走什麼樣的JOIN方式以及JOIN順序,最後計算出那種執行計畫更好。

優化器的核心是查詢轉換器、成本估算器、執行計劃

產生器

Transformer(查詢轉換器):

從圖上可以看出,優化器的第一核心裝置就是查詢轉換器,查詢轉換器的主要功能就是研究各種查詢區塊之間的關係,並從語法上甚至語義上給予SQL等價重寫,重寫後的SQL更容易被核心裝置成本估算器和執行計劃產生器處理,從而利用統計資訊產生最優執行計劃。

查詢轉換器在最佳化器中有兩種方式:啟發式查詢轉換(基於規則)和基於COST的查詢轉換。 啟發式查詢轉換的一般是比較簡單的語句,基於成本的一般比較複雜,也就是說,符合基於規則的ORACLE不管什麼情況下都會進行查詢轉換,不符合的ORACLE可能考慮基於成本的查詢轉換。啟發式查詢轉換歷史悠久,問題較少,一般查詢轉換過的效率比不經過查詢轉換的要高,而基於成本的查詢轉換,因其與CBO優化器緊密關聯,在10G引入,內部非常複雜,所以BUG也比較多,在日常優化過程中,各種疑難SQL,往往就出現在查詢轉換失敗中,因為查詢轉換一旦失敗,Oracle就無法將原始SQL轉換成結構更好的SQL(更容易被最佳化器處理),顯然可選擇的執行路徑就要少很多,例如子查詢不能UNNEST,那麼,往往就是災難的開始。其實,查詢轉換中Oracle做的最多的就是將各種查詢轉換成JOIN方式,這樣就可以利用各種高效的JOIN方法了,例如HASH JOIN。

查詢轉換共有30種以上的方式,以下列出一些常見啟發式和基於COST的查詢轉換。

啟發式查詢轉換(一系列的RULE):

很多啟發式查詢轉換在RBO情況下就已經存在。常見的有:######Simple View merge (簡單視圖合併)、SU (Subquery unnest 子查詢展開)、OJPPD (old style Join predicate push-down 舊的連接謂詞推入方式)、FPD (Filter push -down 過濾謂詞推入)、OR Expansion (OR擴展)、OBYE(Order by Elimination 排序消除)、JE​​ (Join Elimination 連接消除或連接中的表消除)、Transitive Predicate (謂詞傳遞)等技術。 ######基於COST的查詢轉換(透過COST計算):######針對複雜的語句進行基於COST的查詢轉換,常見的有:######CVM (Complex view Merging 複雜視野合併)、JPPD (Join predicate push-down 關聯謂詞推入)、DP (Distinct  placement)、GBP(Group by placement)等技巧。 ######透過一系列查詢轉換技術,將原始SQL轉換為最佳化器更容易理解和分析的SQL,以便能夠使用更多的謂詞、連接條件等,達到獲得最佳計畫的目的。查詢轉換的過程,可以透過10053取得詳細資訊。查詢轉換是否能夠成功和版本、最佳化器限制、隱含參數、補丁等有關。 ######隨便在MOS上搜尋查詢轉換,就會出現一堆BUG:###

解決CBO的SQL最佳化問題(圖文詳解)

竟然還是Wrong result(錯誤的結果),遇到這種BUG不是效能問題了,而是嚴重的資料正確性問題,當然,在MOS裡隨便可以找到一堆這樣的BUG,但是,在實際應用中,我相信,你可能碰到的較少,如果有一天,你看到一條SQL查詢的結果可能不對,那你也得大膽質疑,對於Oracle這種龐然大物來說,遇到問題,質疑是非常正確的思考方式,這種Wrong result問題,在資料庫大版本升級過程中可能見到,主要有兩類問題:

  1. 原來結果正確,現在結果錯誤。 --遇到新版BUG

  2. 現在結果正確,原來結果錯誤。 --新版本修復了舊版BUG

第一種情況很正常,第二種情況也可能存在,我就看到一客戶質疑升級後的結果不正確,結果經過查證之後,竟然是舊版執行計畫就是錯的,新版執行計畫是正確的,也就是錯誤了很多年,都沒有發現,結果升級後是正確的,卻以為是錯了。

遇到錯誤結果,如果不是非核心功能,真的可能被深埋很多年。

Estimator( 估算器):

很顯然,估算器會利用統計資料(表格、索引、列、分割區等)來估算對應執行計畫作業中的選擇性,從而計算出對應操作的cardinality,產生對應操作的COST,並最終計算整個計劃的COST。對於估算器來說,很重要的就是其估算模型的準確性以及統計資訊儲存的準確性,估算的模型越科學,統計資訊能反應實際的資料分佈情況,能夠覆蓋更多的特殊數據,那麼產生的COST則更加準確。

然而,這是不可能的情況,估算器模型以及統計資訊中存在諸多問題,例如針對字串計算選擇性,ORACLE內部會將字串轉換為RAW類型,在將RAW類型轉換成數字,然後左起ROUND 15位,這樣會出現可能字串相差很大的,由於轉換成數字後超過15位,那麼內部轉換後可能結果相近,最終導致計算的選擇性不準確。

Plan Generator( 計畫產生器):

計畫產生器也就是分析各種存取路徑、JOIN方法、JOIN順序,從而生產不同執行計畫。那麼如果這個部分出現問題,也就是對應的部分可能演算法不夠完善或有限制。例如JOIN的表格很多,那麼各種存取順序的選擇成幾何級數增長,ORACLE內部有限制值,也就是事實不可能全部計算一遍。

例如HASH JOIN演算法是普遍做大數據處理的首選演算法,但由於HASH JOIN天生存在一種限制:HASH碰撞,一旦遇到HASH碰撞,必然導致效率大減。

CBO優化器有許多限制,詳細可以參考MOS:Limitations of the Oracle Cost Based Optimizer (文件 ID 212809.1)。

二、CBO優化器坑的解決之道

本部分主要分享下日常常見優化器問題案例,有的問題不僅限於CBO優化器,由於CBO是目前廣泛使用的優化器,因此,一律納入CBO問題。

1 FILTER效能殺手問題

FILTER操作是執行計畫中常見的操作,這種操作有兩種情況:

  1. 只有一個子節點,那就是簡單過濾操作。

  2. 有多個子節點,那麼就是類似NESTED LOOPS操作,只不過與NESTED LOOPS差別在於,FILTER內部會建立HASH表,對於重複匹配的,不會再進行循環查找,而是利用已有結果,提高效率。但一旦重複匹配的較少,循環次數多,那麼,FILTER操作將是嚴重影響效能的操作,可能你的SQL幾天都執行不完了。

下面看看各種情況下的FILTER操作:

單子節點:

解決CBO的SQL最佳化問題(圖文詳解)

##很顯然ID =1的FILTER操作只有一個子節點ID=2,這種情況下的FILTER操作也就是單純的過濾操作。

多子節點:

FILTER多子節點往往就是效能殺手,主要出現在子查詢無法UNNEST查詢轉換,常遇到的情況就是NOT IN子查詢、子查詢和OR連用、複雜子查詢等情況。

(1)NOT IN子查詢中的FILTER

先來看下NOT IN狀況:

解決CBO的SQL最佳化問題(圖文詳解)

針對上面的NOT IN子查詢,如果子查詢object_id有NULL存在,則整個查詢都不會有結果,在11g之前,如果主表和子表的object_id未同時有NOT NULL約束,或都未加IS NOT NULL限制,則ORACLE會走FILTER。 11g有新的ANTI NA(NULL AWARE)優化,可以對子查詢進行UNNEST,從而提高效率。

對於未UNNEST的子查詢,走了FILTER,有至少2個子節點,執行計劃還有個特點就是Predicate謂詞部分有:B1這種類似綁定變數的東西,內部操作走類似NESTED LOOPS操作。

11g有NULL AWARE專門針對NOT IN問題進行最佳化,如下所示:

解決CBO的SQL最佳化問題(圖文詳解)

透過NULL AWARE操作,對無法UNNEST的NOT IN子查詢可以轉換成JOIN形式,這樣效率就大幅提升了。如果在11g之前,遇到NOT IN無法UNNEST,那該怎麼做呢?

  • 將NOT IN部分的符合條件,針對本例就是ANTI_TEST1.object_id和ANTI_TEST2.object_id皆設為NOT NULL約束。

  • 不改NOT NULL約束,則需要兩個object_id都增加IS NOT NULL條件。

  • 改為NOT EXISTS。

  • 改為ANTI JOIN形式。

以上四種方式,大部分情況下均能達到讓優化器走JOIN的目的。

解決CBO的SQL最佳化問題(圖文詳解)

以上寫法執行計劃都是一樣的,如下所示:

解決CBO的SQL最佳化問題(圖文詳解)

說白了,unnest subquery就是轉換成JOIN形式,如果能轉換成JOIN就可以利用高效JOIN特性來提高操作效率,不能轉換就走FILTER,可能影響效率,11g的NULL AWARE從執行計劃裡可以看出,還是有點區別,沒有走INDEX FULL SCAN掃描,因為沒有條件讓ORACLE知道object_id可能有NULL,所以也就走不了索引了。

OK,現在來說一個資料庫升級過程中碰到的案例,背景是11.2.0.2升級到11.2.0.4後下面SQL出現效能問題:

解決CBO的SQL最佳化問題(圖文詳解)

執行計畫如下:

解決CBO的SQL最佳化問題(圖文詳解)

這裡的ID=4和ID=8兩個FILTER都有2個子節點,很明顯是NOT IN子查詢無法UNNEST導致的。上面說了在11g ORACLE CBO可以將NOT IN轉換成NULL AWARE ANTI JOIN,並且在11.2.0.2上是可以轉換的,到11.2.0.4上就不行了。兩個FILTER操作的危害到底有多大呢,可以透過查詢實際執行計劃來看:

解決CBO的SQL最佳化問題(圖文詳解)

使用ALTER SESSION SET STATISTICS_LEVEL=ALL;截取2分25s的記錄查看實際情況,ID=9步驟的CARD=141行就需要2分25s,實際此步驟有:27w行

解決CBO的SQL最佳化問題(圖文詳解)

也就是這條SQL要執行10天以上了,簡直太恐怖了。

針對此問題的分析如下:

  • 查詢和NULL AWARE ANTI JOIN相關的隱含參數是否有效

  • 收集統計資料是否有效

  • 是否是新版本BUG或是升級中修改了參數導致的

針對第一種情況:

解決CBO的SQL最佳化問題(圖文詳解)

參數是TRUE,顯然沒有問題。

針對第二種情況:

收集統計資料發現無效。

那麼此時,只能寄望於第三種情況:可能是BUG或升級過程中修改了其它參數影響了無法走NULL AWARE ANTI JOIN。 ORACLE BUG和參數那麼多,那我們要怎麼快速找到問題根源導致是哪個BUG或參數所造成的呢?這裡要跟大家分享一個神器SQLT,全名為(SQLTXPLAIN),這是ORACLE內部效能部門開發的工具,可以在MOS上下載,功能非常強勁。

解決CBO的SQL最佳化問題(圖文詳解)

 

回歸正題,現在要找出是不是新版本BUG或是修改了某個參數導致問題產生, 那麼就要用到SQLT的高階方法:XPLORE。 XPLORE會針對ORACLE中的各種參數不停打開、關閉,來輸出執行計劃,最終我們可以透過產生的報告,找到匹配的執行計劃來判斷是BUG問題還是參數設定問題。

解決CBO的SQL最佳化問題(圖文詳解)

使用很簡單,參考readme.txt將需要測試的SQL單獨編輯一個文件,一般,我們測試都使用XPLAIN方法,調用EXPLAIN PLAN FOR進行測試,這樣保證測試效率。

SQLT 找出問題根源:

解決CBO的SQL最佳化問題(圖文詳解)

解決CBO的SQL最佳化問題(圖文詳解)

#最終透過SQLT XPLORE找出問題根源在於新版本關閉了_optimier_squ_bottomup參數(和子查詢相關)。從這點上也可以看出來,很多查詢轉換能夠成功,不光是​​一個參數起作用,可能多個參數共同​​作用。因此,關閉預設參數,除非有強大的理由,否則,不可輕易修改其預設值。至此,此問題在SQLT的幫助下,快速得以解決,如果不使用SQLT,那麼解決問題的過程顯然更為曲折,一般情況下,估計是讓開發先修改SQL了。

思考一下,原來的SQL是不是還可以更優化呢?

解決CBO的SQL最佳化問題(圖文詳解)

很顯然,如果要進一步優化,要徹底對SQL進行重寫,透過觀察,2個子查詢部分有相同點,經過分析語義:查找表DT_MBY_TEST_LOG在指定INSERT_TIME範圍內的,依照每個TBILL_ID取最小的INSERT_TIME,且ID不在子查詢中,然後結果依照INSERT_TIME排序,最後取TOP 199。

原SQL使用自連接、兩個子查詢,冗餘繁雜。自然想到用分析函數改寫,避免自連接,進而提高效率。改寫後的SQL如下:

解決CBO的SQL最佳化問題(圖文詳解)

執行計畫:

解決CBO的SQL最佳化問題(圖文詳解)

至此,這條SQL從原來的走FILTER需要耗時10天,到​​找出問題根源可以走NULL AWARE ANTI JOIN需要耗時7秒多,最後透過徹底改寫耗時3.8s。

(2) OR子查詢中的FILTER

再來看下常見的OR與子查詢連用情況,在實際優化過程中,遇到OR與子查詢連用,一般都不能unnest subquery了,可能會導致嚴重效能問題,OR與子查詢連用有兩種可能:

  • condition or subquery

  • subquery內部包含or,如in (select … from tab where condition1 or condition 2)

還是透過一個具體案例,分享下對於OR子查詢優化的處理方式,在某庫11g R2中碰到如下SQL,幾個小時都沒有執行完:

解決CBO的SQL最佳化問題(圖文詳解)

先來看下執行計畫:

解決CBO的SQL最佳化問題(圖文詳解)

怎麼透過看到這個執行計劃,一眼定位效能慢的原因呢?主要透過下列幾點來分析定位:

  • 執行計畫中的Rows,也就是每個步驟回傳的cardinality很少,都是幾行,在分析表也不是太大,那麼怎麼可能導致運行幾個小時都執行不完呢?很大原因可能就在於統計資訊不準,導致CBO優化器估算錯誤,錯誤的統計資訊導致錯誤的執行計劃,這是第一點。

  • 看ID=15到18部分,它們是ID=1 FILTER操作的第二個子節點,第一個子節點是ID=2部分,很顯然,如果ID=2部分估算的cardinality錯誤,實際情況很大的話,那麼對ID=15到18部分四個表全掃描次數將會巨大,那麼也就導致災難產生。

  • 很顯然,ID=2部分的一堆NESTED LOOPS也是很可疑的,找到ID=2操作的入口在ID=6部分,全表掃描DEALREC_ERR_201608,估算返回1行,很顯然,這是導致NESTED LOOPS操作的根源,因此,需要檢驗其準確性。

解決CBO的SQL最佳化問題(圖文詳解)

主表DEALREC_ERR_201608在ID=6查詢條件中經查要返回2000w行,計劃中估算只有1行,因此,會導致NESTED LOOPS次數實際執行千萬次,導致效率低下,應該走HASH JOIN,需要更新統計資訊。

另外ID=1是FILTER,它的子節點是ID=2和ID=15、16、17、18,同樣的ID 15-18也被驅動千萬次。

找出問題根源後,逐步解決。首先要解ID=6部分針對DEALREC_ERR_201608表格依照查詢條件substr(other_class, 1, 3) NOT IN (‘147’,‘151’, …)所得的cardinality的準確性,也就是要收集統計資料。

然而發現使用size auto,size repeat,對other_class收集直方圖均無效果,執行計劃中對other_class的查詢條件回傳行估算還是1(實際2000w行)。

解決CBO的SQL最佳化問題(圖文詳解)

再次執行後的執行計劃如下:

解決CBO的SQL最佳化問題(圖文詳解)

  • DEALREC_ERR_201608與B_DEALING_DONE_TYPE原來走NL的現在正確走HASH JOIN。 Build table是小結果集,probe table是ERR表大結果集,正確。

  • 但是ID=2與ID=11到14,也就是與TMI_NO_INFOS的OR子查詢,還是FILTER,驅動數千萬次子節點查詢,下一步優化要解決的問題。

  • 性能從12小時到2小時。

現在要解決的就是FILTER問題,對子查詢有OR條件的,簡單條件如果能夠查詢轉換,一般會轉為一個union all view後再進行semi join、anti join(轉換成union all view,如果謂詞類型不同,則SQL可能會報錯)。對於這種複雜的,優化器就無法查詢轉換了,因此,改寫是唯一可行的方法。分析SQL,原來查詢的是同一張表,而且條件類似,只是取的長度不同,那就好了!

解決CBO的SQL最佳化問題(圖文詳解)

如何讓帶有OR的子查詢執行計劃從FILTER變成JOIN。兩種方法:

1)改為UNION ALL/UNION

2)語意改寫.前面已經使用語意改寫,內部轉為了類似UNION的操作,如果要繼續減少表格的訪問,則只能徹底改寫OR條件,避免轉換為UNION操作。

再來分析下原始OR條件:

解決CBO的SQL最佳化問題(圖文詳解)

上面意義是ERR表的TMISID截取前8,9,10,11位元與TMI_NO_INFOS.BILLID_HEAD匹配,對應匹配BILLID_HEAD長度剛好為8,9,10,11。顯然,語意上可以這樣改寫:

ERR表與TMI_NO_INFOS表關聯,ERR.TMISID前8位與ITMI_NO_INFOS.BILLID_HEAD長度在8-11之間的前8位完全匹配,在此前提下,TMISID like 'BILLID_HEAD %'。

現在就動手徹底改變多個OR子查詢,讓SQL更加精簡,效率更高。改寫如下:

解決CBO的SQL最佳化問題(圖文詳解)

執行計畫如下:

解決CBO的SQL最佳化問題(圖文詳解)

1)現在的執行計畫終於變的更短,更容易讀,透過邏輯改寫走了HASH JOIN, 最後一條回傳300多萬行資料的SQL原先需要12小時運行的SQL,現在3分鐘就執行了。

2) 思考:結構良好,語意清晰的SQL編寫,有助於優化器選擇更合理的執行計劃,所以說,寫好SQL也是門技術活。

透過這個案例,希望能給大家一些啟發,寫SQL如何能夠自己充當查詢轉換器,編寫的SQL能夠減少表、索引、分區等的訪問,能夠讓ORACLE更易使用一些高效算法進行運算,進而提高SQL執行效率。

其實,OR子查詢也不一定就完全不能unnest,只是絕大多數情況下無法unnest而已,請看下例:

不可unnest的查詢:

解決CBO的SQL最佳化問題(圖文詳解)

可以unnest的查詢:

解決CBO的SQL最佳化問題(圖文詳解)

這2個SQL的差異也就是將條件or id3 = id2-1000轉換成or id3 -1000 = id2,前者不可以unnest,後者可以unnest,透過分析10053可以得知:

不可unnest的出現:

SU: Unnesting query blocks in query block SEL$1 ( #1) that are valid to unnest.

Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.

##SU: Considering subquery unnest on query block SEL$1 (#1).

SU:   Checking validity of unnesting subquery SEL$2 (#2)

SU:     SU bypassed: Invalid correlated predicates.

SU:   Validity checks failed.

#可以unnest的出現:

解決CBO的SQL最佳化問題(圖文詳解)

解決CBO的SQL最佳化問題(圖文詳解)

解決CBO的SQL最佳化問題(圖文詳解)

解決CBO的SQL最佳化問題(圖文詳解)

解決CBO的SQL最佳化問題(圖文詳解)1並將SQL改寫為:

最終CBO先查詢T3條件,做個UNION ALL視圖,之後再與T2關聯。從這裡來看,對於OR子查詢的unnest要求比較嚴格,從這條語句分析,ORACLE可進行unnest必須要求對主表列不要進行運算操作,優化器本身並未將+1000條件左移,正因為嚴格,所以大部分情況下,OR子查詢也就無法進行unnest了,從而導致各種效能問題。

(3)類別FILTER問題

類別FILTER問題主要體現在UPDATE關聯更新和標量子查詢中,雖然此類SQL語句中並未明確出現FILTER關鍵字,但內部操作和FILTER操作如出一轍。

先看下UPDATE關聯更新:

這裡要更新14999行,執行計畫如下:

ID=2部分是where exists選擇部分,先把需要更新的條件查詢出來,之後執行UPDATE關聯子查詢更新,可以看到ID=5部分出現綁定變數:B1,顯然UPDATE運算就類似於原來的FILTER,對於選出的每行與子查詢表NEW_TAB關聯查詢,如果ID列重複值較少,那麼子查詢執行的次數就會很多,從而影響效率,也就是ID=5的操作要執行很多次。

當然,這裡欄位ID唯一性很強,可以建立UNIQUE INDEX,普通INDEX燈,這樣第5步就可以走索引了。這裡為了舉例這種UPDATE的優化方式,不建索引,也可以搞定這樣的UPDATE:MERGR和UPDATE INLINE VIEW方式。

解決CBO的SQL最佳化問題(圖文詳解)

MERGE中直接利用HASH JOIN,避免多次存取操作,從而效率大增,再來看看UPDATE LINE VIEW寫法:

UPDATE

(SELECT a.status astatus,

解決CBO的SQL最佳化問題(圖文詳解)b.status bstatus

FROM old_tab a,

new_tab b解決CBO的SQL最佳化問題(圖文詳解)

#WHERE a.id=b .id

AND a.id  >9000000

)

SET astatus=bstatus;

解決CBO的SQL最佳化問題(圖文詳解)要求b.id是preserved key (唯一索引、唯一約束、主鍵),11g bypass_ujvc會報錯,類似MERGE操作。

再來看看標量子查詢,標量子查詢往往也是引發嚴重效能問題的殺手:

解決CBO的SQL最佳化問題(圖文詳解)標量子查詢的計畫和一般計畫的執行順序不同,標量子查詢雖然在上面,但是它由下面的CUSTOMERS表結果驅動,每行驅動查詢一次標量子查詢(有CACHE例外),同樣類似FILTER操作。

###如果對標量子查詢進行最佳化,一般就是改寫SQL,將標量子查詢改為外在連結形式(在限制和業務滿足的情況下也可改寫為普通JOIN):##### ##########透過改寫後效率大增,並且使用HASH JOIN演算法。下面看一下標量子查詢中的CACHE(FILTER和UPDATE關聯更新類似),如果關聯的列重複值特別多,那麼子查詢執行次數就會很少,這時候效率會比較好:###### #########標量子查詢和FILTER一樣,有CACHE,如上面的emp_a有108K的行,但是重複的department_id只有11,這樣只查詢只掃描11次,掃描子查詢表的次數少了,效率會提升。 ######針對FILTER性能殺手問題,主要分享這3點,當然,還有很多其它值得注意的地方,這需要我們日常多留心和積累,從而熟悉優化器一些問題的處理方法。 ######2 TABLE函數8168基數問題###############此問題來自binding in list問題,使用TABLE函數建構傳入的逗號分隔的值作為子查詢條件,一般前端傳入的值都較少,但是實際上走了HASH JOIN操作,無法使用T表索引,一旦執行頻率高,必然對系統影響較大,為什麼ORACLE不知道TABLE函數傳入了很少的值呢? ######進一步分析:############

從上面結果看出,TABLE函數的預設行數是8168行(TABLE函數所建立的偽表是沒有統計資料的),這個值不小了,一般比實際應用中的行數要多的多,經常導致執行計劃走hash join,而不是nested loop。怎麼改變這種情況呢?當然可以透過hint提示來改變執行計劃了,對where in list,常常使用的hint有:

first_rows,index,cardinality,use_nl等。

這裡特別介紹下cardinality(table|alias,n) ,這個hint很有用, 它可以讓CBO優化器認為表的行數是n,這樣就可以改變執行計劃了。現在改寫上面的查詢:

解決CBO的SQL最佳化問題(圖文詳解)

加了cardinality(tab,5)自動走CBO優化器了,優化器把表的基數看成5,前面的where in list查詢基數預設為8168的時候走的是hash join,現在有了cardinality,趕快試試:

解決CBO的SQL最佳化問題(圖文詳解)

現在走NESTED LOOPS操作,子節點可以走INDEX RANGE SCAN,邏輯讀從184變成7,效率提升數十倍。當然,在實際應用中,最好不要加hints,可以使用SQL PROFILER綁定。

3 選擇性計算不準確問題

Oracle內部計算選擇性都是以數字格式計算,因此,遇到字串類型,會將字串轉換成RAW類型,再將RAW類型轉換成數字,且ROUND到左起15位,這樣對於轉換後的數字很大,可能原來字串相差比較大的,內部轉換後的數字比較接近,這樣就會引起選擇性計算不準確問題。如下例:

解決CBO的SQL最佳化問題(圖文詳解)

執行計劃如下:

解決CBO的SQL最佳化問題(圖文詳解)

#SQL執行計劃走TEM_ID索引,需要執行1小時以上,計劃中對應步驟cardinality很少(幾十級),實際上很大(百萬級別),判斷統計資料出錯。

為什麼走錯索引?

由於TEM_ID是CHAR字串型,長度20,CBO內部運算選擇性會先將字串轉為RAW,然後RAW轉為數字,左起ROUND 15位元。因此,可能字串值差異大的,轉換成數字後值接近(因為超出15位元補0),導致選擇性計算錯誤。以TS_TEM_INFO_DEAD中的TEM_ID列為範例:

解決CBO的SQL最佳化問題(圖文詳解)

而實際依照條件查詢出的行數  29737305。因此,索引走錯了。

解決方法:

收集TEM_ID列直方圖,由於內部演算法有一定限制,導致值不同的字串,內部計算值可能一致,所以收集直方圖後,針對字串值不同,但是轉換成數字後相同的,ORACLE會將實際值儲存到ENDPOINT_ACTUAL_VALUE中,用於校驗,提高執行計畫的準確性。走正確索引GPYTM_ID後,運行時間從1小時以上到5s內。

解決CBO的SQL最佳化問題(圖文詳解)

4 新特性引發執行出錯問題

每個版本都會引入許多新特性,對於新特性,使用不當可能會引發一些嚴重問題,常見的例如ACS、cardinality feedback導致執行計劃變動頻繁,影響效率,子遊標過多等,所以,針對新特性需要謹慎使用,包括前面說的11g null aware anti join也存在很多BUG。

今天要分析的案例是10g到11g大版本升級過程中遇到的SQL,在10g中正常運行,但是到11g中卻執行出錯。 SQL如下:

解決CBO的SQL最佳化問題(圖文詳解)

10g正常,升級11g r2後日期轉換出錯,temp_value_code存多種格式字串。正確執行計劃LT關聯查詢先執行,之後與外表關聯。錯誤執行計劃是TASK_SPRING_VALUES先與外表關聯然後分組,作為VIEW再與TASK_SPRING_LABEL關聯,再次分組,這裡有2個GROUP BY操作,與10g執行計劃中只有1個GROUP BY操作不同,最終導致報錯。

很顯然,對於為什麼出現兩個GROUP BY操作,需要進行研究,首選10053:

解決CBO的SQL最佳化問題(圖文詳解)

分析按照10053操作,是否找到非日期格式值:

解決CBO的SQL最佳化問題(圖文詳解)

的確找到非yyyy-mm-dd格式字串,因此,to_date操作失敗。透過10053可以看出,這裡使用了Group by/Distinct Placement操作,因此,需要找到對應的控制參數,關閉此查詢轉換。

關閉GBP隱含參數後正確:_optimizer_group_by_placement。正確執行計劃如下:

解決CBO的SQL最佳化問題(圖文詳解)

思考: 這個問題的本質在於字段用途設計不合理,其中temp_value_code作為varchar2儲存普通字元、數字型字元、日期格式yyyy-mm -dd,程式中有to_number,to_date等轉換,非常依賴執行計劃中表連接和條件的先後順序。所以,良好的設計很重要,特別要確保各關聯字段類型的一致性以及字段作用的單一性,符合範式要求。

5 坑爹寫法CBO無能為力

結構優良的SQL能夠更易被CBO理解,從而更好地進行查詢轉換操作,從而為後續生成最佳執行計劃打下基礎,然後實際應用過程中,因為不注重SQL寫法,導致CBO也無能為力。以下以分頁寫法案例作為探討。

低效分頁寫法:

解決CBO的SQL最佳化問題(圖文詳解)

原寫法最內層依use_date等條件查詢,然後排序,取得rownum並取別名,最外層使用rn規律。問題在哪?

分頁寫法如果直接<,<=可在排序後直接rownum取得(兩層嵌套),如果需要取得區間值,在最外層取得>,>=(三層嵌套)。

此語句取得<=,而使用三層巢狀,導致無法使用分頁查詢STOPKEY演算法,因為rownum會阻止謂詞推入,導致執行計畫中沒有STOPKEY操作。

<=分頁只需要2層嵌套,done_date列有索引,根據條件done_date>to_date('20150916','YYYYMMDD')和只取得前20行,可有效利用索引和STOPKEY演算法,改寫完成後使用索引降序掃描,執行時間從1.72s到0.01s,邏輯IO 從42648到59,具體如下:

解決CBO的SQL最佳化問題(圖文詳解)

高效分頁寫法應該符合規範,並且能夠充分利用索引消除排序。

6 CBO BUG問題

CBO BUG出現比較多的就是在查詢轉換中,一旦出現BUG,可能查找就比較困難,這時候應該透過分析10053或透過使用SQLT XPLORE快速找到問題根源。如下例:

解決CBO的SQL最佳化問題(圖文詳解)

這個表的oper_type有索引,並且條件oper_type>'D' or oper_type<'D'走索引較好,但是實際上Oracle卻走了全表掃描,透過SQLT XPLORE快速分析:

解決CBO的SQL最佳化問題(圖文詳解)

其中上面2個是走索引的執行計劃,點進去:

解決CBO的SQL最佳化問題(圖文詳解)

很顯然,_fix_control=8275054很可疑,透過查詢MOS:

解決CBO的SQL最佳化問題(圖文詳解)

轉換成a<>b,很顯然使用不了索引了,可以透過關閉此8275054解決。

7 HASH碰撞問題

HASHJOIN是專門用來做大數據處理的高效演算法,並且只能用於等值連接條件,針對表build table(hash table)和probe table建構HASH運算,找出滿足條件的結果集。

一般格式如下:

HASH JOIN

build table

probe table

這裡的build table應該選擇透過篩選條件過濾後,結果集尺寸較小的表(size不是rows),然後按照連接條件進行HASH函數運算,把需要的列和HASH函數運算結果儲存到hash bucket中,hash bucket本身就是鍊錶結構。同樣,對於probe table也需要進行hash函數運算,並根據運算結果到build table的hash bucket中去查詢,查到滿足,查不到丟棄。當然,ORACLE HASH JOIN內部構造還是很複雜的,具體可以參考Jonathan Lewis的CBO原理書。

HASH查找天生存在的問題:

一旦build table的連接條件列選擇性不好(也就是重複值特別多),那麼某些hash bucket上可能儲存大量數據,由於hash bucket本身是鍊錶結構,那麼當查詢這些hash bucket時,效率會急劇下降,此問題就是HASH運算的經典問題Hash Collision(HASH碰撞)。

解決CBO的SQL最佳化問題(圖文詳解)

下面用一個小範例來分析下hash碰撞:

解決CBO的SQL最佳化問題(圖文詳解)

其中a 表61w 多筆記錄, b 表7w 多筆記錄,此SQL 結果傳回8w 多筆記錄,從執行計畫來看,做HASH JOIN 運算沒有什麼問題,但實際此SQL 執行10 分鐘以上都沒有執行完,效率非常低下, CPU 使用率突增,遠大於存取兩個表的時間。

如果你了解HASHJOIN,這時候,你應該考慮是不是遇到hash collision了,如果很多bucket上存儲大量數據,那麼對於這樣的hash bucket裡的數據查找那就類似於nested loops了,必然效率大減。如下進一步分析:

解決CBO的SQL最佳化問題(圖文詳解)

 

找出大於重複資料大於3000條的值,果然有很多,當然剩下資料也有很多比較大,探測HASH JOIN,可以使用EVENT 10104:

解決CBO的SQL最佳化問題(圖文詳解)

#可以看到儲存100行+的bucket有61個,而且最多的一個bucket中儲存了3782條,也就是和我們查詢出來的一致。還是回到原始SQL:

Oralce為什麼選擇substr(b.object_name,1,2)來建構HASH表呢,如果能將OR展開,原始SQL改為一個UNION ALL形式的,那麼HASH表可以採用substr(b.object_name,1,2)和b.object_id以及data_object_id來構建,那麼必然唯一性很好,那應該可以解決hash collision問題,改寫如下:

解決CBO的SQL最佳化問題(圖文詳解)

現在的SQL執行時間從原來的10幾分鐘都沒有結果,到4s執行完畢,再來看內部構建的HASHTABLE資訊:

解決CBO的SQL最佳化問題(圖文詳解)

最多的一個bucket中只儲存6條數據,那肯定效能比前面好很多了。 Hash碰撞的危害很大,實際應用中,可能比較複雜,如果遇到hash碰撞問題,最好的方式就是進行SQL重寫,盡量從業務上分析,能不能增加其它選擇性比較好的列進行JOIN 。

回頭來看看,既然我都知道改寫成UNION ALL後,就採用2個組合列來建構比較好的HASH表,那麼 Oracle 為什麼不這麼做呢?很簡單,我這裡只是舉例刻意這麼做的而已,用以說明HASH碰撞的問題,對於這種簡單SQL,有選擇性更好的列,收集下統計信息,Oracle就可以將的SQL進行OR展開了。

三、加強SQL審核,解決效能問題於襁褓之中

應用系統SQL眾多,如果總是作為救火隊員角色解決線上問題,顯然不能滿足當今IT系統高速發展的需求,基於資料庫的系統,主要效能問題在於SQL語句,如果能在開發測試階段就對SQL語句進行審核,找出待優化SQL,並給予智能化提示,快速輔助優化,則可以避免眾多線上問題。另外,還可以對線上SQL語句進行持續監控,及時發現效能有問題的語句,從而達到SQL的全生命週期管理目的。

為此,公司結合多年運作維護與最佳化經驗,自主研發了SQL審核工具,大幅提升SQL審核最佳化與效能監控處理效率。

SQL審核工具採用四步驟法則:SQL採集—SQL分析—SQL最佳化—上線跟踪,SQL審核四步法區別傳統的SQL最佳化方法,它著眼於系統上線前的SQL分析與最佳化,重點解決SQL問題於系統上線前,扼殺效能問題於襁褓之中。如下圖所示:

解決CBO的SQL最佳化問題(圖文詳解)

解決CBO的SQL最佳化問題(圖文詳解)

透過SQL效能管理平台可解決下列問題:

  • 事前: 上線前SQL 效能審核,扼殺效能問題於襁褓之中;

  • 事中:SQL效能監控處理,及時發現上線後SQL效能發生的變化,在SQL效能變化且未造成嚴重問題時,及時解決;

  • 事後:TOPSQL監控,及時警告處理。

SQL效能管理平台實現了SQL效能的360度全生命週期管控,並且透過各種智慧化提示和處理,將絕大多數本來因SQL引發的效能問題,解決在問題發生前,先提高系統穩定性。

下面是SQL審核的典型案例:

解決CBO的SQL最佳化問題(圖文詳解)

解決CBO的SQL最佳化問題(圖文詳解)

執行計畫如下:

解決CBO的SQL最佳化問題(圖文詳解)

原始SQL執行1688s。透過SQL審核智慧優化準確找到最佳化點—分區列有類型轉換。 優化後0.86s。

解決CBO的SQL最佳化問題(圖文詳解)

SQL審核是新炬資料庫效能管理平台DPM的一個模組,想了解更多關於DPM的信息,可加鄒德裕大師(微信:carydy)交流探討。

今天主要和大家分享了一些Oracle優化器中存在的問題以及常見問題解決方法,當然,優化器問題不僅限於今天分享的,雖然CBO非常強大,並且在12c中有巨大改進,但是,存在的問題也很多,只有平時多累積和觀察,掌握一定的方法,在能在遇到問題事後運籌帷幄,決勝千里。

Q&A

Q1: hash join是不是有排序,可以簡單說hash join的原理嗎?

A1: ORACLE HASH JOIN本身不需要排序,這是差異SORTMERGE JOIN特徵之一。 ORACLE HASH JOIN原理比較複雜,可以參考Jonathan Lewis的Cost-Based Oracle Fundamentals的HASH JOIN部分,針對HASHJOIN最重要的是在原理基礎上搞清楚什麼時候會慢,比如HASH_AREA_SIZE過小,HASH TABLE不能完全放到記憶體中,那麼會發生磁碟HASH運算,再例如上面講的HASH碰撞發生。

Q2: 什麼時候不走索引?

A2: 不走索引情況比較多,首要的原因是統計資料不准導致的,第二原因就是選擇性太低,走索引比走全掃效率更差,還有一個比較常見的就是對索引列進行了運算,導致無法走索引。其它還有很多原因會導致不能走索引,詳細參考MOS文檔:Diagnosing Why a Query is Not Using an Index (文檔 ID 67522.1)。


 

以上是解決CBO的SQL最佳化問題(圖文詳解)的詳細內容。更多資訊請關注PHP中文網其他相關文章!

相關標籤:
來源:php.cn
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板
關於我們 免責聲明 Sitemap
PHP中文網:公益線上PHP培訓,幫助PHP學習者快速成長!