踩坑CBO，解決那些坑爹的SQL優化問題

本文根據DBAplus社群第93期線上分享整理而成

講師介紹

丁俊

新炬網路首席效能優化專家

SQL稽核產品經理

• DBAplus社群聯合發起人，《劍破冰山-Oracle開發藝術》副主編。

• Oracle ACEA，ITPUB開發版資深版主，十年電信行業從業經驗。

本次分享大綱：

1. CBO優化器存在哪些坑

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

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

4. 分享現場FAQ

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：

竟然還是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問題。

1FILTER效能殺手問題

FILTER操作是執行計劃中常見的操作，這種操作有兩種情況：

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

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

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

單子節點：

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

多子節點：

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

（1）NOT IN子查詢中的FILTER

先來看下NOT IN情況：

針對上面的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問題進行優化，如下所示：

通過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的目的。

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

說白了，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出現效能問題：

執行計劃如下：

這裡的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操作的危害到底有多大呢，可以通過查詢實際執行計劃來看：

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

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

針對此問題的分析如下：

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

• 收集統計資訊是否有效

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

針對第一種情況：

引數是TRUE，顯然沒有問題。

針對第二種情況：

收集統計資訊發現無效。

那麼此時，只能寄希望於第三種情況：可能是BUG或者升級過程中修改了其它引數影響了無法走NULL AWARE ANTI JOIN。ORACLE BUG和引數那麼多，那麼我們怎麼快速找到問題根源導致是哪個BUG或者引數導致的呢？這裡給大家分享一個神器SQLT，全稱（SQLTXPLAIN），這是ORACLE內部效能部門開發的工具，可以在MOS上下載，功能非常強勁。

此工具詳細用法不做贅述，針對此工具，Apress也出了一本書籍，感興趣的可以學習一下：

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

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

SQLT找出問題根源：

 

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

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

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

原SQL使用自連線、兩個子查詢，冗餘繁雜。自然想到用分析函式進行改寫，避免自連線，從而提高效率。改寫後的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，幾個小時都沒有執行完：

先來看下執行計劃：

怎麼通過看到這個執行計劃，一眼定位效能慢的原因呢？主要通過下列幾點來分析定位：

• 執行計劃中的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操作的根源，因此，需要檢驗其準確性。

主表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行）。

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

• 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，原來查詢的是同一張表，而且條件類似，只是取的長度不同，那麼就好辦了！

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

1）改為UNION ALL/UNION

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

再來分析下原始OR條件：

上面含義是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更加精簡，效率更高。改寫如下：

執行計劃如下：

1）現在的執行計劃終於變的更短，更易讀，通過邏輯改寫走了HASH JOIN，最終一條返回300多萬行資料的SQL原先需要12小時執行的SQL，現在3分鐘就執行完了。

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

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

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

不可unnest的查詢：

可以unnest的查詢：

這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的出現：

並且將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方式。

MERGE中直接利用HASH JOIN，避免多次訪問操作，從而效率大增，再來看看UPDATE LINE VIEW寫法：

UPDATE

  (SELECT a.status astatus,

    b.status bstatus

  FROM old_tab a,

    new_tab b

  WHERE a.id=b.id

  AND a.id  >9000000

  )

SET astatus=bstatus;

要求b.id是preserved key (唯一索引、唯一約束、主鍵），11g bypass_ujvc會報錯，類似MERGE操作。

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

標量子查詢的計劃和普通計劃的執行順序不同，標量子查詢雖然在上面，但是它由下面的CUSTOMERS表結果驅動，每行驅動查詢一次標量子查詢（有CACHE例外），同樣類似FILTER操作。

如果對標量子查詢進行優化，一般就是改寫SQL，將標量子查詢改為外連線形式（在約束和業務滿足的情況下也可改寫為普通JOIN）：

通過改寫之後效率大增，並且使用HASH JOIN演算法。下面看一下標量子查詢中的CACHE（FILTER和UPDATE關聯更新類似），如果關聯的列重複值特別多，那麼子查詢執行次數就會很少，這時候效率會比較好：

標量子查詢和FILTER一樣，有CACHE，如上面的emp_a有108K的行，但是重複的department_id只有11,這樣只查詢只掃描11次，掃描子查詢表的次數少了，效率會提升。

針對FILTER效能殺手問題，主要分享這3點，當然，還有很多其它值得注意的地方，這需要我們日常多留心和積累，從而熟悉優化器一些問題的處理方法。

2TABLE函式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，這樣就可以改變執行計劃了。現在改寫上面的查詢：

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

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

3選擇性計算不準確問題

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

執行計劃如下：

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

為什麼走錯索引？

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

            

而實際根據條件查詢出的行數  29737305。因此，索引走錯了。

解決方法：

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

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

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

今天要分析的案例是10g到11g大版本升級過程中遇到的SQL，在10g中正常執行，但是到11g中卻執行出錯。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：

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

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

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

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

5坑爹寫法CBO無能為力

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

低效分頁寫法：

原寫法最內層根據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，具體如下：

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

6CBO BUG問題

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

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

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

很顯然，_fix_control=8275054很可疑，通過查詢MOS：

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

7HASH碰撞問題

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碰撞）。

 

下面用一個小例子來分析下hash碰撞：

         

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

         

如果你瞭解HASHJOIN，這時候，你應當考慮是不是遇到hash collision了，如果很多bucket上儲存大量資料，那麼對於這樣的hash bucket裡的資料查詢那就類似於nested loops了，必然效率大減。如下進一步分析：

        

查詢一下大於重複資料大於3000條的值，果然有很多，當然剩下資料也有很多比較大，探測HASH JOIN，可以使用EVENT 10104：

可以看到儲存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問題，改寫如下：

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

最多的一個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問題於系統上線前，扼殺效能問題於襁褓之中。如下圖所示：

通過SQL效能管理平臺可解決下列問題：

• 事前：上線前SQL效能稽核，扼殺效能問題於襁褓之中；

• 事中：SQL效能監控處理，及時發現上線後SQL效能發生的變化，在SQL效能變化並且沒有引起嚴重問題時，及時解決；

• 事後：TOPSQL監控，及時告警處理。

         

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

 

下面是SQL稽核的一個典型案例：

執行計劃如下：

原SQL執行1688s。通過SQL稽核智慧優化準確找到優化點—分割槽列有型別轉換。優化後0.86s。

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)。