604      技術社區[雲棲]

記一次區域DB突發變慢的SQL優化博弈

作者介紹

黃浩，現任職於中國惠普，從業十年，始終專注於SQL。十年一劍，十年磨礪。3年通信行業，寫就近3萬條SQL；5年製造行業，遨遊在ETL的浪潮；2年性能優化，厚積薄發自成一家。

謹以該優化方案，紀念裏約奧運會中國女排4強賽戰勝巴西女排。

2016年8月17日，這本是一個平常的日子，如果硬是要與其他重要事件關聯在一起，那就是裏約奧運會了。或許是08北京奧運的瘋狂激奮嚴重透支了國人的奧運熱情，而產生了審美疲勞；也或許是在經過幾代人的奮發圖強，國人不再需要奧運金牌數量來證明自我，從而看淡了奧運賽場的劍拔弩張人喊馬嘶。而與我而言，是由於工作、家庭的緣故，無暇奧運。

中午小憩後，將醒未醒中打開電腦，一封未讀郵件讓我瞬間驚醒睡意全無：

從郵件內容看，透露了如下幾個信息：

1. 是突然變慢：也就是說在此之前是沒有性能問題的，性能問題是突然性的；

2. 代碼沒有改動：也就是說本次性能變慢並非代碼變更引起；

3. 區域慢，中心不慢：也就是說同樣的SQL，在不同的DB服務器上性能表現不一樣。

我首先想到的是執行計劃走偏導致的，因為第一點和第二點很符合執行計劃走偏導致性能問題的表象。於是，我找到開發人員拿到了SQL，SQL如下：

通過SQL TEXT，我找到了SQLID，從ASH獲得的信息如下：

從ASH中我們發現：執行計劃很穩定，並沒有發生“走偏”的異象。

難道是數據量發生了變化，導致了性能的突然變慢？

正當我計劃谘詢相關人員的時候，ESPACE彈出了消息，是多人討論組，裏麵的人員名單中有開發人員、SE及性能測試人員。經過一番你來我往的討論後，信息歸納如下：

1. 這是個查詢基礎數據的SQL，數據量並沒有發生變化；

2. 由於是基礎數據，原始數據都是存放在中心庫，各個區域庫是通過OGG同步，因此，中心庫和個區域庫的數據量是一樣的；

3. 經確認，所有的區域庫都變慢了，而中心庫則很好很快；

4. 在變慢之前，沒有做任何的代碼變更；

5. 由於一線業務對首頁加載突然變慢很不適應，影響麵非常廣，因此勒令務必在當天予以解決；

6. 同樣的代碼，同樣的表，同樣的數據量，為何中心和區域性能相差這麼大？因此，SE要求在不修改代碼的基礎上解決該問題，很明顯，他認為這不是代碼的問題。

在上述6條信息中，第5條信息是最要命的，我瞄了下屏幕右下方，發現當時已是14:26，離下班隻有3個半小時。而至於第6條的要求，我心裏麵已經有了解決方案的腹稿。

在排除了執行計劃走偏、數據量變化的因素後，我把目光瞄準了“中心快，區域慢”這一現象。於是我決定對比下中心和區域的執行計劃，看是否一致？

區域執行計劃：

中心執行計劃：

果真，兩個執行計劃有明顯的偏差：

而在差異中，最紮眼的莫過於執行計劃的第20行，區域環境是UNOIN ALL，中心庫是UNION ALL PUSHED PREDICATE。即中心庫對VIEW做了謂詞推入，而區域庫則沒有，而這個視圖就是臭名昭著的該死的BAS_PROJECT_ALL_V。

由於SQL返回的結果集很少，一般都是在50條以內，所以對該視圖進行PUSHED PREDICATE是最優方案。從執行計劃中的COST中也可以看出，中心庫在VIEW上的COST消耗為225，而區域庫的消耗竟達到了15322，相差兩個數據量級，套用對中國足球的評語“想不輸都難”，區域庫“想不慢都難”呀。

區域庫和中心庫執行計劃的差異是我想要的結果，因為有了這個結果，才能實施我的方案：綁定執行計劃，即將中心庫的執行計劃綁定到區域庫。我將臨時方案與SE溝通後，SE同意先通過綁定執行計劃的方式解決一線業務的性能之需。於是我將SQLID及PLAN_HASH_VALUE發給DBA，由DBA通過SQL_PROFILE的方式將中心庫的執行計劃綁定到了區域。

至此，按照正常的劇情設計，接下來的畫麵應該是這樣的：通過綁定執行計劃，區域的性能得到飛速提升，臨時方案不戰而屈人之兵，兵不血刃的完成了優化，開發人員、SE、測試人員、一線業務人員都皆大歡喜。而接下來的就可以有充裕的時間來分析區域庫不進行謂詞推入的原因，從而從根本上解決性能問題。

但是，生活終究是沒有劇本的。

當DBA通過SQL_PROFILE完成綁定後，測試人員則反饋：還是不行，感覺更慢了……原來是4-5秒,現在出不來。什麼情況？劇情也太反轉了。DBA懵了、SE懵了、我也懵了：沒道理呀！！！！DBA趕緊回滾，我再次去到區域庫，當我看到執行計劃的時候，我和我的小夥伴驚呆了，執行計劃中明明顯示是綁定了執行計劃的：

