本次分享的內容是基于Oracle的SQL優化，以一條巨慢的SQL為例，從快速解讀SQL執行計劃、如何從執行計劃中找到SQL執行慢的Root Cause、統計信息與cardinality問題、探索性能殺手Filter操作、如何進行邏輯重寫讓SQL起飛等多個維度進行解析，最終優化巨慢SQL語句，希望能夠拋磚引玉，和大家一起探討SQL優化方法。

另外，還簡單介紹了兩種解決疑難SQL優化問題的工具：10053和SQLT，特別是SQLT，往往在無計可施過程中，可能建立奇功，建議大家抽空研究下SQLT工具。最后對本次分享進行總結和思考：分享SQL Tuning RoadMap以及SQL Tuning最佳實踐的相關內容。

大綱如下：

這條巨慢SQL執行預計耗時12小時以上，返回百萬行數據。首先我們接手一條SQL優化問題，至少需要做以下兩件事：

1. 了解SQL結構：SQL中使用了哪些語法，這些語法是不是經常會導致性能問題，比如標量子查詢的濫用。

2. 獲取執行計劃：執行計劃反應了SQL的執行路徑，直接影響了SQL的執行效率。如何從執行計劃中找出問題，是SQL Tuning的關鍵。

言歸正傳，先揭開巨慢SQL的神秘面紗：

這條語句其實就是查詢DEALREC_ERR_201608表，有各種復雜的子查詢，初看此子查詢，我基本已經了解問題大概出在什么地方了，先賣個關子，看執行計劃先：

這種執行計劃拿到手，其實很容易找出問題：

（1）分析指標問題：Rows，也就是每步驟的cardinality很小，說明每步返回的結果行數很少。這點值得懷疑。

（2）由于cardinality很少導致了Operation走了一系列Nested Loops操作，我們知道，NL操作，一般是驅動表返回的結果行數很少，被驅動表走索引，返回的最終結果比較少（一般最多幾千行），效率會很高。

以上兩點值得注意：如果cardinality是準確的，那么這個執行計劃中走一系列Nested Loops的部分應該沒有多大問題，但是，如果cardinality不是準確的呢？那就是大問題。這也就是一些初級開發人員的思維一樣，經常喜歡對數據的處理使用循環，如果循環的次數少那還好，如果循環次數很多，那就會很慢。循環操作完全依賴于循環的次數，從SQL執行計劃里看，也就是依賴于驅動表返回的結果行數，很顯然，這種不適合大量數據運算。

（3） 在ID=1中有個Filter，這個Filter的子操作是ID=15~18的全表掃描。Filter可是執行計劃里的一個大問題，當然，這里的問題Filter必須有2個或2個以上子節點的操作，如果是單節點，那只是簡單的過濾條件而已。

對于一般的SQL優化，必須得分析SQL的語法結構，語義以及解讀SQL執行計劃，以SQL執行計劃為基準，分析執行計劃中的問題，來進行SQL Tuning，基本能解決大部分SQL優化問題了。

當然，以我的理解，SQL優化不僅需要很強的邏輯思維、正確的理論指導、各種SQL語法的精通、熟悉index的使用、了解CBO相關內容，甚至還需從大局觀進行把控：物理模型的設計以及對具體的業務分析。

SQL執行計劃作為SQL優化的一把鑰匙，必須要很好地利用起來。經常看到開發人員喜歡用PL/SQL Developer之類的工具來看執行計劃。這里我得提醒下，這種內部調用的是Explain Plan For，可能不夠準確，特別是有綁定變量的情況下，最重要的一點，對于長的SQL執行計劃，簡直沒法進行分析。個人還是喜歡文本類型的執行計劃，特別是真實的執行計劃，能獲取A-ROWS，E-ROWS這些指標的執行計劃，讓我對執行計劃中的問題一覽無余，特別對于巨慢的SQL，也可以運行個幾分鐘中斷后獲取部分信息來協助判斷。

執行計劃要點如下：

• 找入口：通過最右最上最先執行原則找出執行計劃入口操作。對于巨長執行計劃Copu到UE里使用光標縮進下探法則可找出入口，由于執行計劃是鋸齒狀結構，父節點的子操作是向右縮進的，因此，從ID=0開始，光標向下向右縮進下探，直到縮進不了停止，然后按照同級別的，也就是格式的垂直線是同一級的，上面的是入口。

