387      阿裏雲  技術社區[雲棲]

SQL性能突然降低引起的業務辦理緩慢案例一則

鍾時榮

雲和恩墨技術專家

大家好，我是雲和恩墨的技術支持工程師鍾時榮，服務的行業主要分布在通信、能源、政府、醫療和金融。作為一位衝在一線的現場 DBA 而言，回望過去，往往有種“往事不堪回首“的感覺，還沒有閉上眼曾經處理各種問題的景象就已經浮現在眼前。

作為一名一線 DBA工程師，處理各類應急問題是常事，麵對突入而來的緊急技術支持請求，我們該怎麼去處理呢？接下來為大家分享一個緊急技術支持的案例，處理手法極其簡單、粗暴，但是回味整個過程又不乏樂趣。理論為道、實戰為器，分享的主題為《SQL 性能突然降低引起的業務辦理緩慢案例一則》。

雲和恩墨是一個滋養 DBA 的好地方，在這裏有著 ORACLE DBA 發展所需的沃土和廣袤的空間。

在正式分享 ORACLE 技術案例前，先給大家分享一點自己和 ORACLE 的一些事:

1）說到 ORACLE，剛接觸 ORACLE 的時候我經常這樣問自己：

• ORACLE 是什麼

• ORACLE 怎樣工作

• 我為什麼要用 ORACLE 來開始和延續自己的職業生涯

• ORACLE 在什麼時候使用

• ORACLE 在什麼場景使用

• ORACLE 通常在哪些客戶中使用

• 我作為一名 DBA 的價值是什麼

2）當成為 DBA 有一段時間後，我又會經常問自己這樣的一些的問題：

• 我是一名什麼類型的 DBA

• 我平時怎麼學習和使用 ORACLE

• 我學習和使用 ORACLE 到了什麼程度

• 在 ORACLE 領域中我擅長什麼、不擅長什麼

• 我所擁有的 ORACLE 技能在什麼時候、什麼場景能為哪些客戶提供服務

• 我對 ORACLE 的付出和收獲能讓自己的 ORACLE 職業走多遠，走多廣以及走多深

• 除了做 ORACLE DBA，我的價值還有哪些

相信我問自己的問題，有些也是大家會問自己的問題，在此引用蓋總曾經分享的一張 ORACLE 學習導圖和大家一起在 ORACLE 的前進道路上共勉。

有人問 ORACLE 技術中的什麼最有技術難度？

• 安裝、升級和遷移

• 備份和恢複

• 故障診斷

• 性能調優

• 數據安全

• 基礎運維管理

• SQL、PL/SQL 開發

• 圍繞 ORACLE 的綜合應用設計

• 其它

因為工作的閱曆差異，相信每個 DBA 心中都有自己心中的一把尺，今天我要給大家分享的內容就是涉及到故障診斷和性能調優的一個案例。

極簡標題

本案例是一則由於 SQL 執行性能突然下降而引起的業務辦理緩慢的問題處理分

享。

1.1.  問題處理流程

俗話說“工欲善其事、必先利其器”，在正式分享該案例的診斷過程前，先給大家分享一個我工作中常用的問題處理流程，有一個總的指導方針，方能讓自己在很多問題處理的環境中不會處於太被動的局麵：

我作為工程師的角色時，隻需要主要關心工程師這個角色的任務即可，而在工程師角色提供技術支持服務的過程中比較容易出現三個方麵的主要問題：

1. 解決方案出現問題，導致問題不能被解決或者問題更加嚴重。

2. 沒有問題升級的機製，當發生的問題不是自己解決範圍內而問題又對業務係統產生嚴重影響時，應該給自己創建一條問題升級的機製的路線，比如5分鍾內進行問題解決可能性的初判，10分鍾未能真正解決的便向項目經理或者其它一切可以協調的資源進行問題升級，當然這個時間範圍是根據你真實的業務環境來定的。

3. 沒有技術支持文檔的沉澱。很多時候我們提供了技術支持幫客戶解決問題後就不再做其它工作了，為了養成一個良好的習慣，還是建議在技術支持完成後對所支持的內容進行一個簡要或者詳細的文檔沉澱，以備不時之需。

1.2.  問題現象

客戶通過電話的方式反饋一個基於 ORACLE 數據庫的係統業務運行緩慢，影響的業務範圍暫時未確定。

1.3.  問題診斷

由於該問題發生在一套關鍵業務係統上，快速的解決問題是第一目標，通過遠程連接方式連接到存在問題的數據庫主機上進行問題的診斷，情況如下：

1.4.  問題處理

經和客戶商量後，通過手工殺會話的方式來進行緊急的問題處理，當手工殺掉產生 direct path read temp 這2個會話後，該業務程序自動重新連接到了數據庫，之前業務辦理緩慢的問題消失，業務運行恢複正常，至此問題得以快速解決。