但是，謂詞卻沒有被推入。綁定執行計劃的方案最終以失敗告終。經過這一番折騰，時針轉到了4點半的方向，仍然是套用評論中國足球的一句話“留給中國隊的時間不多了”。

又回到問題的本質，影響Oracle執行計劃的是COST計算，而COST的計算基礎則是表的統計信息、索引、數據分布等因素。因為區域的數據是通過OGG從中心庫同步過來的，因此數據分布應該是一致的，否則就不是性能問題，而是更嚴重的功能問題了。我收集了該SQL涉及到的所有表對象，通過對比兩套環境，發現區域的統計信息和中心基本上是一致的；而索引也是一致的。為了確保萬一，DBA手工對這些表對象進行了一次統計信息收集。而Oracle依舊無動於衷，固執的拋棄了“謂詞推入”，即便是在SQL中加上/*+ push_pred(p)*/hint。

情況變得越來越複雜，越來越糟糕，越來越不可控。

就在百思不得其解，一籌莫展之際，DBA發來了一封郵件，內容如下：

意思是說：造成中心和區域執行計劃不一致的原因是Oracle的版本不一致，針對視圖這種場景，中心庫的版本高，支持謂詞推入；而區域庫的版本低，不支持謂詞推入。

這封郵件並沒有解決問題，但是卻價值千金，因為它證明了：在區域庫現有版本的基礎上，很難通過後台技術（比如綁定執行計劃）來完成優化。

SE也無奈的接受了DB的建議：從SQL上進行優化。而此時，時針又順時針方向移動了30°，也就是說我必須得在半小時內完成SQL優化。

看著這個SQL，有種似曾相識的感覺，一打聽，才知道上次也優化過一個類似的SQL（詳見）。我對比了下兩個SQL，天呐，這哪裏是類似呀，此前案例中的SQL包含兩部分數據，如圖：

而這個SQL就是“用戶配置項目列表”的代碼呀：

我立馬找到上次優化的SQL，一對比，果然99%的吻合。於是我沿用上次優化後的SQL，重新改寫了該SQL：

一執行，飛快秒出。

客戶虐我千百遍，我待客戶如初戀。我趕緊將這個SQL提交給了SE，滿以為可以定氣收工了。但是SE看了SQL後，說了一句：這個SQL改動太大了，存在很大的風險。言下之意就是否決了這個優化方案。而此時，時間已經走到了17:45，距離下班隻剩下15分鍾，此刻，我能感覺到手心都滲出汗來了。15分鍾，已經沒有時間再與SE爭論方案的可靠性（上次的優化方案已經實施了一段時間，足以證明方案的正確性），我必須要在15分鍾內找出一個更保險的方案。

還得回到功能邏輯上。這個SQL是根據USER_ID獲取所屬的PROJECT_NUMBER列表，然後再通過視圖BAS_PROJECT_ALL_V中匹配某些屬性，而顯然單個USER_ID的PROJECT_NUMBER的數是非常少的。此時，我靈機一動，心裏在想：是否可以先通過with子查詢獲取該用戶的所有PROJECT_NUMBER列表，然後在與視圖BAS_PROJECT_ALL_V關聯呢？

死馬當成活馬醫。我來不及多想，就改寫了這個SQL：

再次執行，1S。再次提交給SE，由於隻是將普通子查詢改成了WITH子查詢，SE認為風險可控。此時時間已是17：58，離18:00隻剩2分鍾。我長舒了口氣，望窗外，路麵濕漉漉的，低窪處還明顯有積水；而殘陽如血，雲淡天高。剛過去的一場暴風雨衝刷了炎炎烈日，帶來了秋後的絲絲涼意。

在班車上瀏覽起新聞來，立馬被頭條吸引住了：中國女排3:2戰勝了巴西隊。天呐，這無疑是一個奇跡呀，大寫的奇跡呀。巴西女排，兩屆奧運冠軍，又坐擁東道主之利，可謂是占盡了天時地利人和。賽前也了解過，中國女排無論是技戰術還是大戰經驗，都不及巴西隊，而就是這些被低看的中國姑娘們，用女排魂拚出了一個奇跡，而就在中國女排姑娘們在賽場上“像老虎一樣撕咬”巴西女排、“打哭巴西小球迷”時，我也在拚力在為首頁加載的性能問題不折不饒。

雖然兩者的價值成果不可同日而語，但是其內在的精神是一致的：不到最後不放棄的執著，心無旁騖心力齊的專注，排除萬難大無畏的拚搏，不到樓蘭死不休的勇氣；有的時候，的確需要一股子狠勁，一股子對自己的狠勁，這樣才能激發自己的潛能，去完成不可能完成的任務。

原文發布時間為：2017-01-10

本文來自雲棲社區合作夥伴DBAplus

最後更新：2017-05-13 08:43:59

  上一篇：  網站盜鏈是什麼？盜鏈與廣告流量問題及如何防止

  下一篇：  數據即金錢，中小企業如何搭建數據平台分得一杯羹？