第一章 技術人生系列 · 我和資料中心的故事（第五期）—清算/報表/日終跑批程式之效能優化案例（一）

中亦安圖 | 2016-02-18 21:40

前言

不知不覺，技術人生系列·我和資料中心的故事來到了第五期。小y又和大家見面了！

前幾期主要發了一些TroubleShooting的案例分享，其實小y最擅長的是效能優化，所以從這期開始，小y會陸續的分享更多的資料庫效能優化案例。

進入正題，如果您的日終跑批/清算/報表等程式時快時慢，或者從某一天以後就一直變慢，作為運維DBA或開發的您，會怎麼下手？還有，除了解決問題外，你要如何解答領導最關心的一個問題，“為什麼現在有問題，但是以前沒有問題呢”！

小y今天要和大家分享的就是這樣一個效能問題的分析和解決過程。

你們的點贊和轉發就是小y繼續堅持分享的動力。

更多Oracle資料庫實戰經驗分享的首發，盡在“中亦安圖”公眾號！歡迎關注。

另外，前陣子有部分朋友問，小y所在的團隊是否可以提供對外的第三方Oracle服務，答案是YES！

有興趣的朋友可以加一下小y的個人微信，微訊號是 shadow-huang-bj,希望可以交到更多的朋友，並幫助到更多有需要的人。

Part 1

問題來了

小y,有空麼？一會一起看一個報表的效能問題。

有個SQL語句一週前開始，效能急劇惡化，執行時間從10分鐘以內變成了10個小時以上。

剛在客戶現場做完Oracle的培訓，問題來的正是時候，剛好可以讓客戶感受下理論如何融入實戰的魅力！小y的第一想法是SQL語句的執行計劃發生了改變，通常從統計資訊或者CBO對cardinality的估算情況中就可以快速找到線索，應該很快就可以查明原因並解決！

最後的事實證明，小y一開始想簡單了。針對這個問題，客戶通過並且重新收集統計資訊或重啟資料庫均無法解決問題。幸運的是，小y及時調整回到了學院派模式，最終在一個小時內找到了問題的原因，問題的解決也就是順其自然了。

環境介紹：

作業系統 Redhat 64 bit

資料庫 Oracle 11.2.0.3 , 2節點RAC

Part 2

分析過程

2.1 完整的SQL語句

小y對這條SQL進行了敏感資訊處理和寫法的簡化處理，可以看到：

? 該SQL對兩張表張進行join，然後group by

? 參與關聯的兩張表一張是80M的小表，另外一張是3.5G的較大一些的表。記錄數分別是160萬和800萬

? SQL語句用了hint，提示優化器表連線走hash join,單表訪問路徑小表走全表掃描。

這樣的一條SQL，按照小y的經驗，驅動表只要選擇小表，那麼整個HASH JOIN的執行時間基本等同於兩張表的單表訪問時間，兩張表加起來不到4G，通常都可以在5分鐘內完成。這和客戶描述的以前的執行時間是相吻合的。

這裡順便說一下：

很多開發寫hint往往寫的不完整，例如這個hint只寫了表連線方式，單表訪問路徑只寫了一張表，表的連線順序沒有寫，其實並沒有完全固定死執行計劃。

接下來，小y將檢視執行計劃是否發生變化，還有執行計劃是否正確。

2.2 執行計劃

可以看到：

? 執行計劃（oracle內部的演算法）確實如hint一樣

? 表連線方式走的是hash join

? 單表訪問路徑都是全表掃描(table access full)