整個問題處理過程看似簡單粗暴，但是其中卻蘊含了 ORACLE 的基礎知識掌握的熟練度以及實戰經驗的豐富度。

1.5.  問題回顧

該案例從客戶反饋問題到快速的解決問題，全程共計花費10分鍾左右，解決問題的過程也看似比較簡單，過程大概為確定問題現象、推測問題原因、嚐試快速的解決問題、解決問題。

在生產係統的運維工作中，可以將問題處理概括為一句話‘將問題消滅在萌芽狀態、並快速的消除問題’，這個和我們平時學習 ORACLE 知識的時候處理態度不一樣。

• ORACLE 學習過程中，我們注重的是將原理研究清楚。

• DBA 應急響應時，我們注重的是快速解決問題。

在日常的 ORACLE 學習過程中，我們通過學習理論知識和做相應的實驗來提升自己的 ORACLE 基礎知識能力，而在實際的生產環境操作中就在不斷的積累實際經驗。這個過程就像練武的人先完成內、外雙修，最終再走向江湖。

在此問題的處理上，某種程度上可以說是是幸運的，幸運的是問題點就出在數據庫內，幸運的是熟悉 ORACLE 性能方麵的知識，幸運的是對該業務類型有一定的實際維護經驗，故根據目前數據庫裏麵的會話現象就快速的找到了引起問題的原因，並將問題進行了快速的消除。

生產環境往往是比較複雜、具有強關聯關係的，如下圖：

在現在的 DBA 時代，對我們 DBA 的要求也越來越高，不僅是需要我們有擅長領域的深度，還需要我們有整個IT架構的廣度。故 DBA 擁有一套應急響應流程顯得格外的重要，如下是一個 DBA 應急響應的簡要任務表：

1.6.  問題詳解

當我們快速的處理完問題，業務也恢複了正常運行後，那麼問題是不是就算徹底解決了呢？客戶是否完全滿意快速的問題解決方案呢？答案不一定都是 YES。

以剛剛分享的案例為例子，從問題的解決完整性上來看，剛剛隻是臨時快速的填平了一個坑，但是由於未真正找到出現坑的原因，故在前方則可能還存在未爆炸的雷，隻要雷未徹底的排除掉，則其就存在還會因為相同原因而引爆的隱患。

此案例客戶會問如下問題：

• 引起該問題的根本原因是什麼

• 為什麼性能突然就下降了呢

• 徹底解決該問題的方法是什麼

接下來我們對該問題中的一些關鍵點進行進一步的詳細分析：

1）問題的準確定位

在定位問題的過程中，往往有如下典型的情況：

• 問題定位明顯，環境中能有諸如問題反饋精確、資源利用異常、I/O 錯誤、網絡錯誤或大量的同一業務(SQL_ID)異常等能明顯定位問題的表象。

• 問題定位模煳，不能明確的判定出出現的問題是因還是果，隻能依據現有的一些信息進行推測。

在該案例中，雖然數據庫主機的資源利用率正常、主機和數據庫日誌無異常報錯以及數據庫中無大量的異常消耗資源的等待事件，但是通過少量的可能存在問題的SQL和業務側的問題現象匹配便精確的定位出了問題點的所在，為後續的問題快速處理贏得了很多的時間。

2）數據庫側問題定位的分析

由於數據庫中瞬時活動會話的等待事件小於10個，故很快的找出了可能存

在性能問題的2個會話，這2個會話產生的等待事件為 direct path read temp，該等待事件是指將數據從臨時表空間中讀取到 PGA 中，其通常和排序、HASH JOIN、臨時表操作或者 BUG 等有關。

3）會話的分析

分析會話的連接和 SQL 執行情況，如下：

• 從 V$SESSION 中的 LOGON_TIME 和 STATUS 字段的值可以推斷出該程序對應的連接類型為長連接。

• 從 V$SQL 中的執行次數、執行計劃值、第一次和最後一次加載到共享內存中的時間和 WRH$_SQL_PLAN 中的執行計劃值可以推斷出該 SQL 的執行計劃未發生變化，且平均每次執行的時間為0.4秒左右，但是由於出問題的時候客戶反饋該SQL是超過1分鍾無響應的，故0.4這個平均響應時間隻能說明業務正常的時候或者絕大部分的執行時間相比於1分鍾來說是很小的，即小於0.4秒每次。

• 從 ASH 數據 (DBA_HIST_ACTIVE_SESS_HISTORY) 中查詢產生 direct pathread temp 等待事件的2個會話(即被 kill 掉的會話)的資源使用情況，可以得知存在問題的會話從11:56開始執行，到下午15點都沒有執行完成，且其消耗了17G的 TEMP 空間；由於被殺掉的會話讀取文件的地址在不停的變化，說明該會話並沒有夯住，隻是執行速度慢。

