處理CBO的SQL優(yōu)化問題（圖文詳細說明）
發(fā)表時間：2023-09-10 來源：明輝站整理相關(guān)軟件相關(guān)文章人氣：
[摘要]本次分享大綱：CBO優(yōu)化器存在哪些坑CBO優(yōu)化器坑的解決之道加強SQL審核，將性能問題扼殺于襁褓之中分享現(xiàn)場FAQCBO( Cost Based Optimizer)優(yōu)化器是目前Oracle廣泛使用的優(yōu)化器，其使用統(tǒng)計信息、查詢轉(zhuǎn)換等計算各種可能的訪問路徑成本，并生成多種備選執(zhí)行計劃，最終Orac...
本次分享大綱：
CBO優(yōu)化器存在哪些坑
CBO優(yōu)化器坑的解決之道
加強SQL審核，將性能問題扼殺于襁褓之中
分享現(xiàn)場FAQ
CBO( Cost Based Optimizer)優(yōu)化器是目前Oracle廣泛使用的優(yōu)化器，其使用統(tǒng)計信息、查詢轉(zhuǎn)換等計算各種可能的訪問路徑成本，并生成多種備選執(zhí)行計劃，最終Oracle會選擇成本最低的作為最優(yōu)執(zhí)行計劃。與“遠古”時代的RBO(Rule Based Optimizer)相比，顯然更加符合數(shù)據(jù)庫實際情況，能夠適應(yīng)更多的應(yīng)用場景。但是，由于其自身非常復(fù)雜，CBO并未解決的實際問題以及存在的BUG非常多，在日常優(yōu)化過程中，你可能會遇到一些，不管怎么收集統(tǒng)計信息，都無法走正確執(zhí)行計劃的情形，這時候，你可能踩坑CBO了。
本次分享，主要以日常常見優(yōu)化器問題作為引子，一起探討CBO的那些坑的解決之道。
一、CBO優(yōu)化器存在哪些坑
先來看一下，CBO優(yōu)化器的組件：
從上圖可以看出，一條SQL進入ORACLE中，實際上經(jīng)過解析會將各部分進行分離，每個分離的部分獨立成為一個查詢塊(query blocks)，比如子查詢會成為一個查詢塊，外部查詢又是一個查詢塊，那么ORACLE優(yōu)化器要做的工作就是各查詢塊內(nèi)部走什么樣的訪問路徑更好（走索引、全表、分區(qū)？），其次就是各查詢塊之間應(yīng)該走什么樣的JOIN方式以及JOIN順序，最終計算出那種執(zhí)行計劃更好。
優(yōu)化器的核心就是查詢轉(zhuǎn)換器、成本估算器以及執(zhí)行計劃生成器。
Transformer(查詢轉(zhuǎn)換器)：
從圖上可以看出，優(yōu)化器的第一核心裝置就是查詢轉(zhuǎn)換器，查詢轉(zhuǎn)換器的主要作用就是研究各種查詢塊之間的關(guān)系，并從語法上甚至語義上給予SQL等價重寫，重寫后的SQL更容易被核心裝置成本估算器和執(zhí)行計劃生成器處理，從而利用統(tǒng)計信息生成最優(yōu)執(zhí)行計劃。
查詢轉(zhuǎn)換器在優(yōu)化器中有兩種方式：啟發(fā)式查詢轉(zhuǎn)換(基于規(guī)則)和基于COST的查詢轉(zhuǎn)換。 啟發(fā)式查詢轉(zhuǎn)換的一般是比較簡單的語句，基于成本的一般比較復(fù)雜，也就是說，符合基于規(guī)則的ORACLE不管什么情況下都會進行查詢轉(zhuǎn)換，不符合的ORACLE可能考慮基于成本的查詢轉(zhuǎn)換。啟發(fā)式查詢轉(zhuǎn)換歷史悠久，問題較少，一般查詢轉(zhuǎn)換過的效率比不經(jīng)過查詢轉(zhuǎn)換的要高，而基于成本的查詢轉(zhuǎn)換，因其與CBO優(yōu)化器緊密關(guān)聯(lián)，在10G引入，內(nèi)部非常復(fù)雜，所以BUG也比較多，在日常優(yōu)化過程中，各種疑難SQL，往往就出現(xiàn)在查詢轉(zhuǎn)換失敗中，因為查詢轉(zhuǎn)換一旦失敗，Oracle就不能將原始SQL轉(zhuǎn)換成結(jié)構(gòu)更良好的SQL（更易于被優(yōu)化器處理），顯然可選擇的執(zhí)行路徑就要少很多，比如子查詢不能UNNEST,那么，往往就是災(zāi)難的開始。其實，查詢轉(zhuǎn)換中Oracle做的最多的就是將各種查詢轉(zhuǎn)換成JOIN方式，這樣就可以利用各種高效的JOIN方法了，比如HASH JOIN。
查詢轉(zhuǎn)換共有30種以上的方式，下面列出一些常見啟發(fā)式和基于COST的查詢轉(zhuǎn)換。
啟發(fā)式查詢轉(zhuǎn)換（一系列的RULE）：
很多啟發(fā)式查詢轉(zhuǎn)換在RBO情況下就已經(jīng)存在。常見的有：
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 (謂詞傳遞）等技術(shù)。
基于COST的查詢轉(zhuǎn)換（通過COST計算）：
針對復(fù)雜的語句進行基于COST的查詢轉(zhuǎn)換，常見的有：
CVM (Complex view Merging 復(fù)雜視圖合并）、JPPD (Join predicate push-down 關(guān)聯(lián)謂詞推入）、DP (Distinct placement）、GBP（Group by placement）等技術(shù)。
通過一系列查詢轉(zhuǎn)換技術(shù)，將原始SQL轉(zhuǎn)為優(yōu)化器更容易理解和分析的SQL，從而能夠使用更多的謂詞、連接條件等，達到獲得最佳計劃的目的。查詢轉(zhuǎn)換的過程，可以通過10053獲取詳細信息。查詢轉(zhuǎn)換是否能夠成功和版本、優(yōu)化器限制、隱含參數(shù)、補丁等有關(guān)。
隨便在MOS上搜索一下查詢轉(zhuǎn)換，就會出現(xiàn)一堆BUG：
竟然還是Wrong result（錯誤的結(jié)果），遇到這種BUG不是性能問題了，而是嚴重的數(shù)據(jù)正確性問題，當(dāng)然，在MOS里隨便可以找到一堆這樣的BUG，但是，在實際應(yīng)用中，我相信，你可能碰到的較少，如果有一天，你看到一條SQL查詢的結(jié)果可能不對，那你也得大膽質(zhì)疑，對于Oracle這種龐然大物來說，遇到問題，質(zhì)疑是非常正確的思考方式，這種Wrong result問題，在數(shù)據(jù)庫大版本升級過程中可能見到，主要有兩類問題：
原來結(jié)果正確，現(xiàn)在結(jié)果錯誤。--遇到新版本BUG
現(xiàn)在結(jié)果正確，原來結(jié)果錯誤。--新版本修復(fù)了老版本BUG
第一種情況很正常，第二種情況也可能存在，我就看到過一客戶質(zhì)疑升級后的結(jié)果不正確，結(jié)果經(jīng)過查證之后，竟然是老版本執(zhí)行計劃就是錯的，新版本執(zhí)行計劃是正確的，也就是錯誤了很多年，都沒有發(fā)現(xiàn)，結(jié)果升級后是正確的，卻以為是錯了。
遇到錯誤結(jié)果，如果不是非核心功能，真的可能被深埋很多年。
Estimator( 估算器)：
很顯然，估算器會利用統(tǒng)計信息（表、索引、列、分區(qū)等）來估算對應(yīng)執(zhí)行計劃操作中的選擇性，從而計算出對應(yīng)操作的cardinality，生成對應(yīng)操作的COST，并最終計算整個計劃的COST。對于估算器來說，很重要的就是其估算模型的準確性以及統(tǒng)計信息存儲的準確性，估算的模型越科學(xué)，統(tǒng)計信息能反應(yīng)實際的數(shù)據(jù)分布情況，能夠覆蓋更多的特殊數(shù)據(jù)，那么生成的COST則更加準確。
然而，這是不可能的情況，估算器模型以及統(tǒng)計信息中存在諸多問題，比如針對字符串計算選擇性，ORACLE內(nèi)部會將字符串轉(zhuǎn)換為RAW類型，在將RAW類型轉(zhuǎn)換成數(shù)字，然后左起ROUND 15位，這樣會出現(xiàn)可能字符串相差很大的，由于轉(zhuǎn)換成數(shù)字后超過15位，那么內(nèi)部轉(zhuǎn)換后可能結(jié)果相近，最終導(dǎo)致計算的選擇性不準確。
Plan Generator( 計劃生成器)：
計劃生成器也就是分析各種訪問路徑、JOIN方法、JOIN順序，從而生產(chǎn)不同執(zhí)行計劃。那么如果這個部分出現(xiàn)問題，也就是對應(yīng)的部分可能算法不夠完善或者存在限制。比如JOIN的表很多，那么各種訪問順序的選擇成幾何級數(shù)增長，ORACLE內(nèi)部有限制值，也就是事實不可能全部計算一遍。
比如HASH JOIN算法是普遍做大數(shù)據(jù)處理的首選算法，但是由于HASH JOIN天生存在一種限制：HASH碰撞，一旦遇到HASH碰撞，必然導(dǎo)致效率大減。
CBO優(yōu)化器存在很多限制，詳細可以參考MOS：Limitations of the Oracle Cost Based Optimizer (文檔 ID 212809.1)。
二、CBO優(yōu)化器坑的解決之道
本部分主要分享下日常常見優(yōu)化器問題案例，有的問題不僅限于CBO優(yōu)化器，由于CBO是目前廣泛使用的優(yōu)化器，因此，一律納入CBO問題。
1 FILTER性能殺手問題
FILTER操作是執(zhí)行計劃中常見的操作，這種操作有兩種情況：
只有一個子節(jié)點，那么就是簡單過濾操作。
有多個子節(jié)點，那么就是類似NESTED LOOPS操作，只不過與NESTED LOOPS差別在于，F(xiàn)ILTER內(nèi)部會構(gòu)建HASH表，對于重復(fù)匹配的，不會再次進行循環(huán)查找，而是利用已有結(jié)果，提高效率。但是一旦重復(fù)匹配的較少，循環(huán)次數(shù)多，那么，F(xiàn)ILTER操作將是嚴重影響性能的操作，可能你的SQL幾天都執(zhí)行不完了。
下面看看各種情況下的FILTER操作：
單子節(jié)點：
很顯然ID=1的FILTER操作只有一個子節(jié)點ID=2，這種情況下的FILTER操作也就是單純的過濾操作。
多子節(jié)點：
FILTER多子節(jié)點往往就是性能殺手，主要出現(xiàn)在子查詢無法UNNEST查詢轉(zhuǎn)換，經(jīng)常遇到的情況就是NOT IN子查詢、子查詢和OR連用、復(fù)雜子查詢等情況。
（1）NOT IN子查詢中的FILTER
先來看下NOT IN情況：
針對上面的NOT IN子查詢，如果子查詢object_id有NULL存在，則整個查詢都不會有結(jié)果，在11g之前，如果主表和子表的object_id未同時有NOT NULL約束，或都未加IS NOT NULL限制，則ORACLE會走FILTER。11g有新的ANTI NA（NULL AWARE）優(yōu)化，可以對子查詢進行UNNEST，從而提高效率。
對于未UNNEST的子查詢，走了FILTER，有至少2個子節(jié)點，執(zhí)行計劃還有個特點就是Predicate謂詞部分有：B1這種類似綁定變量的東西，內(nèi)部操作走類似NESTED LOOPS操作。
11g有NULL AWARE專門針對NOT IN問題進行優(yōu)化，如下所示：
通過NULL AWARE操作，對無法UNNEST的NOT IN子查詢可以轉(zhuǎn)換成JOIN形式，這樣效率就大幅度提升了。如果在11g之前，遇到NOT IN無法UNNEST，那該怎么做呢？
以上四種方式，大部分情況下均能達到讓優(yōu)化器走JOIN的目的。
以上寫法執(zhí)行計劃都是一樣的，如下所示：
說白了，unnest subquery就是轉(zhuǎn)換成JOIN形式，如果能轉(zhuǎn)換成JOIN就可以利用高效JOIN特性來提高操作效率，不能轉(zhuǎn)換就走FILTER，可能影響效率，11g的NULL AWARE從執(zhí)行計劃里可以看出，還是有點區(qū)別，沒有走INDEX FULL SCAN掃描，因為沒有條件讓ORACLE知道object_id可能存在NULL，所以也就走不了索引了。
OK，現(xiàn)在來說一個數(shù)據(jù)庫升級過程中碰到的案例，背景是11.2.0.2升級到11.2.0.4后下面SQL出現(xiàn)性能問題：
執(zhí)行計劃如下：
這里的ID=4和ID=8兩個FILTER均有2個子節(jié)點，很顯然是NOT IN子查詢無法UNNEST導(dǎo)致的。上面說了在11g ORACLE CBO可以將NOT IN轉(zhuǎn)換成NULL AWARE ANTI JOIN，并且在11.2.0.2上是可以轉(zhuǎn)換的，到11.2.0.4上就不行了。兩個FILTER操作的危害到底有多大呢，可以通過查詢實際執(zhí)行計劃來看：
使用ALTER SESSION SET STATISTICS_LEVEL=ALL;截取2分25s的記錄查看實際情況，ID=9步驟的CARD=141行就需要2分25s,實際此步驟有：27w行
也就是這條SQL要運行10天以上了，簡直太恐怖了。
針對此問題的分析如下：
針對第一種情況：
參數(shù)是TRUE，顯然沒有問題。
針對第二種情況：
收集統(tǒng)計信息發(fā)現(xiàn)無效。
那么此時，只能寄希望于第三種情況：可能是BUG或者升級過程中修改了其它參數(shù)影響了無法走NULL AWARE ANTI JOIN。ORACLE BUG和參數(shù)那么多，那么我們怎么快速找到問題根源導(dǎo)致是哪個BUG或者參數(shù)導(dǎo)致的呢？這里給大家分享一個神器SQLT，全稱（SQLTXPLAIN），這是ORACLE內(nèi)部性能部門開發(fā)的工具，可以在MOS上下載，功能非常強勁。
回歸正題，現(xiàn)在要找出是不是新版本BUG或者修改了某個參數(shù)導(dǎo)致問題產(chǎn)生， 那么就要用到SQLT的高級方法：XPLORE。 XPLORE會針對ORACLE中的各種參數(shù)不停打開、關(guān)閉，來輸出執(zhí)行計劃，最終我們可以通過生成的報告，找到匹配的執(zhí)行計劃來判斷是BUG問題還是參數(shù)設(shè)置問題。
使用很簡單，參考readme.txt將需要測試的SQL單獨編輯一個文件，一般，我們測試都使用XPLAIN方法，調(diào)用EXPLAIN PLAN FOR進行測試，這樣保證測試效率。
SQLT 找出問題根源：
最終通過SQLT XPLORE找出問題根源在于新版本關(guān)閉了_optimier_squ_bottomup參數(shù)（和子查詢相關(guān)）。從這點上也可以看出來，很多查詢轉(zhuǎn)換能夠成功，不光是一個參數(shù)起作用，可能多個參數(shù)共同作用。因此，關(guān)閉默認參數(shù)，除非有強大的理由，否則，不可輕易修改其默認值。至此，此問題在SQLT的幫助下，快速得以解決，如果不使用SQLT，那么解決問題的過程顯然更為曲折，一般情況下，估計是讓開發(fā)先修改SQL了。
思考一下，原來的SQL是不是還可以更優(yōu)化呢？
很顯然，如果要進一步優(yōu)化，要徹底對SQL進行重寫，通過觀察，2個子查詢部分有相同點，經(jīng)過分析語義：查找表DT_MBY_TEST_LOG在指定INSERT_TIME范圍內(nèi)的，按照每個TBILL_ID取最小的INSERT_TIME，并且ID不在子查詢中，然后結(jié)果按照INSERT_TIME排序，最后取TOP 199。
原SQL使用自連接、兩個子查詢，冗余繁雜。自然想到用分析函數(shù)進行改寫，避免自連接，從而提高效率。改寫后的SQL如下：
執(zhí)行計劃：
至此，這條SQL從原來的走FILTER需要耗時10天，到找出問題根源可以走NULL AWARE ANTI JOIN需要耗時7秒多，最后通過徹底改寫耗時3.8s。
（2） OR子查詢中的FILTER
再來看下常見的OR與子查詢連用情況，在實際優(yōu)化過程中，遇到OR與子查詢連用，一般都不能unnest subquery了，可能會導(dǎo)致嚴重性能問題，OR與子查詢連用有兩種可能：
還是通過一個具體案例，分享下對于OR子查詢優(yōu)化的處理方式，在某庫11g R2中碰到如下SQL，幾個小時都沒有執(zhí)行完：
先來看下執(zhí)行計劃：
怎么通過看到這個執(zhí)行計劃，一眼定位性能慢的原因呢？主要通過下列幾點來分析定位：
執(zhí)行計劃中的Rows，也就是每個步驟返回的cardinality很少，都是幾行，在分析表也不是太大，那么怎么可能導(dǎo)致運行幾個小時都執(zhí)行不完呢？很大原因可能就在于統(tǒng)計信息不準，導(dǎo)致CBO優(yōu)化器估算錯誤，錯誤的統(tǒng)計信息導(dǎo)致錯誤的執(zhí)行計劃，這是第一點。
看ID=15到18部分，它們是ID=1 FILTER操作的第二子節(jié)點，第一子節(jié)點是ID=2部分，很顯然，如果ID=2部分估算的cardinality錯誤，實際情況很大的話，那么對ID=15到18部分四個表全掃描次數(shù)將會巨大，那么也就導(dǎo)致災(zāi)難產(chǎn)生。
很顯然，ID=2部分的一堆NESTED LOOPS也是很可疑的，找到ID=2操作的入口在ID=6部分，全表掃描DEALREC_ERR_201608，估算返回1行，很顯然，這是導(dǎo)致NESTED LOOPS操作的根源，因此，需要檢驗其準確性。
主表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部分針對DEALREC_ERR_201608表按照查詢條件substr(other_class, 1, 3) NOT IN (‘147’,‘151’, …)獲得的cardinality的準確性，也就是要收集統(tǒng)計信息。
然而發(fā)現(xiàn)使用size auto,size repeat,對other_class收集直方圖均無效果，執(zhí)行計劃中對other_class的查詢條件返回行估算還是1（實際2000w行）。
再次執(zhí)行后的執(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小時。
現(xiàn)在要解決的就是FILTER問題，對子查詢有OR條件的，簡單條件如果能夠查詢轉(zhuǎn)換，一般會轉(zhuǎn)為一個union all view后再進行semi join、anti join（轉(zhuǎn)換成union all view,如果謂詞類型不同，則SQL可能會報錯）。對于這種復(fù)雜的，優(yōu)化器就無法查詢轉(zhuǎn)換了，因此，改寫是唯一可行的方法。分析SQL，原來查詢的是同一張表，而且條件類似，只是取的長度不同，那么就好辦了！
如何讓帶OR的子查詢執(zhí)行計劃從FILTER變成JOIN。兩種方法：
1）改為UNION ALL/UNION
2）語義改寫.前面已經(jīng)使用語義改寫，內(nèi)部轉(zhuǎn)為了類似UNION的操作，如果要繼續(xù)減少表的訪問，則只能徹改寫OR條件，避免轉(zhuǎn)換為UNION操作。
再來分析下原始OR條件：
上面含義是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更加精簡，效率更高。改寫如下：
執(zhí)行計劃如下：
1）現(xiàn)在的執(zhí)行計劃終于變的更短，更易讀，通過邏輯改寫走了HASH JOIN， 最終一條返回300多萬行數(shù)據(jù)的SQL原先需要12小時運行的SQL，現(xiàn)在3分鐘就執(zhí)行完了。
2） 思考：結(jié)構(gòu)良好，語義清晰的SQL編寫，有助于優(yōu)化器選擇更合理的執(zhí)行計劃，所以說，寫好SQL也是門技術(shù)活。
通過這個案例，希望能給大家一些啟發(fā)，寫SQL如何能夠自己充當(dāng)查詢轉(zhuǎn)換器，編寫的SQL能夠減少表、索引、分區(qū)等的訪問，能夠讓ORACLE更易使用一些高效算法進行運算，從而提高SQL執(zhí)行效率。
其實，OR子查詢也不一定就完全不能unnest，只是絕大多數(shù)情況下無法unnest而已，請看下例：
不可unnest的查詢：
可以unnest的查詢：
這2條SQL的差別也就是將條件or id3 = id2-1000轉(zhuǎn)換成or id3-1000 = id2，前者不可以unnest,后者可以unnest，通過分析10053可以得知：
不可unnest的出現(xiàn)：
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的出現(xiàn)：
并且將SQL改寫為：
最終CBO先查詢T3條件，做個UNION ALL視圖，之后與T2關(guān)聯(lián)。從這里來看，對于OR子查詢的unnest要求比較嚴格，從這條語句分析，ORACLE可進行unnest必須要求對主表列不要進行運算操作，優(yōu)化器自身并未將+1000條件左移，正因為嚴格，所以大部分情況下，OR子查詢也就無法進行unnest了，從而導(dǎo)致各種性能問題。
（3）類FILTER問題
類FILTER問題主要體現(xiàn)在UPDATE關(guān)聯(lián)更新和標量子查詢中，雖然此類SQL語句中并未顯式出現(xiàn)FILTER關(guān)鍵字，但是內(nèi)部操作和FILTER操作如出一轍。
先看下UPDATE關(guān)聯(lián)更新：
這里需要更新14999行，執(zhí)行計劃如下：
ID=2部分是where exists選擇部分，先把需要更新的條件查詢出來，之后執(zhí)行UPDATE關(guān)聯(lián)子查詢更新，可以看到ID=5部分出現(xiàn)綁定變量：B1，顯然UPDATE操作就類似于原來的FILTER，對于選出的每行與子查詢表NEW_TAB關(guān)聯(lián)查詢，如果ID列重復(fù)值較少，那么子查詢執(zhí)行的次數(shù)就會很多，從而影響效率，也就是ID=5的操作要執(zhí)行很多次。
當(dāng)然，這里字段ID唯一性很強，可以建立UNIQUE INDEX，普通INDEX燈，這樣第5步就可以走索引了。這里為了舉例這種UPDATE的優(yōu)化方式，不建索引，也可以搞定這樣的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操作。
再來看看標量子查詢，標量子查詢往往也是引發(fā)嚴重性能問題的殺手：
標量子查詢的計劃和普通計劃的執(zhí)行順序不同，標量子查詢雖然在上面，但是它由下面的CUSTOMERS表結(jié)果驅(qū)動，每行驅(qū)動查詢一次標量子查詢（有CACHE例外），同樣類似FILTER操作。
如果對標量子查詢進行優(yōu)化，一般就是改寫SQL，將標量子查詢改為外連接形式（在約束和業(yè)務(wù)滿足的情況下也可改寫為普通JOIN）：
通過改寫之后效率大增，并且使用HASH JOIN算法。下面看一下標量子查詢中的CACHE（FILTER和UPDATE關(guān)聯(lián)更新類似），如果關(guān)聯(lián)的列重復(fù)值特別多，那么子查詢執(zhí)行次數(shù)就會很少，這時候效率會比較好：
標量子查詢和FILTER一樣，有CACHE，如上面的emp_a有108K的行，但是重復(fù)的department_id只有11,這樣只查詢只掃描11次，掃描子查詢表的次數(shù)少了，效率會提升。
針對FILTER性能殺手問題，主要分享這3點，當(dāng)然，還有很多其它值得注意的地方，這需要我們?nèi)粘６嗔粜暮头e累，從而熟悉優(yōu)化器一些問題的處理方法。
2 TABLE函數(shù)8168基數(shù)問題
此問題來源于binding in list問題，使用TABLE函數(shù)構(gòu)造傳入的逗號分隔的值作為子查詢條件，一般前端傳入的值都較少，但是實際上走了HASH JOIN操作，無法使用T表索引，一旦執(zhí)行頻率高，必然對系統(tǒng)影響較大，為什么ORACLE不知道TABLE函數(shù)傳入了很少的值呢？
進一步分析：
從上面結(jié)果看出，TABLE函數(shù)的默認行數(shù)是8168行(TABLE函數(shù)創(chuàng)建的偽表是沒有統(tǒng)計信息的)，這個值不小了，一般比實際應(yīng)用中的行數(shù)要多的多，經(jīng)常導(dǎo)致執(zhí)行計劃走hash join，而不是nested loop。怎么改變這種情況呢?當(dāng)然可以通過hint提示來改變執(zhí)行計劃了,對where in list，常常使用的hint有：
first_rows，index，cardinality，use_nl等。
這里特別介紹下cardinality(table alias,n) ，這個hint很有用， 它可以讓CBO優(yōu)化器認為表的行數(shù)是n，這樣就可以改變執(zhí)行計劃了�，F(xiàn)在改寫上面的查詢：
加了cardinality(tab,5)自動走CBO優(yōu)化器了，優(yōu)化器把表的基數(shù)看成5，前面的where in list查詢基數(shù)默認為8168的時候走的是hash join，現(xiàn)在有了cardinality，趕緊試試：
現(xiàn)在走NESTED LOOPS操作，子節(jié)點可以走INDEX RANGE SCAN，邏輯讀從184變成7，效率提升數(shù)十倍。當(dāng)然，實際應(yīng)用中，最好不要加hints,可以使用SQL PROFILER綁定。
3 選擇性計算不準確問題
Oracle內(nèi)部計算選擇性都是以數(shù)字格式計算，因此，遇到字符串類型，會將字符串轉(zhuǎn)換成RAW類型，再將RAW類型轉(zhuǎn)換成數(shù)字，并且ROUND到左起15位，這樣對于轉(zhuǎn)換后的數(shù)字很大，可能原來字符串相差比較大的，內(nèi)部轉(zhuǎn)換后的數(shù)字比較接近，這樣就會引起選擇性計算不準確問題。如下例：
執(zhí)行計劃如下：
SQL執(zhí)行計劃走TEM_ID索引，需要運行1小時以上，計劃中對應(yīng)步驟cardinality很少（幾十級別），實際很大（百萬級別），判斷統(tǒng)計信息出錯。
為什么走錯索引？
由于TEM_ID是CHAR字符串類型，長度20，CBO內(nèi)部計算選擇性會先將字符串轉(zhuǎn)為RAW，然后RAW轉(zhuǎn)為數(shù)字，左起ROUND 15位。因此，可能字符串值差別大的，轉(zhuǎn)換成數(shù)字后值接近（因為超出15位補0），導(dǎo)致選擇性計算錯誤。以TS_TEM_INFO_DEAD中的TEM_ID列為例：
而實際根據(jù)條件查詢出的行數(shù) 29737305。因此，索引走錯了。
解決方法：
收集TEM_ID列直方圖，由于內(nèi)部算法有一定限制，導(dǎo)致值不同的字符串，內(nèi)部計算值可能一致，所以收集直方圖后，針對字符串值不同，但是轉(zhuǎn)換成數(shù)字后相同的，ORACLE會將實際值存儲到ENDPOINT_ACTUAL_VALUE中，用于校驗，提高執(zhí)行計劃的準確性。走正確索引GPYTM_ID后，運行時間從1小時以上到5s內(nèi)。
4 新特性引發(fā)執(zhí)行出錯問題
每個版本都會引入很多新特性，對于新特性，使用不當(dāng)可能會引發(fā)一些嚴重問題，常見的比如ACS、cardinality feedback導(dǎo)致執(zhí)行計劃變動頻繁，影響效率，子游標過多等，所以，針對新特性需要謹慎使用，包括前面說的11g null aware anti join也存在很多BUG。
今天要分析的案例是10g到11g大版本升級過程中遇到的SQL，在10g中正常運行，但是到11g中卻執(zhí)行出錯。 SQL如下：
10g正常，升級11g r2后日期轉(zhuǎn)換出錯,temp_value_code存多種格式字符串。正確執(zhí)行計劃LT關(guān)聯(lián)查詢先執(zhí)行，之后與外表關(guān)聯(lián)。錯誤執(zhí)行計劃是TASK_SPRING_VALUES先與外表關(guān)聯(lián)然后分組，作為VIEW再與TASK_SPRING_LABEL關(guān)聯(lián)，再次進行分組，這里有2個GROUP BY操作，與10g執(zhí)行計劃中只有1個GROUP BY操作不同，最終導(dǎo)致報錯。
很顯然，對于為什么出現(xiàn)兩個GROUP BY操作，需要進行研究，首選10053：
分析按照10053操作，是否找到非日期格式值：
的確找到非yyyy-mm-dd格式字符串，因此，to_date操作失敗。通過10053可以看出，這里使用了Group by/Distinct Placement操作，因此，需要找到對應(yīng)的控制參數(shù)，關(guān)閉此查詢轉(zhuǎn)換。
關(guān)閉GBP隱含參數(shù)后正確：_optimizer_group_by_placement。正確執(zhí)行計劃如下：
思考： 這個問題的本質(zhì)在于字段用途設(shè)計不合理，其中temp_value_code作為varchar2存儲普通字符、數(shù)字型字符、日期格式y(tǒng)yyy-mm-dd，程序中有to_number,to_date等轉(zhuǎn)換，非常依賴于執(zhí)行計劃中表連接和條件的先后順序。所以，良好的設(shè)計很重要，特別要保證各關(guān)聯(lián)字段類型的一致性以及字段作用的單一性，符合范式要求。
5 坑爹寫法CBO無能為力
結(jié)構(gòu)優(yōu)良的SQL能夠更易被CBO理解，從而更好地進行查詢轉(zhuǎn)換操作，從而為后續(xù)生成最佳執(zhí)行計劃打下基礎(chǔ)，然后實際應(yīng)用過程中，因為不注重SQL寫法，導(dǎo)致CBO也無能為力。下面以分頁寫法案例作為探討。
低效分頁寫法：
原寫法最內(nèi)層根據(jù)use_date等條件查詢，然后排序，獲取rownum并取別名，最外層使用rn規(guī)律。問題在哪？
分頁寫法如果直接<,<=可在排序后直接rownum獲取（兩層嵌套），如果需要獲取區(qū)間值，在最外層獲取>,>=（三層嵌套）。
此語句獲取<=,而使用三層嵌套，導(dǎo)致無法使用分頁查詢STOPKEY算法，因為rownum會阻止謂詞推入，導(dǎo)致執(zhí)行計劃中沒有STOPKEY操作。
<=分頁只需要2層嵌套，done_date列有索引，根據(jù)條件done_date>to_date(‘20150916’,‘YYYYMMDD’)和只獲取前20行，可高效利用索引和STOPKEY算法，改寫完成后使用索引降序掃描，執(zhí)行時間從1.72s到0.01s,邏輯IO 從42648到59，具體如下：
高效分頁寫法應(yīng)該符合規(guī)范，并且能夠充分利用索引消除排序。
6 CBO BUG問題
CBO BUG出現(xiàn)比較多的就是在查詢轉(zhuǎn)換中，一旦出現(xiàn)BUG，可能查找就比較困難，這時候應(yīng)該通過分析10053或者通過使用SQLT XPLORE快速找到問題根源。如下例：
這個表的oper_type有索引，并且條件oper_type>’D’ or oper_type<’D’走索引較好，但是實際上Oracle卻走了全表掃描，通過SQLT XPLORE快速分析：
其中上面2個是走索引的執(zhí)行計劃，點進去：
很顯然，_fix_control=8275054很可疑，通過查詢MOS：
轉(zhuǎn)換成a<>b，很顯然使用不了索引了，可以通過關(guān)閉此8275054解決。
7 HASH碰撞問題
HASHJOIN是專門用來做大數(shù)據(jù)處理的高效算法，并且只能用于等值連接條件，針對表build table（hash table）和probe table構(gòu)建HASH運算，查找滿足條件的結(jié)果集。
一般格式如下：
HASH JOIN
build table
probe table
這里的build table應(yīng)該選擇通過過濾條件過濾后，結(jié)果集尺寸較小的表（size不是rows），然后按照連接條件進行HASH函數(shù)運算，把需要的列和HASH函數(shù)運算結(jié)果存儲到hash bucket中，hash bucket自身是鏈表結(jié)構(gòu)。同樣，對于probe table也需要進行hash函數(shù)運算，并根據(jù)運算結(jié)果到build table的hash bucket中去查詢，查到滿足，查不到丟棄。當(dāng)然，ORACLE HASH JOIN內(nèi)部構(gòu)造還是很復(fù)雜的，具體可以參考Jonathan Lewis的CBO原理書。
HASH查找天生存在的問題：
一旦build table的連接條件列選擇性不好（也就是重復(fù)值特別多），那么某些hash bucket上可能存儲大量數(shù)據(jù)，由于hash bucket自身是鏈表結(jié)構(gòu)，那么當(dāng)查詢這些hash bucket時，效率會急劇下降，此問題就是HASH運算的經(jīng)典問題Hash Collision（HASH碰撞）。
下面用一個小例子來分析下hash碰撞：
其中 a 表 61w 多條記錄， b 表 7w 多條記錄，此 SQL 結(jié)果返回 8w 多條記錄，從執(zhí)行計劃來看，做 HASH JOIN 運算沒有什么問題，但是實際此 SQL 執(zhí)行 10 多分鐘都沒有執(zhí)行完，效率非常低下， CPU 使用率突增，遠遠大于訪問兩個表的時間。
如果你了解HASHJOIN，這時候，你應(yīng)當(dāng)考慮是不是遇到hash collision了，如果很多bucket上存儲大量數(shù)據(jù)，那么對于這樣的hash bucket里的數(shù)據(jù)查找那就類似于nested loops了，必然效率大減。如下進一步分析：
查找一下大于重復(fù)數(shù)據(jù)大于3000條的值，果然有很多，當(dāng)然剩下數(shù)據(jù)也有很多比較大，探測HASH JOIN，可以使用EVENT 10104：
可以看到存儲100行+的bucket有61個，而且最多的一個bucket中存儲了3782條，也就是和我們查詢出來的一致。還是回到原始SQL：
Oralce為什么選擇substr(b.object_name,1,2)來構(gòu)建HASH表呢，如果能將OR展開，原始SQL改為一個UNION ALL形式的，那么HASH表可以采用substr(b.object_name,1,2)和b.object_id以及data_object_id來構(gòu)建，那么必然唯一性很好，那應(yīng)該可以解決hash collision問題，改寫如下：
現(xiàn)在的SQL執(zhí)行時間從原來的10幾分鐘都沒有結(jié)果，到4s執(zhí)行完畢，再來看內(nèi)部構(gòu)建的HASHTABLE信息：
最多的一個bucket中只存儲6條數(shù)據(jù)，那肯定性能比前面好很多了。Hash碰撞的危害很大，實際應(yīng)用中，可能比較復(fù)雜，如果遇到hash碰撞問題，最好的方式就是進行SQL重寫，盡量從業(yè)務(wù)上分析，能不能增加其它選擇性比較好的列進行JOIN。
回頭來看看，既然我都知道改寫成UNION ALL后，就采用2個組合列構(gòu)建比較好的HASH表，那么 Oracle 為什么不這樣做呢？很簡單，我這里只是舉例刻意這么做的而已，用以說明HASH碰撞的問題，對于這種簡單SQL，有選擇性更好的列，收集下統(tǒng)計信息，Oracle就可以將的SQL進行OR展開了。
三、加強SQL審核，解決性能問題于襁褓之中
應(yīng)用系統(tǒng)SQL眾多，如果總是作為救火隊員角色解決線上問題，顯然不能滿足當(dāng)今IT系統(tǒng)高速發(fā)展的需求，基于數(shù)據(jù)庫的系統(tǒng)，主要性能問題在于SQL語句，如果能在開發(fā)測試階段就對SQL語句進行審核，找出待優(yōu)化SQL，并給予智能化提示，快速輔助優(yōu)化，則可以避免眾多線上問題。另外，還可以對線上SQL語句進行持續(xù)監(jiān)控，及時發(fā)現(xiàn)性能存在問題的語句，從而達到SQL的全生命周期管理目的。
為此，公司結(jié)合多年運維和優(yōu)化經(jīng)驗，自主研發(fā)了SQL審核工具，極大提升SQL審核優(yōu)化和性能監(jiān)控處理效率。
SQL審核工具采用四步法則：SQL采集—SQL分析—SQL優(yōu)化—上線跟蹤，SQL審核四步法區(qū)別傳統(tǒng)的SQL優(yōu)化方法，它著眼于系統(tǒng)上線前的SQL分析和優(yōu)化，重點解決SQL問題于系統(tǒng)上線前，扼殺性能問題于襁褓之中。如下圖所示：
通過SQL性能管理平臺可解決下列問題：
事前 ： 上線前 SQL 性能審核，扼殺性能問題于襁褓之中；
事中：SQL性能監(jiān)控處理，及時發(fā)現(xiàn)上線后SQL性能發(fā)生的變化，在SQL性能變化并且沒有引起嚴重問題時，及時解決；
事后：TOPSQL監(jiān)控，及時告警處理。
SQL性能管理平臺實現(xiàn)了SQL性能的360度全生命周期管控，并且通過各種智能化提示和處理，將絕大多數(shù)本來因SQL引發(fā)的性能問題，解決在問題發(fā)生之前，提高系統(tǒng)穩(wěn)定度。
下面是SQL審核的一個典型案例：
執(zhí)行計劃如下：
原SQL執(zhí)行1688s。通過SQL審核智能優(yōu)化準確找到優(yōu)化點—分區(qū)列有類型轉(zhuǎn)換。 優(yōu)化后0.86s。
SQL審核是新炬數(shù)據(jù)庫性能管理平臺DPM的一個模塊，想了解更多關(guān)于DPM的信息，可加鄒德裕大師（微信：carydy）交流探討。
今天主要和大家分享了一些Oracle優(yōu)化器中存在的問題以及常見問題解決方法，當(dāng)然，優(yōu)化器問題不僅限于今天分享的，雖然CBO非常強大，并且在12c中有巨大改進，但是，存在的問題也很多，只有平時多積累和觀察，掌握一定的方法，在能在遇到問題事后運籌帷幄，決勝千里。
Q&A
Q1： hash join是不是有排序，可以簡單說說hash join的原理嗎？
A1： ORACLE HASH JOIN自身不需要排序，這是區(qū)別SORTMERGE JOIN特點之一。ORACLE HASH JOIN原理比較復(fù)雜，可以參考Jonathan Lewis的Cost-Based Oracle Fundamentals的HASH JOIN部分，針對HASHJOIN最重要的是在原理基礎(chǔ)上搞清楚什么時候會慢，比如HASH_AREA_SIZE過小，HASH TABLE不能完全放到內(nèi)存中，那么會發(fā)生磁盤HASH運算，再比如上面講的HASH碰撞發(fā)生。
Q2： 什么時候不走索引？
A2： 不走索引情況比較多，首要的原因就是統(tǒng)計信息不準導(dǎo)致的，第二原因就是選擇性太低，走索引比走全掃效率更差，還有一個比較常見的就是對索引列進行了運算，導(dǎo)致無法走索引。其它還有很多原因會導(dǎo)致不能走索引，詳細參考MOS文檔：Diagnosing Why a Query is Not Using an Index (文檔 ID 67522.1)。
以上就是解決CBO的SQL優(yōu)化問題（圖文詳解）的詳細內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！
學(xué)習(xí)教程快速掌握從入門到精通的SQL知識。