? 表連線順序是小表做驅動表(hash記憶體表）

這是一個完美、最優的執行計劃。唯一的小缺點是優化器評估hash join和hash group by的步驟用到了一些臨時表空間，不過這只是CBO的評估，不代表實際會發生。

對比了以前的執行計劃，也是一樣的。

既然執行計劃沒有問題，也沒有發生改變，那麼就需要將SQL的執行時間進行分解，看看時間到底消耗在了是CPU還是IO、叢集、併發競爭等什麼環節。

2.3 SQL執行的相關統計

可以看到：

1、 每次執行時間39615秒，超過10個小時

2、 每次執行邏輯讀只有45276個block（塊）

3、 每次執行物理讀451421個block（塊）

4、 時間基本都消耗在CPU上，達到38719秒，超過10個小時，而在IO/叢集/應用（鎖）/併發環節消耗時間很小

2.4 第一次頭腦風暴

到了這裡，經驗豐富的DBA應該可以發現，該CASE出現了一些奇怪的現象。

不過還是要照顧一下大家，先來回答一些朋友心裡可能的問題。

2.4.1

是不是有什麼異常等待事件

看到這裡，也許有人會說:

是不是SQL語句執行過程中有什麼異常的等待事件？

首先答案是NO！

因為整個SQL的執行時間中，時間基本都消耗在CPU上，達到38719秒，超過10個小時，而在IO/叢集/應用（鎖）/併發環節消耗時間很小（加起來不到100秒）。如果SQL跑在CPU上，那麼是不會有等待事件的線索的。時間分佈如下圖所示。

2.4.2

是不是hash join One-pass/Muti-pass導致慢

也許有人會說：

執行計劃出現了temp表空間的使用，是不是hash join One-pass/Muti-pass導致SQL執行慢

答案是NO！

首先，執行計劃中顯示會用到temp表空間(hash join one-pass/muti-pass)，這是CBO執行前的評估而已，實際執行很可能根本不會使用。

其次，如果真的使用temp表空間，並且成為整個SQL的瓶頸，則我們會看到很多的direct path read/write temp,由於這類等待事件算在IO類的等待事件裡，那麼整個SQL語句的執行事件就應該是IO佔的最多而不是現在看到的時間都消耗在CPU上。

2.4.3

小y的疑惑

到這裡，小y開始感覺到了這個case需要更專注來解決了！

執行時間基本都耗在CPU上，這通常意味著所需要的資料基本都在記憶體中。

一個常識是，如果所需要的BLOCK在記憶體中，那麼 CPU每秒可以處理10萬甚至幾十萬的邏輯讀！

但具體到這條SQL， 10的小時的CPU時間，處理的邏輯讀，才有45萬！

45萬的邏輯讀剛好對應4G的大小，即兩張表的大小之和。

目前確實有一些奇怪的地方，小y接下來需要：

? 和歷史執行時間的分解進行比對

? 將這條SQL語句重新跑起來，獲取更多的線索。

2.5 歷史執行情況比對和確認

可以看到：

? 一開始的時候，每個小時還可以處理44萬的邏輯讀，但是後來就慢了起來

? 後來的絕大部分時間裡，每個小時才處理1000-3000的邏輯讀

? 執行時間確實都在CPU上！

可惜的是，由於AWR報告只保留7天，因此未能獲取到原來的執行時間的分解的情況，也就沒有辦法做正常和異常時刻的比對。接下來，這是一個SELECT語句，可以直接跑起來重現問題，這樣小y可以觀察到更多的線索！

2.6 重現問題實時抓取線索

將這條SQL語句重新跑起來，然後開啟其他視窗觀察，一開始的1分鐘內還算正常，先後讀取小表和大表，IO差不多到每秒30M，然後IO就急劇的下降了，這個時候等待事件是ON CPU。

小y立馬檢視了SQL的執行進度，v$session_longops中表SMALL_TABLE已經掃描完成，但另外一張表BIG_TABLE全表掃描的進度進本停留在82%的位置！但細看還是漲的，只是漲的比較慢！如下圖所示。

對BIG_TABLE的全表掃描，sofar基本上每5秒才漲1 ！

按照這個速度，還需要（442460-362690）*5=40萬秒，即10個小時以上！這和“歷史執行情況比對和確認”章節是可以對上的！

這裡提示一下，漲的慢和IO效能沒關係，上面已經分析過了，時間都消耗在CPU上

接下來，讀者朋友們，可以停一下，把上述現象總結一下，再思考個幾分鐘、

如果是您來接這個CASE，你會怎麼繼續往下查呢？

↓

↓

↓

↓

↓

↓

↓

↓

↓

不要走開後邊還有.....

2.7 沒有等待事件如何往下查

既然SQL執行是在CPU上，那麼就不會有什麼等待事件的線索留出來，既然在CPU上，那麼必然要去看call stack,這是小y多年養成的習慣了。

通過oradebug short_stack,間隔幾秒抓取了三次。如下圖所示：

到了這裡，小y已經做完了所有的檢查。

是時候出去抽一根菸了，需要把所有發現的線索在腦子裡過一次。

幸運的是，一根菸後，小y終於把所有問題都想明白了，所有看到的現象都可以說清楚了,還有領導最關心的一個問題—“為什麼現在有問題，但是以前沒有問題呢”！，小y也有了答案。

建議朋友們，讀到這裡也可以先停一下，思考個幾分鐘，看看自己是否已經找到了問題原因。

到這裡已經找到答案的朋友，可以發小y發一份簡歷，說明你有不錯的思考能力和經驗！歡迎你加入中亦科技Oracle服務團隊！簡歷請發 51994106@qq.com

2.8 第二次頭腦風暴

在出門抽菸的這一小會功夫裡，小y不斷思考著幾個問題。

? 為什麼每個小時才處理幾千個邏輯讀呢？

? SQL執行時間都消耗在CPU上，都在做什麼呢？

? 為什麼以前不出，現在出呢？

下圖的這個函式qerhjWalkHashBucket，將所有問題都徹底解釋清楚了! qerhjWalkHashBucket就表示在做hash join的過程中需要遍歷hash bucket中的資料。

因此，小y重新縷了一下Hash Join原理，例如兩張表A和B表的整表關聯

SELECT * FROM A,B

WHERE A.ID=B.ID

ORACLE內部的執行過程，可以簡化為：

? SCAN A（掃描A表）

? HASH(A.ID),打散到各個桶（BUCKET）中，呆在pga hash area中等待別人來匹配

? SCAN B（掃描B表）

? HASH(B.ID)

? 到相應的Bucket中，比較表關聯欄位的值是否相同，返回或丟棄

HASH的目的是為了打算資料到各個桶中。每個演算法都有優缺點。

那麼HASH JOIN有什麼缺點呢？

我們是否命中了該缺點呢？！

2.9 真相浮出水命

很顯然，當驅動表在記憶體中裡的其中一個桶裡 （bucket）的資料很多的時候，那麼被驅動表的一個值到該桶裡比較起來就很需要遍歷更多的資料，這個時候就類似於nest loop了。那麼一個值的比對就需要很久了！

被驅動表一個BLOCK可以儲存幾十到幾百條記錄，而一條記錄需要到一個記錄很多的桶裡去比較很久，被驅動表一個BLOCK有很多條記錄，自然就出現了每個小時只能處理幾千個邏輯讀的情況了！也就觀察到了v$session_longops.sofar漲的很慢的情況了！

同時，關聯欄位大量比較的過程是很消耗CPU的 （當驅動表讀進PGA裡後就呆在PGA記憶體中了）

那麼為什麼以前不出呢？ 那是因為以前驅動表的關聯欄位的資料分佈是均勻的！而自從某一天以後，表關聯欄位的分佈開始不均勻了！

發出SQL，驗證如下:

可以看到，驅動表small_table中id=0的記錄數達到17萬條，意味著一個bucket的資料至少達到17萬條，這與hash join打散資料到各個bucket,通常一個bucket的資料不超過5條的想法和設計初衷是相違背的！

至此，所有問題得到了圓滿的解答！

2.10 進一步驗證

在SQL語句中加入small_table.id != 0的過濾條件，small_table的資料從160萬減少到143萬，變化不大的情況下，執行上述SQL，執行時間在3分鐘左右就完成了！

這就驗證了hash join不適合驅動表表關聯欄位分佈不均勻的一個缺點 ！

2.11 解決方案

知道原因了，那麼解決方案就多種多樣了！

hash join不適合驅動表表關聯欄位分佈不均勻的情況，因此解決方案有多種

1） 採用use_merge的hint而非use_hash，無法修改程式的情況可以通過sql profile指定執行計劃。這裡兩張表都不大，排序合併連線也很快。

2） 對驅動表small_table.id=0的資料進行調查、確認和處理，為什麼會在某一天突然出現大量id=0的資料，是否可以刪除

……

2.12 經驗提示

可以看到：

? 掌握原理是必須的

? 什麼樣的架構、演算法和儲存結構決定了他可以做什麼樣的事情，不可以做什麼樣的事情

? 但你思考過他的缺點是什麼麼？以前沒有的話，小y建議嘗試，讓你有更多收穫

 

About Me

....................................................................................................................................................

本文來自於微信公眾號轉載文章，若有侵權，請聯絡小麥苗及時刪除

ITPUB BLOG：http://blog.itpub.net/26736162

QQ：642808185 若加QQ請註明您所正在讀的文章標題

【版權所有，文章允許轉載，但須以連結方式註明源地址，否則追究法律責任】

....................................................................................................................................................

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2083724/，如需轉載，請註明出處，否則將追究法律責任。