DBA_HIST_ACTIVE_SESS_HISTORY 的關鍵字段解釋如下：

4）SQL 的分析

通過產生 direct path read temp 等待事件的 SQL_ID，找到對應的 SQL，SQL 文本如下( SQL 中的表名做了部分處理)：

其執行計劃如下：

分析該 SQL 的執行計劃，快速分析執行計劃的時候我們主要關注點有如下：

• 表的連接方式

• 返回的行數

• 謂詞信息

上麵的案例中有如下連接方式：

• 嵌套循環(NESTED LOOPS)

嵌套循環的關鍵點是知道驅動表(外部表)返回的結果集行數就是被驅動表(內部表)需要被掃描的次數，靠近 NESTED LOOPS 關鍵字的就是驅動表。

• 半連接(SEMI JOIN)

半鏈接是指2個表做連接，隻返回其中一個表的數據，此 SQL 中存在in子查詢，且采用的是 HASH 半連接連接方式。

怎麼來分析這個執行計劃呢，這裏提供如下幾個方麵的思考點：

1. 粗略的分析執行計劃的效率

從上麵的執行計劃可以看出 CBO 評估出來的每步操作返回的最大行數不超過12行，假設該評估值是準確的，則無論是采用 HASH 還是嵌套循環的連接方式，理論上該 SQL 的執行時間應該是毫秒級別的。

但實際上該 SQL 的執行效率很低，故推測是 CBO 評估的行數是不準確的且執行計劃是低效的。

2. 推測該執行計劃效率低的原因

執行計劃中的5到13步都是采用嵌套循環連接方式，嵌套循環連接方式的缺點就在於當驅動表的數據量越大，其循環掃描被驅動表的次數越多，故問題可能會出現嵌套循環的連接中。

再看在 SQL PLAN ID=4 的時候是將 SQL_PLAN_ID=5 和 SQL_PLAN_ID=32 做了一個 HASH 半連接的算法，當 SQL 在做 HASH 連接的時候會將結果集劃分為多個 hash 桶，並將其盡量放在 PGA 中，如果 PGA 中不能存放則會使用TEMP空間，由於 SQL_PLAN_ID=32中 A_QUERY_ACCT_ATTR 表的數據量為72行， 但卻消耗了17G 的臨時表空間，故可能第5步返回的結果集會比較大，如果第5步返回的結果集大，由於其之前是通過嵌套循環獲取的結果集，則其存在消耗大量時間資源的可能。

3. 利用 ASH 數據來查找問題點

有了第2點的執行計劃效率低的推測後，我們再次通過 ASH 的數據來進行輔證，如下：

從 ASH 的數據中可以看到會話 90%以上的都是在進行 SQL_PLAN_LINE_ID=31 即 <TABLE ACCESS BY INDEX ROWID        | DDSERV_ATTR>

由於31步後緊接著就是和第6步產生的結果集做第5步中的嵌套循環，可以推測第6步的結果集中返回了大量的數據，或第31步的 DDSERV_ATTR 也返回了大量的數據，當一個大數據量的結果集做驅動表去和大數據集的內表做嵌套循環計算時，其性能是非常低下的。

4.推測執行計劃是否可以優化

a. 當遇到2個大數據量的結果集做連接時，我們一般會考慮采用 HASH 連接的方式來替代嵌套循環的連接方式，但此處表的連接數較多，且有明確的數據限定條件，故暫時不考慮此種方法。

b. 注意執行計劃中的 Filter

執行計劃中的 id=1和 id=3 是 2個 filter 操作，其對應 SQL中的如下子查詢部分:

以及謂詞訪問部分：

可以看出該 SQL 涉及的2個標量子查詢中，對於表 DDSERV_SUBSRIBER 涉及的標量子查詢未發生子查詢展開的情況，其對應於 id=3 這個 filter，且該 filter 是對 id=4 和 id=33 的這2個結果集進行一個類似嵌套循環的操作；

對於表 DDSERV_LOCATION 涉及的標量子查詢其采用的是子查詢展開的方式來執行，其對應於id=1這個 filter，其在執行計劃的最後一步才進行數據過濾。

如果采用子查詢和表優先進行連接的方式，並進行子查詢數據處理的方式來過濾數據，假如過濾後的數據變得比較少，則執行的速度理論上會大幅提升。

5. 驗證之前的分析結果

為了確認上述分析產生的疑問，需要跟蹤執行速度慢的 SQL，獲取其變量值，根據實際的數據分布情況來查找該原因。

