前言

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

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

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

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

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

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

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

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

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

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

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

環境介紹：

操作系統 Redhat 64 bit

數據庫   Oracle 11.2.0.3 ,  2節點RAC

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

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

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

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

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

這里順便說一下：

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

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

可以看到：

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

?  表連接方式走的是hash join

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

?  表連接順序是小表做驅動表(hash內存表）

可以看到：

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

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

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

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

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

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

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

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

也許有人會說：

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

答案是NO！

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

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

執行時間基本都耗在CPU上，這通常意味著所需要的數據基本都在內存中。

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

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

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

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

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

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

可以看到：

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

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

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

將這條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，你會怎么繼續往下查呢？

↓

↓

↓

↓

↓

↓

↓

↓

↓

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

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

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

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

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

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

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

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

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

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

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

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

因此，小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有什么缺點呢？

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

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

同時，關聯字段大量比較的過程是很消耗CPU的 （當驅動表讀進PGA里后就呆在PGA內存中了）

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

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

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

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

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

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

……

可以看到：

?  掌握原理是必須的

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

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