• 看關系：各操作之間的關系：Nesed Loops、HASH JOIN、Filter等是否準確，以及操作的順序是否準確，直接關系此操作甚至影響整個SQL的執行效率。

• 理順序：一步走錯，滿盤皆輸。通過理清執行計劃順序找出key steps。

• 重操作：執行計劃中的Operation和Predicate部分是需要關注的核心內容，從操作中看出不合理部分，以此建立正確索引等優化措施。

• 求真實：執行計劃中指標是估算的，估算的指標和實際情況很可能不匹配。所以優化SQL需要了解每步驟真實的基數、真實執行時間和Buffer gets等，從而準確找出問題Root cause。（可以根據謂詞手動計算、建議采用display_cursor方式獲取A-ROWS、A-TIME等信息，工具有很多，也可以使用sql monitor等），如果采用Explain Plan For、SET AUTOTRACE之類的看執行計劃，由于指標信息是不準的，要獲取真實的信息，還需要手動根據謂詞去計算，然后比較估算的和真實的差別，從而判斷問題。

• 輕成本：COST雖然是CBO的核心內容，但因為執行計劃中COST不一定準確反應SQL快慢，因此不要唯COST論，COST只是一個參考指標，當然可以通過執行計劃判斷一些COST是否明顯存在問題，比如COST非常小，但是SQL執行很慢，可能就是統計信息不準確了。

以上執行計劃入口是ID=6(全表掃描)，返回行數1，之后與ID=7的做Nested Loops操作。詳細見分析部分。

• 問題：為什么要尋找執行計劃入口？為什么要分析執行計劃各步驟順序和關系？

各種操作之間的關系是由cardinality等各種因素觸發的，不正確的cardinality會導致本來應該走HASH JOIN的走了Nested loops Join。往往入口處就有問題，導致后續執行計劃全部錯誤，所以明確各種步驟的關系，有助于找出影響問題的根源步驟。

理清執行計劃順序，有助于理解SQL內部的執行路徑，通過執行的實際情況判斷出不合理步驟操作。

• 重操作、求真實、輕成本是通過執行計劃優化SQL的重要方法。

這里的入口是ID=6的全表掃描，返回行是1行，不是準確的，很顯然，找到入口的問題，已經可以解決一部分問題了。

• 主表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步驟估算的cardinality不準確問題。

• 找出入口操作ID=6,由于ID=6操作的cardinality估算為1導致后續走一系列Nested Loops影響效率。

• cardinality的計算與謂詞緊密相關，所以要找出ID=6的謂詞，根據謂詞手動計算真實card與估算card之間的區別。

• 嘗試收集統計信息，檢驗效果。

現在的問題，也就是轉為對表DEALREC_ERR_201608統計信息準確性的問題，特別是統計信息對謂詞計算的準確性。

• 嘗試更新統計信息：

發現使用size auto,size repeat,對other_class收集直方圖均無效果，執行計劃中對other_class的查詢條件返回行估算還是1（實際返回2000w行）。如何解決？card的計算和謂詞緊密相關，查看謂詞：

怎么辦？思緒萬千，靈光乍現！

還有更好的辦法嗎？

突然想起11g有個統計信息收集新特性：擴展統計信息收集。

擴展統計信息一收集，執行計劃如下：

• 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小時。到這里結束了嗎？

統計信息的問題還是很多的，一個表的統計信息收集，特別是自動收集，不一定能讓所有相關SQL找到最佳執行路徑，特別是SQL條件復雜、數據傾斜、表類型定義不準確等情況，特別是使用了復雜條件，CBO無法準確計算對應謂詞的card，或者類型定義不準確，本來是日期的用了VARCHAR2，內部全部要轉為數字來計算選擇性，很顯然，亂定義列類型也是有問題的。所以有針對性地修正收集的統計信息，是很有必要的。

• 疑問1：100%收集為什么還沒有走正確執行計劃？

統計信息收集比例高不代表就可以翻譯對應謂詞的特征，而且統計信息內部有很多算法限制以及不完善的情況，比如11g的擴展統計信息來繼續完善，12c也有很多統計信息完善的特性。所以并不是比例低就不好，比例高就好！統計信息的收集要滿足核心SQL的執行效率，對于非核心SQL一定程度上可以不用過度關注，因為統計信息很難滿足所有相關SQL的最佳執行。

• 疑問2：統計信息各種維度收集了包括直方圖都收集了怎么不起作用？

