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

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

大綱如下：

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

1. 了解SQL結(jié)構(gòu)：SQL中使用了哪些語法，這些語法是不是經(jīng)常會導(dǎo)致性能問題，比如標(biāo)量子查詢的濫用。

2. 獲取執(zhí)行計劃：執(zhí)行計劃反應(yīng)了SQL的執(zhí)行路徑，直接影響了SQL的執(zhí)行效率。如何從執(zhí)行計劃中找出問題，是SQL Tuning的關(guān)鍵。

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

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

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

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

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

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

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

對于一般的SQL優(yōu)化，必須得分析SQL的語法結(jié)構(gòu)，語義以及解讀SQL執(zhí)行計劃，以SQL執(zhí)行計劃為基準(zhǔn)，分析執(zhí)行計劃中的問題，來進(jìn)行SQL Tuning，基本能解決大部分SQL優(yōu)化問題了。

當(dāng)然，以我的理解，SQL優(yōu)化不僅需要很強(qiáng)的邏輯思維、正確的理論指導(dǎo)、各種SQL語法的精通、熟悉index的使用、了解CBO相關(guān)內(nèi)容，甚至還需從大局觀進(jìn)行把控：物理模型的設(shè)計以及對具體的業(yè)務(wù)分析。

SQL執(zhí)行計劃作為SQL優(yōu)化的一把鑰匙，必須要很好地利用起來。經(jīng)?？吹介_發(fā)人員喜歡用PL/SQL Developer之類的工具來看執(zhí)行計劃。這里我得提醒下，這種內(nèi)部調(diào)用的是Explain Plan For，可能不夠準(zhǔn)確，特別是有綁定變量的情況下，最重要的一點，對于長的SQL執(zhí)行計劃，簡直沒法進(jìn)行分析。個人還是喜歡文本類型的執(zhí)行計劃，特別是真實的執(zhí)行計劃，能獲取A-ROWS，E-ROWS這些指標(biāo)的執(zhí)行計劃，讓我對執(zhí)行計劃中的問題一覽無余，特別對于巨慢的SQL，也可以運(yùn)行個幾分鐘中斷后獲取部分信息來協(xié)助判斷。

執(zhí)行計劃要點如下：

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

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

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

• 重操作：執(zhí)行計劃中的Operation和Predicate部分是需要關(guān)注的核心內(nèi)容，從操作中看出不合理部分，以此建立正確索引等優(yōu)化措施。

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

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

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

• 問題：為什么要尋找執(zhí)行計劃入口？為什么要分析執(zhí)行計劃各步驟順序和關(guān)系？

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

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

• 重操作、求真實、輕成本是通過執(zhí)行計劃優(yōu)化SQL的重要方法。

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

• 主表DEALREC_ERR_201608在ID=6查詢條件中經(jīng)查要返回2000w行，計劃中估算只有1行，因此，會導(dǎo)致Nested Loops次數(shù)實際執(zhí)行千萬次，導(dǎo)致效率低下。應(yīng)該走HASH JOIN，需要更新統(tǒng)計信息。

• 另外ID=1是Filter，它的子節(jié)點是ID=2和ID=15、16、17、18，同樣的ID 15-18也被驅(qū)動千萬次。

找出問題根源后，逐步解決。

第一次分析：解決ID=6步驟估算的cardinality不準(zhǔn)確問題。

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

• cardinality的計算與謂詞緊密相關(guān)，所以要找出ID=6的謂詞，根據(jù)謂詞手動計算真實card與估算card之間的區(qū)別。

• 嘗試收集統(tǒng)計信息，檢驗效果。

現(xiàn)在的問題，也就是轉(zhuǎn)為對表DEALREC_ERR_201608統(tǒng)計信息準(zhǔn)確性的問題，特別是統(tǒng)計信息對謂詞計算的準(zhǔn)確性。

• 嘗試更新統(tǒng)計信息：

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

怎么辦？思緒萬千，靈光乍現(xiàn)！

還有更好的辦法嗎？

突然想起11g有個統(tǒng)計信息收集新特性：擴(kuò)展統(tǒng)計信息收集。

擴(kuò)展統(tǒng)計信息一收集，執(zhí)行計劃如下：

• DEALREC_ERR_201608與B_DEALING_DONE_TYPE原來走NL的現(xiàn)在正確走HASH JOIN。Build table是小結(jié)果集，probe table是ERR表大結(jié)果集，正確。

• 但是ID=2與ID=11到14，也就是與TMI_NO_INFOS的OR子查詢，還是FILTER,驅(qū)動數(shù)千萬次子節(jié)點查詢，下一步優(yōu)化要解決的問題。

• 性能從12小時到2小時。到這里結(jié)束了嗎？

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

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

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

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

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

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

很顯然不對，直方圖只是反應(yīng)數(shù)據(jù)的分布，數(shù)據(jù)的分布正確，對應(yīng)謂詞可以查詢出比較準(zhǔn)確的cardinality，從而影響執(zhí)行計劃，所以對全表也是有用的。

