本文根據dbaplus社群第93期線上分享整理而成。
講師介紹
丁俊
新炬網絡首席性能優化專家
sql稽核産品經理
dbaplus社群聯合發起人，《劍破冰山-oracle開發藝術》副主編。
oracle acea，itpub開發版資深版主，十年電信行業從業經驗。
本次分享大綱：
cbo優化器存在哪些坑
cbo優化器坑的解決之道
加強sql稽核，将性能問題扼殺于襁褓之中
分享現場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問題，在資料庫大版本更新過程中可能見到，主要有兩類問題：
原來結果正确，現在結果錯誤。--遇到新版本bug
現在結果正确，原來結果錯誤。--新版本修複了老版本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問題。
1 filter性能殺手問題
filter操作是執行計劃中常見的操作，這種操作有兩種情況：
隻有一個子節點，那麼就是簡單過濾操作。
有多個子節點，那麼就是類似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點，當然，還有很多其它值得注意的地方，這需要我們日常多留心和積累，進而熟悉優化器一些問題的處理方法。
2 table函數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，具體如下：
高效分頁寫法應該符合規範，并且能夠充分利用索引消除排序。
6 cbo 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解決。
7 hash碰撞問題
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)。
原文釋出時間為：2017-02-27
本文來自雲栖社群合作夥伴dbaplus