直方圖有很多限制，12c之前，只有頻度直方圖和等高直方圖兩種，對很多值的分布不能精確表示，所以有很多限制。因此，12c又增加了2種直方圖：頂級頻度直方圖和混合直方圖。另外直方圖還有只存儲前32位字符的限制。

• 疑問3：直方圖只對走索引的有作用？

很顯然不對，直方圖只是反應數據的分布，數據的分布正確，對應謂詞可以查詢出比較準確的cardinality，從而影響執行計劃，所以對全表也是有用的。

• 疑問4：收集或更新了統計信息，執行計劃怎么變得更差了？

很有可能，比如把原來的直方圖給去掉了可能導致執行計劃變差。因此，一般更新使用size repeat，除非是確認需要修改某些直方圖，另外謂詞和統計信息緊密相關，某些謂詞條件一旦收集統計信息，可能就計算不準確了。

• 疑問5：執行計劃中cardinality顯示的和已有統計信息計算不一致？

Oracle CBO內部算法很復雜，而且Bug眾多，遇到問題要大膽懷疑。

• 疑問6：統計信息應該按照Oracle建議自動收集？

具體問題具體分析，是讓Oracle自動還是自己寫腳本收集，都需要長期實踐總結，對于一個復雜系統來說采樣比例和method_opt很多需要定制設置。

• 疑問7：為什么唯一性很好的列，還需要收集直方圖？

選擇性的內部計算是要轉成數字的：CBO內部計算選擇性會先將字符串轉為RAW，然后RAW轉為數字，左起ROUND15位。如果字符串的唯一性好， 但是計算成數字后唯一性不好，則會導致執行計劃錯誤，這時候也需要收集直方圖。

• 疑問8：我需要根據統計信息以及CBO公式去計算COST嗎？

不需要，除非你很喜歡研究，這樣做只會得不償失。了解各種JOIN算法、查詢轉換特性、索引等效率和哪些有關即可，COST不是最需要關心的指標，我們應該關心SQL高效運行所需的執行路徑和執行方法，是否可以達到及早過濾大量數據，JOIN方法和順序是否正確，是否可以建立高效訪問對象等。

• 為什么會形成Filter操作？（多子節點，單子節點純粹過濾操作）

Filter形成于查詢轉換期間，如果對于子查詢無法進行unnest轉換來消除子查詢，則會走Filter。走Filter說明子查詢是受外表結果驅動，類似循環操作！很顯然，如果驅動的次數越多，效率越低！

查詢轉換是能夠生成高效SQL執行計劃的重要步驟，查詢轉換不能做好，后面的很多執行路徑就沒法走了。掌握查詢轉換機制，對如何寫高效的SQL，調優SQL至關重要，了解的越深，對CBO就越了解。

下面是CBO組件圖，熟悉對應組件是SQL優化必須的內容：

• Filter什么時候高效？

Filter本身會構建HASH表來保存輸入/輸出對，以備后續減少子查詢執行次數，這是與純粹Nesed Loops操作的典型區別，比如from a where a.status In(select b.staus from b…)。 如果status前面已經查過，則后續不需要再次執行子查詢，而是直接從保存的HASH表中獲取結果，這樣減少了子查詢執行次數，從而提高效率。也就是說，如果子查詢關聯條件的重復值很多，Filter還是有一定的優勢，否則就是災難！

• Filter與push_subq hints

如果走Filter則子查詢是受制于子查詢外結果集驅動，也就是子查詢是最后執行，但是實際有時候子查詢應該先執行效率更好，這時候可以使用push_subq hints。

• 簡化前面的語句關鍵部分如下：

• Oracle內部改寫如下，無法unnest,如果unnest：

執行計劃如下：

從執行計劃里可以看到，Filter多子節點一般有如下特點：

1. 自動生成的綁定變量：B1，因為需要執行循環操作

2. 轉為EXISTS

所以，以后看到有自動生成的綁定變量的執行計劃，都是類似Filter的操作，比如標量子查詢，UPDATE關聯子查詢，優化的話，都需要干掉（類）Filter來優化。

這里的例子其實是一個CBO的限制：

• 含有OR的子查詢，經常性無法unnest，Oracle大多無法給轉換成UNION/UNION ALL形式的查詢

• 所以，針對這樣的語句優化：

  1）改寫為UNION/UNION ALL形式

  2）根據語義、業務含義徹底重寫

也就是說，需要重構查詢，消除Filter！慢的根源如下，這里7萬多行，只執行了116行打印的執行計劃！ID=3~6的執行次數依賴于ID=2的結果行數，ID=3~6全表掃描次數太多。

