495
見微知著：一條 SQL 性能問題引發的核心係統悲劇
黃廷忠（網名：認真就輸）
雲和恩墨技術專家
個人博客：https://www.htz.pw/
本篇整理內容是黃廷忠在“雲和恩墨大講堂”微信分享中的講解案例，SQL 優化及 SQL審核，是從源頭解決性能問題的根本手段，無論是開發人員還是DBA，都應當持續深入的學習 SQL 開發技能，從而為解決性能問題打下根基。
第一篇為：性能為王：SQL標量子查詢的優化案例分析
第二篇為：SQL審核：OR展開與子查詢優化案例詳解
本篇為係列案例之三：IN子查詢返回結果集很小
這是不久前在一個客戶現場遇到的一條 SQL 性能問題，此 SQL 子查詢結果集返回最多10行，但是整個 SQL 的性能卻不好，此 SQL 最後還導致了一個核心係統故障，引起了一個悲劇的事情。
業務反應慢，查詢 v$session 發現同時有24個會話在執行此 SQL:7ug8q9myb0bsz，由於此 SQL 性能不好引起大量的 GC 等待，導致其它的業務受影響。
SQL性能問題診斷
下麵直接給出常量的 SQL
Child_number 0
Child_number 1
首先說明一下，是 OLTP 環境。也就意味著要快速的返回結果，並且多數情況下，SQL 返回的結果集不多。
在 SQL 切圖中，有兩處我們用紅色的箭頭標識出來，這部分信息需要我們關注的。在整個 SQL 中，就隻存在2處過濾信息，一個是 redu_owner_id，一個是 status_cd。但是 status_cd 在兩個子執行計劃中都是相同的，所以這裏就隻剩餘 redu_owner_id 這列了，我們也可以執行 redu_owner_id 所在的 OP 這個表，肯定是驅動表，並且 redu_owner_id 這列應該存在數據傾斜的情況。那麼 redu_owner_id 返回的結果集將直接影響整個 SQL 性能的好壞。
下麵繼續查看 SQL 部分，可以發現一個重要的信息就是在子查詢中存在 rownum<10，也就意味子查詢最多返回10行。在 OLTP 係統中，存在一個表最後最多返回10行的情況，這裏也就大概想到了用子查詢做去驅動表了，如果執行計劃中，沒有用子查詢做驅動表，那麼很有肯能執行計劃就是錯誤的，那麼這裏的自己認為的驅動表與之前根據 SQL 前部分猜測出來當前執行的驅動表（OP）不一樣。
下麵查看執行計劃
在執行計劃中，我們看到當前執行計劃的驅動表示 OFFER_PROD(OP) 這個表，與之前我們猜想一樣，那麼基本可以肯定，redu_owner_id 列的數據存在傾斜，當返回大量結果集時，性能就很不好。
在執行計劃中，這裏特意把子查詢標記出來，就是需要引起重視，子查詢當著一個整體與主查詢做 HASH 連接，沒有作為驅動表走 NL，也就可以肯定整個執行計劃連最基本的驅動表都選擇錯誤。下圖可以更直觀的看到。
這裏做個補充：子查詢當著整體，也就是被當著一個視圖與主查詢做關聯，什麼情況下子查詢會當著一個整體呢?
其實 MOS 有相關的文檔說明的，大家可以去 MOS 一下，在本案例是由於 ROWNUM<10 導致的。
基礎信息分析
在 V$SQL 中查看每個 child 的統計信息
這裏看到，存在兩個子遊標，他們的執行計劃相等，但是兩個子優化的性能相差很大，並且性能不好的子優化執行次數很多。
在上麵我們提到主查詢就隻存在兩個過濾條件。執行計劃+謂詞信息可以看到驅動表使用那個列來過濾數據。
在上麵一直在說 redu_owner_id 這個列存在數據傾斜，那麼下麵來證實一下：
下麵來查看，redu_owner_id 的值的分布，兩個不同綁定變量返回的行數：
通過這個信息，我們知道了，上麵 SQL 由於列的值存在傾斜，導致 SQL 執行計劃部分值執行很快，部分值執行很慢。
大家可能會說，在11G中，SQL 引入了 ACS 功能，但是很不幸的事在客戶這裏 ACS 都是禁用了的。
SQL 的修改
下麵就是怎麼來優化這個 SQL，在上麵提到了子查詢中最多返回10行，可以用於做NL的驅動，要讓子查詢的表做驅動表，應該怎麼來修改 SQL?
在上一個案例中，通過了with as 的方式來改寫。
這個案例就不修改 SQL，通過提示（Hints）來達到目的。
這裏使用 cardinality 提示，在 SQL 解析的時候告訴 CBO 表上存在多少行。表上存在的行數越少，也就意味著訪問表的成本越低。
下麵我們拿返回8611行的綁定變量來做測試
添加提示後的 SQL 如下：
紅色部分就是添加的提示，執行計劃如下：
可以看到，子查詢的結果集已經作為驅動表了。
統計信息
每次的邏輯讀從原來的369，927降低到現在的45 ，性能提升很明顯，並且主要解決了 RAC之間的 GC 等待，不影響其它的業務了。
優化 SQL 後，CPU 使用率從原來的70%左右直接下降到25%左右，此係統的主機性能很 NB 的，8路的 PC ，E7 的 CPU。
此案例結束，主要提到兩個知識點：
列的值分布不均勻，導致 SQL 性能出問題
通過 cardinality 來指定表的行數，達到指定表做驅動表的目的
SQL優化是一項專業的技能，必須深入了解數據庫原理才可能做出準確的判斷，在開發過程中強化開發人員培訓，引入SQL審核、審計，是確保應用高性能的有力手段。 文章轉自數據和雲公眾號，原文鏈接
最後更新：2017-07-18 20:36:42