幸運的是該係統中除了剛剛殺掉2個會話外，還有一個會話同樣也產生了 direct path readtemp 等待且 SQL 和剛剛殺掉的會話所產生的 SQL 類似，通過對該會話做跟蹤後，獲取到了執行速度慢時的實際值，其為：

將如上變量值代入 QUERY 程序中存在問題的那個 SQL中，加上 gather_plan_statistics 提示做 SQL 性能測試，得到的結果如下：

從實際的執行計劃中可以看出，問題開始於 id 為 24/25，Oracle 評估返回1條記錄，而實際上返回了56條，這裏其實可以通過 NVL(G.EXP_DATE, SYSDATE) =(SELECT MAX(NVL(EXP_DATE, SYSDATE)) FROM SERV_LOCATION WHERE SERV_ID =A.SERV_ID) 過濾條件來過濾出大量重複數據，但是 ORACLE 把其放在了 id=1 中去進行過濾 (FILTER)。後麵的26/27步問題就越發嚴重了，實際返回行數達到了30000以上，但是 ORACLE 卻將過濾條件放在了第3步來進行過濾 (FILTER)。到後麵的第30步，返回的數據多達3000多萬條，再做嵌套循環時，速度會較慢。

經過測試，該 SQL 執行超過2分鍾仍然沒有執行完成，故終止了此測試。

通過上麵的分析，可以提出如下優化點：

采用子查詢不展開且優先處理子查詢表中的數據的方式來過濾數據。即是為子查詢添加提示 /*+ no_unnest push_subq*/ 來強製前置過濾步驟以及關閉子查詢展開，其中 no_unnest表示希望子查詢不展開，push_subq 表示希望優先執行子查詢中表的數據。

補充一下 HINT 的相關知識：

HINT 通俗的理解就是一種手工改變 ORACLE 執行計劃的方法，它有固定的格式和固定的名字，格式為 /*+ hint 名字 hint 名字 …*/，hint 名字的值是同義詞 V$SQL_HINT 中的 name 字段值，其一般是跟蹤 select 關鍵字後麵，需要注意的是即使 HINT 在語法和語義上是有效的其不一定在 SQL中會生效。

該案例中，修改前的語句為

修改後的語句為：

調整後的執行計劃為：

調整後的執行計劃中的 id=17 到 id=21,id=24 到 id=28 表示該 SQL 已經按照我們希望的子查詢不展開且先進行子查詢表數據處理的方式來進行連接。

調整後的 SQL 從之前的2分鍾未執行完成到目前35毫秒便執行完成。

故可以通過如下方法來進行 SQL 優化：

通過 ORACLE 的 sql profle 腳本來調整此執行計劃，調整後的SQL使用采用了 /*+ no_unnest push_subq*/ 提示的表連接方式來執行數據獲取:

整個問題的主要處理過程如下：

極簡標題

該案例涉及到的主要知識點有如下：

• DBA 應急處理流程

• 怎麼準確的獲取故障現象

• 日誌文件診斷

• 數據庫主機負載診斷

• 數據庫等待事件診斷

• ASH 數據診斷

• 執行計劃診斷

• HINT 的含義與使用方法

• 穩定執行計劃的方法

通過上麵案例的分享，是否還需要做如下思考呢？

• 該 SQL 的執行計劃沒有改變，那麼是什麼原因導致的 SQL 性能突然下降

呢?

• 從通過 sql profile 來調整 SQL 執行計劃的解決方案來看，該問題是由於數據分布不均勻和表連接方式不適合該數據分布的情況引起的性能問題，那麼該解決方案是不是一種通用的解決方式呢？

• 還需要刨根問題的去分析數據分布不均勻的原因麼？

• 該問題再次爆發的可能性？

• 該案例中的解決方案的通用性怎樣，你會怎麼給客戶介紹一些更好的後續改進建議?

• 你覺得該案例中比較有意思的地方在哪裏呢？

有機會還可以做如下探討：

1）分享你的問題處理流程與原則

2）你有調優強迫症麼？

3）分享你的 SQL 優化那些事

4）分享你的執行計劃調整方法，比如 out line, sql profile, sql tuning, spm, dbms_stats 包，改寫 SQL，索引調整，應用設計調整等等針對 SQL 的優化調整方法

自上而下的業務係統的好用與否和應用使用人員、應用程序、中間件、網絡、主機、數據庫和存儲等密切相關。在問題處理中不僅需要我們具有全局的廣度，還需要我們具有專業方向的深度，IT服務之路漫長而富有挑戰，與大家共勉。

文章轉自數據和雲公眾號，原文鏈接

最後更新：2017-07-18 20:36:00

  上一篇：  輕輕揭開 b*tree 索引結構的神秘麵紗

  下一篇：  性能為王：SQL標量子查詢的優化案例分析