689
你要為難優化器，優化器會加倍為難你
又一個核心係統去IE成功了，操作係統從AIX換為Linux，Oracle數據庫從11.2.0.3升級到11.2.0.4，整體CPU利用率穩定運行在10%之內。
但是，有一個停複機業務的SQL猶如脫韁的野馬，執行時間從幾毫秒變到幾百秒（執行時間的變化過程中，執行計劃沒有發生改變，表中的數據有變化），變成升級過程的插曲。本文將詳細分析這個SQL的優化過程，展示一個不符合Oracle優化器的SQL語句，扭轉起來到底有多費神。
我們可以看到，在8點15這個snapshot，15分鍾內執行了10000多次（一萬多次複機），之後每15分鍾處理效率極低，隻有40次。
語句（下述語句均經過脫敏）並不複雜：
遷移之前，正確的執行計劃如下，使用了USER_ID作為關聯條件，做一個NL嵌套循環即可完成查詢：
遷移之後，執行計劃變了，執行時間從幾毫秒暴漲到幾十秒上百秒。哪裏發生問題了？
對該語句做一個10053跟蹤，看看中間出了什麼問題？
在《踩坑CBO，解決那些坑爹的SQL優化問題》一文中，丁俊做了CBO優化器組件的描述：
從上圖可以看出，一條SQL進入Oracle中，實際上經過解析會將各部分進行分離，每個分離的部分獨立成為一個查詢塊（query blocks），比如子查詢會成為一個查詢塊，外部查詢又是一個查詢塊，那麼Oracle優化器要做的工作就是各查詢塊內部走什麼樣的訪問路徑更好（走索引、全表、分區？），其次就是各查詢塊之間應該走什麼樣的JOIN方式以及JOIN順序，最終計算出哪種執行計劃更好。優化器的核心就是查詢轉換器、成本估算器以及執行計劃生成器。
這次遇到的語句之所以執行計劃沒有走“正確”：本質是subquery unnest沒有做成功，導致FILTER失敗。而失敗的根源是觸犯了CBO的底線，跟山東辱母案一樣。
簡單說，由於子查詢中包含rowid和distinct，所以視圖合並查詢失敗先執行外層查詢，外層查詢的每一行驅動執行一次子查詢，因為外層條件返回行數高達數萬條，因此，子查詢被驅動查詢數萬次，效率低下。
通常情況，這種時候我們就會祭出SQL Profile大殺器，在不修改SQL語句的情況下把SQL優化好。這通常是管用的，但是在11.2.0.4裏，它跟段譽的六脈神劍一樣存在失靈的可能性。很不巧的是，我們運氣實在有點好，從少商劍用到少澤劍，所有“原本”可以使用的Profile都失靈了。
最後查詢轉換是由對應參數控製，在我們的優化過程中嚐試了SQLTXPLORE，把FIX control接近1200個參數都打開關閉了一下，也沒有發現正常執行計劃。一般Oracle已經fix的BUG，是可以通過XPLORE發現並解決的，然而這條SQL沒有發現正常計劃。
因此，這個問題是Oracle為了避免在類似這種SQL中寫ROWID故意限製的，那麼對開發編寫SQL有什麼啟發呢？要符合規範，特別是關鍵字最好要用別名。
接下來我們談談SQL的改寫。
SQL改寫一：with查詢物化
傳統的方法都不能用，那麼我們就嚐試著改寫SQL語句。語句本身並不複雜，操作都在一張表內完成（要查詢2次），從停複機接口表中抽出最近一天產生的停機用戶，排除去重，給出ROWID，複機服務根據ROWID去複機。
所以兄弟們想到一個臨時解決方案，將第一個子查詢結果物化：
執行計劃如下：
這個執行計劃跟沒有改寫前的形式很像，區別在於將子查詢結果物化，進而提升了語句效率。
實際生產運行看，基本效率沒有問題，但是在業務小高峰，存在一定積壓的時候，性能會有些許異變。
SQL改寫二：官方怎麼看
Oracle MOS文章Query Referencing ROWID ofSubquery With Join Fails With ORA-01445（文檔 ID1929880.1）中，詳細說明了開發商原來的SQL語句寫法存在問題。
A rowid is only defined for individual rows in a table, so it is not legal to select a rowid from a subquery unless each row from that subquery can be guaranteed to be uniquely associated with exactly one row in one table. Therefore, the subquery may have only one item in its FROM list; that item must also have a rowid; and the subquery must not use DISTINCT, GROUP BY, or anything else that might gather multiple rows into one.
簡單翻譯一下：rowid僅僅用來明確識別表中的特定行，因此除非能保證從子查詢中查詢出的每一行能夠與表中的一條記錄嚴格一一匹配，否則在子查詢中使用rowid是不合法的（不符合Oracle要求的）。也就是說，子查詢輸出中能且僅可以有一個輸出項，這個輸出項可以有一個rowid，並且這個子查詢中不可以有DISTINCT、GROUP BY或其他可能會將多個行變為一個行的關鍵字。
很不幸，我們的語句裏，既有ROWID還有DISTINCT。
參考Oracle給出的解決方案：
參照，對原生產SQL語句做第二次改寫（重複部分忽略）。改成JOIN方式，避免子查詢相關查詢轉換，同時裏層用別名，外層用ROWID交付給其他服務接口使用：
執行計劃：
我們可以看到，執行計劃重新用回了NL嵌套循環。COST值有變化，其中一個表用到了全表掃描，但是應為表大小總量可控，整體效率性能依然很高。
SQL改寫三：元芳怎麼看？
波波同學深入研究了業務邏輯，提出了一種創新思路，整個SQL建議改寫為：
隻對停複機接口表做一次查詢，該表數據量本身不大（記錄數通常在10萬以內），是否走索引效率都會很高。
所以優化器理解起來就更容易，執行計劃也就更簡單，效率更高。
修改語句的邏輯是，在沒有業務積壓的情況下，複機順序不嚴格按照停機順序進行，隻要處理得夠快，複機時間早或者晚個幾毫米對最終用戶來說是無感知的。
這個案例說到這裏，有沒有給你帶來什麼啟示？
從問題發生到結案，DBA團隊嚐試過索引重建、統計信息搜集、HINT、重建表、SQL profile綁定等多種方式，最終通過SQL改寫解決。
對於DBA來說，不僅僅要懂得數據庫的基本原理、基本技術，還應該更加多往業務端走一走，懂業務的DBA會更加高效、卓越。
我們之所以要去開發SQL審核平台，初衷就是要讓開發/應用程序的SQL語句書寫更合規，更加按照符合數據庫的優化器行為去做，將潛在問題扼殺在萌芽狀態。
並且，隻有通過大量類似的大規模客戶案例的充實，才能讓SQL審核平台越來越臻於完善，SQL審核的有效識別率從80%提升到90%，以及更多。
原文發布時間為：2017-03-29
本文來自雲棲社區合作夥伴DBAplus
最後更新：2017-05-16 11:31:58