• 回到原來的SQL中，看如何改寫，通過分析，可以改為JOIN形式：

• 改寫后執行時間從2小時到8分鐘，返回360w行+。雖然執行計劃更復雜了，但是充分利用了HASH JOIN、MERGE JOIN這種大數據量處理算法代替原來的Filter，更高效。如果不走OR擴展走什么？（走Nested Loops，對IMS_NUM_INFO掃描從4次到1次，也很慢）。

• OR擴展存在缺點，大表還是多次被訪問，還能繼續優化嗎？

• 上一次重寫，等于使用了第一種方法，用UNION/UNION ALL消除Filter，那么如何消除UNION/UNION ALL呢，也就是要將OR語句合并為AND！

追本溯源，從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更加精簡，效率更高。

通過上一節的思路，改寫SQL如下：

執行計劃如下：

• 現在的執行計劃終于變的更短，更易讀，通過邏輯改寫走了HASH JOIN，那速度，杠杠的，最終一條返回300多萬行數據的SQL原先需要12小時運行的SQL，現在3分鐘就執行完了。

• 思考：結構良好，語義清晰的SQL編寫，有助于優化器選擇更合理的執行計劃，看來編寫SQL真的有很多值得注意的地方。

• 一條SQL執行12分鐘沒有結果：其中object_id有索引，從查詢結構來看，內層查詢完全可以獨立執行（最多100行），然后與外層的表進行關聯，走NL，這樣可以利用到object_id索引，然而，事與愿違，ID=4出現Filter，這樣內層查詢會驅動N次，問題出在何處？

下面就使用10053探索優化器行為來研究此問題。

• 從10053中可以看出，查詢轉換失敗，因為遇到了rowid,當然把Rowid改別名是可以，但是此SQL要求必須用Rowid名字。

• 通過改寫消除Filter運算如下：

• SQL能否生成正確執行計劃，不光和統計信息、索引等有關，能否正確執行查詢轉換是至關重要的，由于各種復雜的查詢轉換機制導致Bug很多，Oracle對這些已知Bug通過fix control參數管理，有的默認打開，有的默認關閉。所以，如果遇到復雜的SQL，特別包含復雜視圖的SQL，比如謂詞無法推入這種查詢轉換，收集統計信息無效，這時候可以考慮是否遇到了Bug。

• Bug那么多，我怎么知道是哪個？SQLT神器來幫你！使用SQLT里面的XPLORE工具，可以把參數打開關閉一遍，并且生成對應執行計劃，這樣通過生成的報告，可以一眼定位問題。（當然，是已知Bug，比如前面的Rowid問題，也是定位不到的）

• 問題背景：11.2.0.2升級到11.2.0.4出現此問題，性能殺手Filter操作，SQL跑不出來，Filter產生原因，無法unnest subquery，其中11g _optimizer_null_aware_antijoin參數為true。

執行計劃如下所示：

很顯然，這兩個Filter有問題，按理說應該走ANTI JOIN。

下面看看使用SQLT的XPLORE來找出問題，先來看下SQLT介紹：

跑一下XPLORE，只需要調用XPLAIN方法即可，提高效率，不實際執行SQL：

可以看到和對應的隱含參數_optimizer_squ_bottomup設置有關，這是一個和子查詢的查詢轉換有關的隱含參數。

修正之后的執行計劃：

走回ANTI JOIN，正確了。終于從跑不出來到幾秒搞定，其實還可以優化，但是那已經不是最重要的事了！

SQLT XPLORE的一些限制：

• 只能單個參數測試是否有效；

• 做XPLORE使用XPLAIN方法，內部調用explain plan for,不需要執行從而提高效率和避免修改數據；

• 只有是已知參數或者Bug fix control才會有用，對于未知Bug無用，當然修改參數需要做足測試，如果非批量問題，建議找出原因，使用SQL PROFILE搞定，批量問題需要做足測試再實施修改！

• 獲取問題SQL制定優化目標

從AWR、ASH、SQL CHECK SCRIPTS等主動發現有問題的SQL、用戶報告有性能問題時DBA介入等，通過對SQL的執行情況分析，制定SQL的優化目標。

• 檢查執行計劃

explain工具、sql*plus autotrace、dbms_xplan、10046、10053、awrsqrpt.sql等。

• 檢查統計信息