• 疑問4：收集或更新了統(tǒng)計信息，執(zhí)行計劃怎么變得更差了？

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

• 疑問5：執(zhí)行計劃中cardinality顯示的和已有統(tǒng)計信息計算不一致？

Oracle CBO內(nèi)部算法很復(fù)雜，而且Bug眾多，遇到問題要大膽懷疑。

• 疑問6：統(tǒng)計信息應(yīng)該按照Oracle建議自動收集？

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

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

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

• 疑問8：我需要根據(jù)統(tǒng)計信息以及CBO公式去計算COST嗎？

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

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

Filter形成于查詢轉(zhuǎn)換期間，如果對于子查詢無法進(jìn)行unnest轉(zhuǎn)換來消除子查詢，則會走Filter。走Filter說明子查詢是受外表結(jié)果驅(qū)動，類似循環(huán)操作！很顯然，如果驅(qū)動的次數(shù)越多，效率越低！

查詢轉(zhuǎn)換是能夠生成高效SQL執(zhí)行計劃的重要步驟，查詢轉(zhuǎn)換不能做好，后面的很多執(zhí)行路徑就沒法走了。掌握查詢轉(zhuǎn)換機(jī)制，對如何寫高效的SQL，調(diào)優(yōu)SQL至關(guān)重要，了解的越深，對CBO就越了解。

下面是CBO組件圖，熟悉對應(yīng)組件是SQL優(yōu)化必須的內(nèi)容：

• Filter什么時候高效？

Filter本身會構(gòu)建HASH表來保存輸入/輸出對，以備后續(xù)減少子查詢執(zhí)行次數(shù)，這是與純粹Nesed Loops操作的典型區(qū)別，比如from a where a.status In(select b.staus from b…)。 如果status前面已經(jīng)查過，則后續(xù)不需要再次執(zhí)行子查詢，而是直接從保存的HASH表中獲取結(jié)果，這樣減少了子查詢執(zhí)行次數(shù)，從而提高效率。也就是說，如果子查詢關(guān)聯(lián)條件的重復(fù)值很多，F(xiàn)ilter還是有一定的優(yōu)勢，否則就是災(zāi)難！

• Filter與push_subq hints

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

• 簡化前面的語句關(guān)鍵部分如下：

• Oracle內(nèi)部改寫如下，無法unnest,如果unnest：

執(zhí)行計劃如下：

從執(zhí)行計劃里可以看到，F(xiàn)ilter多子節(jié)點一般有如下特點：

1. 自動生成的綁定變量：B1，因為需要執(zhí)行循環(huán)操作

2. 轉(zhuǎn)為EXISTS

所以，以后看到有自動生成的綁定變量的執(zhí)行計劃，都是類似Filter的操作，比如標(biāo)量子查詢，UPDATE關(guān)聯(lián)子查詢，優(yōu)化的話，都需要干掉（類）Filter來優(yōu)化。

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

• 含有OR的子查詢，經(jīng)常性無法unnest，Oracle大多無法給轉(zhuǎn)換成UNION/UNION ALL形式的查詢

• 所以，針對這樣的語句優(yōu)化：

  1）改寫為UNION/UNION ALL形式

  2）根據(jù)語義、業(yè)務(wù)含義徹底重寫

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

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

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

• OR擴(kuò)展存在缺點，大表還是多次被訪問，還能繼續(xù)優(yōu)化嗎？

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

追本溯源，從SQL含義出發(fā)，上面含義是ERR表的TMISID截取前8,9,10,11位與TMI_NO_INFOS.BILLID_HEAD匹配，對應(yīng)匹配BILLID_HEAD長度正好為8,9,10,11。很顯然，語義上可以這樣改寫：

ERR表與TMI_NO_INFOS表關(guān)聯(lián)，ERR.TMISID前8位與ITMI_NO_INFOS.BILLID_HEAD長度在8-11之間的前8位完全匹配，在此前提下，TMISID like  BILLID_HEAD||’%’。

現(xiàn)在就動手徹底改變多個OR子查詢，讓SQL更加精簡，效率更高。

通過上一節(jié)的思路，改寫SQL如下：

執(zhí)行計劃如下：

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

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

• 一條SQL執(zhí)行12分鐘沒有結(jié)果：其中object_id有索引，從查詢結(jié)構(gòu)來看，內(nèi)層查詢完全可以獨立執(zhí)行（最多100行），然后與外層的表進(jìn)行關(guān)聯(lián)，走NL，這樣可以利用到object_id索引，然而，事與愿違，ID=4出現(xiàn)Filter，這樣內(nèi)層查詢會驅(qū)動N次，問題出在何處？

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

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

• 通過改寫消除Filter運(yùn)算如下：

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

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

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

執(zhí)行計劃如下所示：

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

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

