95
凍結時間倒數前一小時，記一次步步驚心的SQL優化
作者介紹
黃浩：從業十年，始終專注於SQL。十年一劍，十年磨礪。3年通信行業，寫就近3萬條SQL；5年製造行業，遨遊在ETL的浪潮；2年性能優化，厚積薄發自成一家。
9月版本是一個大版本，上上下下都在緊鑼密鼓地張羅著。
9月10日版本上線，8日開始，能明顯的感覺到大戰前戰鼓擂動人喊馬嘶的緊張氛圍。項目組人頭簇動，奔走如織；郵箱內，關於BUG單通報及處理意見的郵件，在這個驕陽似火的南方，猶如冷冽寒冬時北方的雪花般漫天紛飛。
14：40
主動出擊
快下午三點鍾的時候，一片雪花悄然飄落在我的身上：
務必搞定，全力支持，看內容，聽口氣，這是PM的死命令了。
雖然，我隻是“全力支持”，也就意味著是“協助”性質；但是我也不敢怠慢，其一是關乎到能否下班，其二是此時已是一片混亂，都被功能BUG單弄得焦頭爛額了，開發人員都無暇顧及性能問題了。如果我不主動出擊，今天肯定就不能下班了。
我通讀了郵件內容，發現有兩項性能問題看起來是與我有關，也就是與SQL有關的，缺陷單號分別為：D6899590、D6679058。
14:48
雙拳難敵四手
從描述上看，單號為D6899590的性能問題更加突出，更應急劇解決。正當我準備先啃硬骨頭，主動聯係相關責任人時，又來了一封郵件，內容如下：
這正是一場及時雨，澆滅了一團熊熊正燃的大火。先不管“申請”是否能得到批準，至少是一種解決問題的途徑，而且可以預見，這將是一個有效途徑，因為此時此刻，已經到了上線前最關鍵的節骨眼，隻要提單人同意了，PM也不想節外生枝，多一事不如少一事，多半都是同意的。
所以，我決定將這個問題先放在一旁，全力進攻另一個問題。
當我來到開發人員座位時，開發人員正在“語音會議”中，桌麵上布滿了即時通訊的聊天窗口，還不時的彈出消息提醒。一邊“語音會議”，一邊還要文字交談。此時我也不再忍心幹擾，隻是默默待在一邊等待“語音會議”結束。
“關於性能的那封郵件你看到了嗎？”
“我知道……哎，又被通報了……我手上還有好幾個BUG單要處理。”
低沉中略帶顫抖、無奈下盡顯滄桑，看著這位被BUG單折騰得疲憊不堪語無倫次的小夥子，我心戚戚。
“你把這個性能對應的SQL發給我。”
“要不等下吧，這邊先處理完手頭的功能BUG單。”
“我這邊優化也需要時間，你先把SQL給到我，我這邊優化SQL的時候，你就可以同時處理其他BUG。”
14：55
這樣，我拿到了對應的SQL：
15:33
意外收獲-又是視圖惹的禍
由於時間關係，我沒有深入解讀&分析SQL代碼，而是直接查看了執行計劃，如下：
初看這個執行計劃，倒也婀娜多姿、凹凸有致。但這個妙曼少女般的執行計劃卻隱藏著一個巨大的疑問：SQL並不複雜，表對象並不多，為何執行計劃卻如此“漫長”？我能想到的有兩種場景會導致執行計劃“變長”：其一是SQL中的OR條件被展開（CONCATENATION），其二是SQL中有視圖。
果真，我在執行計劃中找到了被展開的VIEW：BPAV_V，這個視圖還不止被訪問了一次，總共訪問了兩次。看到這個視圖，我心竊喜。
我與這個視圖曾結下了不解之緣（從到《SQL優化案例之抽絲剝繭》，真可謂纏綿悱惻愛恨情仇），最終，萬般無奈之下，我手起刀落，用一張表BPAT_T替換了這個“萬惡”的視圖。
所以，我立馬將SQL視圖BPAV_V改成了BPAT_T。再執行，性能從11秒變成了7秒，提升了4S。
意外收獲並沒有轉換成的意外之喜。
相反的，來自於SE的催促郵件不絕於耳，一會兒是要原因分析，一會兒又要進度說明。此時，我咬定青山不放鬆，沒有時間和精力去理會，相信隻要能盡快將SQL優化好了，一切聲音自然會消逝。所以我潛心優化，對各種郵件視而不見，對各種聲音聽而不聞。
15:15
繼續分析執行計劃，在“千絲萬縷”中，我發現了幾個關鍵字：
CONCATENATION、MERGE JOIN OUTER、SORT JOIN，尤其是MERGE JOIN OUTER，是成本消耗較高的操作。
於是我重點分析了下產生這幾個操作的SQL代碼片段。
根據經驗，並結合SQL的業務功能“我參與的流程”，可以判定
就是關鍵的過濾條件，即如下兩個條件隻要任意滿足一個即可：
1、WPF表的BCB_C為自己的UID_C；
2、PAT子查詢的PII_C不為空
這也是執行計劃中出現CONCATENATION的原因所在。這應該沒問題，因為通過這兩個條件應該可以過濾掉大部分數據，以此過濾後的數據（小表）為驅動自然能收到不錯的性能效果。
我單獨執行了子查詢，發現隻有4條數據：
順著這個思路，繼續分析執行計劃，有了重大發現：子查詢PAT並沒有被選為驅動表。
我嚐試著用leading強製指定驅動表，但是並沒有奏效。想著之前一個有關CONCATENATION的優化案例，任憑如何hint也很難改變其執行計劃，當時在焦頭爛額，萬般無奈之下，隻能將or修改成union all。這次難道也非得到這樣嗎？
情急之下，死馬當成活馬醫。我靈機一動，既然希望PAT作為驅動表，而且PAT的數據量隻有4條，是否可以指定其與外部結果集的連接方式為nested loop呢？
在增加了USE_NL(P, PAT)的SQL HINT後，我看了下執行計劃，果然PAT子查詢變成了驅動表：
看到驅動表的選擇已經如願以償了，我也沒有時間仔細分析執行計劃，直接按下了F8執行，我的個乖乖，2.98S，終於進入了3S。
15:36
節外生枝
我把優化後的SQL發給了開發人員，本以為可以收工了。但是開發人員回複說：這個SQL要跑到2S內，因為這個頁麵除了執行這個SQL外，還需要做其他的處理，大約需要1S時間。
不怕有問題，就怕沒時間
性能不達標，可以繼續優化，但是做任何事情都需要時間。而現在雪上加霜的是：9月版本的代碼將在16:00整凍結。
這下，留給我的時間真的不多了，原本是截止到18點的，現在凍結的時間說變就變，從18點提前到了16點，2個小時呀。
從最開始的11S到現在的3S，我已經使出了洪荒之力；即便如此，我也不能讓這1S成為壓垮這個性能問題的最後一根稻草，哪怕隻剩下不到半小時的時間，我也要堅持到底。
或許是有更緊急的功能BUG要處理，而顧不上性能這塊，到了這個時候，郵箱安靜了，即時通訊安靜了，仿佛整個世界都安靜了下來，最重要的是，此時我的心也安靜了。
再回到SQL語句，我又快速瀏覽了一遍，這次，SQL中的大量的CAST類型轉換引發了我的興趣。
這種轉換，我未曾用過，也未曾見過。但是，有一點是可以肯定的：類型轉換勢必會影響到執行性能，原因很簡單，轉換的時候，需要逐行校驗數據的合法性。
基於此，我試著取消某個字段的CAST轉換，F8執行，居然報錯了：
看到這個錯誤，再結合SQL語句，我們勐然醒悟：SQL中有UNION關鍵字，而兩部分中，相同業務內容的字段的數據類型居然不一致，因此就需要轉換。
我的第一反應就是：統一數據類型，修改表中字段的數據類型。但轉念一想，統一數據類型固然可行，但是有點想當然了，因為對表結構的任何改動都存在巨大的風險，而現在時間上根本無法承受這種風險。
兩眼瞅著這些鱗次櫛比的CAST，心裏想著誰還把TID_C這種明顯NUMBER型的字段建成了NVARCHAR2，也是個人才呀。
再看這個CD_C，轉換成了TIMESTAMP，這個沒有必要了吧，如果CD_C本身就是date類型的話。查看了表結果，果然是DATE類型，我就果斷連同後麵兩處同樣的轉換一並取消了。
不放棄，總能收獲
繼續往下看，接下來的一個CAST讓我激動不已：
CAST('JCS' || DBMS_RANDOM.RANDOM() AS NVARCHAR2(64)) BS_ID_,
在這裏居然碰到了DBMS_RANDOM.RANDOM()，天殺的，這個查詢將近100萬的數據量，也就是要產生100萬的隨機數，性能是絕對受影響的。
我試著注釋掉隨機函數，果真，速度杠杠的，不到2S。那麼這個隨機函數在這個SQL中的作用是什麼呢？是否也注釋掉呢？在時間上，已經不容許我深入疑問。於是我把這個疑問拋給了開發人員。由於開發人員新接手這個功能，這些細節上的問題，還需要確認。
我一邊等著開發人員的回複，一邊繼續往下看，希望能發現更多可以優化的地方。我在NOTEPAD++編輯器中，漫無目天馬行空般逐個雙擊被CAST的字段，當雙擊到STATE字段時，意想不到的情況出現了：
這個state字段同時也是過濾條件字段，而且隻有一個值，那就意味著CAST的字段對象完全可以用常量值替代。而緊挨著的HANDLER字段也是如此。這一下子，就省略了兩個字段的CAST轉換。再加上前麵3個CD_C，總共省卻了5個字段CAST轉換帶來的成本開銷。
此時，也收到了開發人員那邊的回複：這個是為了兼容平台SQL（也就是UNOIN ALL的另外一段代碼），用隨機數唯一標識數據。既然是這個用途，我就決定用TID_C字段替換隨機函數，因為在SQL中，TID_C本身就是唯一的。
經過這番“咬文嚼字”般的“急急如律令”，我也如釋重負，相信跑進2S應該問題不大了。按下F8，1.8S。
15:40
一波未平一波又起
就在我將優化後的SQL提交給開發人員，準備發包驗證時，發包人員回複：這個是平台包，每天的發包時間窗隻有兩個，中午12點及下午5點。
非常日期當用非常手段
眼看隻有不到20分鍾的時間了，此時的我心急如焚：在險象環生的海上風暴中，經曆半個多小時的殊死搏鬥後，九死一生，港口即在眼前，卻發現錨不見了，靠不了岸。
我轉問開發人員：
“你本地JAVA服務端能不能連接到測試服務器？”
“可以。”
“那就切換到測試服務器，直接在你本地驗證這個性能。”
“不過我要重啟服務，大概需要10分鍾。”
我一邊敦促開發人員切換數據庫，一邊聯係上性能測試人員。
“由於時間關係，這個性能問題需要在開發人員本地測試，需要你過來這邊”
“這不行，還沒有過在本地環境驗證性能問題的做法。”
“這個性能問題完全是SQL造成的。本地服務端已經切換到測試數據庫了，從原理上看，在本地驗證完全是等價的。”
在SE的共同努力下，測試人員從另外一個ODC趕了過來。此時開發人員的本地服務也重啟完畢。
後記
驚心動魄過後，再來回顧下該案例，整個優化過程都沒有出奇出意、可圈可點之處，每個優化點都是那麼的平凡，平凡得讓“高手”們不齒。但也就是這些平凡的優化湊在一起，化解了一場“危機”。很多人都問過我一個問題：怎樣才能做好SQL優化？我想這個案例或許能給出答案：
1、驅動真的很重要；
2、讓Oracle盡量少做事。
原文發布時間為：2017-02-21
本文來自雲棲社區合作夥伴DBAplus
最後更新：2017-05-15 10:02:39