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

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

大綱如下：

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

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

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

言歸正傳，先揭開(kāi)巨慢SQL的神秘面紗：

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

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

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

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

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

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

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

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

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

執(zhí)行計(jì)劃要點(diǎn)如下：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

找出問(wèn)題根源后，逐步解決。

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

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

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

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

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

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

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

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

還有更好的辦法嗎？

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

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

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

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

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

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

• 疑問(wèn)1：100%收集為什么還沒(méi)有走正確執(zhí)行計(jì)劃？

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

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

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

• 疑問(wèn)3：直方圖只對(duì)走索引的有作用？

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

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

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

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

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

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

具體問(wèn)題具體分析，是讓Oracle自動(dòng)還是自己寫(xiě)腳本收集，都需要長(zhǎng)期實(shí)踐總結(jié)，對(duì)于一個(gè)復(fù)雜系統(tǒng)來(lái)說(shuō)采樣比例和method_opt很多需要定制設(shè)置。

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

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

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

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

• 為什么會(huì)形成Filter操作？（多子節(jié)點(diǎn)，單子節(jié)點(diǎn)純粹過(guò)濾操作）

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

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

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

• Filter什么時(shí)候高效？

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

• Filter與push_subq hints

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

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

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

執(zhí)行計(jì)劃如下：

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

1. 自動(dòng)生成的綁定變量：B1，因?yàn)樾枰獔?zhí)行循環(huán)操作

2. 轉(zhuǎn)為EXISTS

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

這里的例子其實(shí)是一個(gè)CBO的限制：

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

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

  1）改寫(xiě)為UNION/UNION ALL形式

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

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

• 回到原來(lái)的SQL中，看如何改寫(xiě)，通過(guò)分析，可以改為JOIN形式：

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

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

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

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

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

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

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

執(zhí)行計(jì)劃如下：

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

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

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

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

• 從10053中可以看出，查詢轉(zhuǎn)換失敗，因?yàn)橛龅搅藃owid,當(dāng)然把Rowid改別名是可以，但是此SQL要求必須用Rowid名字。

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

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

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

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

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

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

下面看看使用SQLT的XPLORE來(lái)找出問(wèn)題，先來(lái)看下SQLT介紹：

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

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

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

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

SQLT XPLORE的一些限制：

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

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

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

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

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

• 檢查執(zhí)行計(jì)劃

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

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

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

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

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

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

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

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

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

• SQL語(yǔ)句編寫(xiě)問(wèn)題

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

• 優(yōu)化器限制

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

• 其他

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

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

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

首頁(yè)審核總體情況一覽無(wú)余：

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

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

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

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

SQL性能管理平臺(tái)必須解決事前事中事后的SQL全生命周期管理問(wèn)題。

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

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

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

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

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

SQL性能管理平臺(tái)特點(diǎn)-自動(dòng)化采集、分析、跟蹤，減少DBA分析時(shí)間，提高管控效率：

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

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

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

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

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

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

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

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

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

1、分享PPT下載：

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

2、直播鏈接：

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

密碼：006

好書(shū)相送

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