跑一下XPLORE，只需要調(diào)用XPLAIN方法即可，提高效率，不實際執(zhí)行SQL：

可以看到和對應(yīng)的隱含參數(shù)_optimizer_squ_bottomup設(shè)置有關(guān)，這是一個和子查詢的查詢轉(zhuǎn)換有關(guān)的隱含參數(shù)。

修正之后的執(zhí)行計劃：

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

SQLT XPLORE的一些限制：

• 只能單個參數(shù)測試是否有效；

• 做XPLORE使用XPLAIN方法，內(nèi)部調(diào)用explain plan for,不需要執(zhí)行從而提高效率和避免修改數(shù)據(jù)；

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

• 獲取問題SQL制定優(yōu)化目標(biāo)

從AWR、ASH、SQL CHECK SCRIPTS等主動發(fā)現(xiàn)有問題的SQL、用戶報告有性能問題時DBA介入等，通過對SQL的執(zhí)行情況分析，制定SQL的優(yōu)化目標(biāo)。

• 檢查執(zhí)行計劃

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

• 檢查統(tǒng)計信息

Oracle使用DBMS_STATS包對統(tǒng)計信息進(jìn)行管理，涉及系統(tǒng)統(tǒng)計信息、表、列、索引、分區(qū)等對象的統(tǒng)計信息，統(tǒng)計信息是SQL能夠走正確執(zhí)行計劃的保證。

• 檢查高效訪問結(jié)構(gòu)

重要的訪問結(jié)構(gòu)，諸如索引、分區(qū)等能夠快速提高SQL執(zhí)行效率。表存儲的數(shù)據(jù)本身，如碎片過多、數(shù)據(jù)傾斜嚴(yán)重、數(shù)據(jù)存儲離散度大，也會影響效率。

• 檢查影響優(yōu)化器的參數(shù)

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

• 優(yōu)化器新特性、Bug

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

• SQL語句編寫問題

SQL語句結(jié)構(gòu)復(fù)雜、使用了不合理的語法，比如UNION代替UNION ALL可能導(dǎo)致性能低下。

• 優(yōu)化器限制

無法收集準(zhǔn)確的統(tǒng)計信息、無法正確進(jìn)行查詢轉(zhuǎn)換操作等，如SEMI JOIN、ANTI JOIN與or連用會走Filter操作。

• 其他

主要涉及設(shè)計問題，如應(yīng)用在業(yè)務(wù)高峰期運(yùn)行，實際上可以放到較空閑狀態(tài)運(yùn)行。表、索引、分區(qū)等設(shè)計不合理。

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

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

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

審核頁面展現(xiàn)詳細(xì)SQL審核情況：

SQL審核結(jié)果多維護(hù)分析：

優(yōu)化建議詳細(xì)準(zhǔn)確：

內(nèi)置上百種規(guī)則集，可按需選擇：

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

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

• 事中：SQL性能監(jiān)控處理，及時發(fā)現(xiàn)上線后SQL性能發(fā)生的變化，在SQL性能變化并且沒有引起嚴(yán)重問題時，及時解決。

• 事后：核心SQL監(jiān)控，及時告警處理。

SQL性能管理平臺實現(xiàn)了SQL性能的360度全生命周期管控，并且通過各種智能化提示和處理，將絕大多數(shù)本來因SQL引發(fā)的性能問題，解決在問題發(fā)生之前，提高系統(tǒng)穩(wěn)定度。

另外對SQL性能的分析，從SQL寫法、SQL執(zhí)行信息、執(zhí)行計劃、統(tǒng)計信息等多方面定義規(guī)則，多維度進(jìn)行分析，提供智能化的建議，提升優(yōu)化速度和準(zhǔn)確性。

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

SQL審核是新炬網(wǎng)絡(luò)數(shù)據(jù)庫性能管理平臺DPM的一個模塊，大家若想了解更多關(guān)于DPM的信息，可加鄒德裕大師微信carydy交流探討。

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

A1：Nested loops適合各種關(guān)聯(lián)條件的查詢，=,<>,>,<等等，主要是驅(qū)動行數(shù)少，被驅(qū)動的如果有高效索引，返回結(jié)果集不大的情況下高效，側(cè)重于CPU消耗。

HASH JOIN是必須要等值連接的，側(cè)重于大數(shù)據(jù)量運(yùn)算，本次分享的巨慢SQL就是通過將OR子查詢通過SUBSTR函數(shù)構(gòu)造等值連接，實現(xiàn)HASH JOIN運(yùn)算，側(cè)重于內(nèi)存消耗。

SORT MERGE JOIN主要適合<,>之類的大數(shù)據(jù)量運(yùn)算，需要排序，側(cè)重于內(nèi)存消耗。

Q2：收集統(tǒng)計信息用analyze還是dbms_stats？

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

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

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

1、分享PPT下載：

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

2、直播鏈接：

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

密碼：006

好書相送

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