Oracle使用DBMS_STATS包對統計信息進行管理，涉及系統統計信息、表、列、索引、分區等對象的統計信息，統計信息是SQL能夠走正確執行計劃的保證。

• 檢查高效訪問結構

重要的訪問結構，諸如索引、分區等能夠快速提高SQL執行效率。表存儲的數據本身，如碎片過多、數據傾斜嚴重、數據存儲離散度大，也會影響效率。

• 檢查影響優化器的參數

optimizer_mode、optimizer_index_cost_adj、optimizer_dynamic sampling、_optimizer_mjc_enabled、_optimizer_cost_based_transformation、hash_join_enable等對SQL執行計劃影響較大。

• 優化器新特性、Bug

如11g的ACS、cardinality feedback、automatic serial direct path、extended statistics、SQL query result cache等。有的新特性會導致問題，需謹慎使用。

• SQL語句編寫問題

SQL語句結構復雜、使用了不合理的語法，比如UNION代替UNION ALL可能導致性能低下。

• 優化器限制

無法收集準確的統計信息、無法正確進行查詢轉換操作等，如SEMI JOIN、ANTI JOIN與or連用會走Filter操作。

• 其他

主要涉及設計問題，如應用在業務高峰期運行，實際上可以放到較空閑狀態運行。表、索引、分區等設計不合理。

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

針對以上種種，我們新炬網絡以多年運維和優化經驗自主研發出了一款SQL審核工具，通過SQL采集—SQL分析—SQL優化—上線跟蹤這四步SQL審核法則， 極大地提升了SQL審核優化和性能監控處理效率。有別于傳統的SQL優化方法，它是著眼于系統上線前的SQL分析和優化，重點解決SQL問題于系統上線之前，將性能問題扼殺于襁褓之中。

首頁審核總體情況一覽無余：

審核頁面展現詳細SQL審核情況：

SQL審核結果多維護分析：

優化建議詳細準確：

內置上百種規則集，可按需選擇：

SQL性能管理平臺必須解決事前事中事后的SQL全生命周期管理問題。

• 事前：上線前SQL性能審核，扼殺性能問題于襁褓之中。

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

• 事后：核心SQL監控，及時告警處理。

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

另外對SQL性能的分析，從SQL寫法、SQL執行信息、執行計劃、統計信息等多方面定義規則，多維度進行分析，提供智能化的建議，提升優化速度和準確性。

SQL性能管理平臺特點-自動化采集、分析、跟蹤，減少DBA分析時間，提高管控效率：

SQL審核是新炬網絡數據庫性能管理平臺DPM的一個模塊，大家若想了解更多關于DPM的信息，可加鄒德裕大師微信carydy交流探討。

Q1：merge join、nested loops、hash join什么時候走什么樣的連接呢？

A1：Nested loops適合各種關聯條件的查詢，=,<>,>,<等等，主要是驅動行數少，被驅動的如果有高效索引，返回結果集不大的情況下高效，側重于CPU消耗。

HASH JOIN是必須要等值連接的，側重于大數據量運算，本次分享的巨慢SQL就是通過將OR子查詢通過SUBSTR函數構造等值連接，實現HASH JOIN運算，側重于內存消耗。

SORT MERGE JOIN主要適合<,>之類的大數據量運算，需要排序，側重于內存消耗。

Q2：收集統計信息用analyze還是dbms_stats？

A2：很顯然收集統計信息要用DBMS_STATS，ANALYZE有些功能DBMS_STATS沒有，比如validate structure等。

Q3：SQL第一次快，之后執行慢大概什么原因？

A3：這種問題需要具體分析了，如果是11g，大多是執行計劃頻繁變化導致的，11g有cardinality feedback和adaptive cursor sharing,BUG較多，經常會導致SQL忽快忽慢，可以通過執行計劃來進行分析，如果是這樣的原因，可以關閉此特性。如果不是新特性導致的，可以通過分析物理讀，邏輯讀，或者10046跟蹤來找出原因加以解決。

1、分享PPT下載：

點擊文末【閱讀原文】或登錄云盤：http://pan.baidu.com/s/1kVSLFlt，即可下載本次分享PPT。

2、直播鏈接：

https://m.qlchat.com/topic/220000485078915.htm?preview=Y&intoPreview=Y

密碼：006

好書相送

在本文微信訂閱號（dbaplus）評論區留下足以引起共鳴的真知灼見，并在本文發布后的隔天中午12點成為點贊數最多的1名，可獲得絕版譯著《Oracle核心技術》一本~