一個簡單的優化例子(鎖問題)

 前一陣剛給同僚做完一個tuning的教育訓練，順便把以前做過的case重新整理了一下，今天發出來。

case的開始是客戶開始抱怨一個批量處理的操作現在變得很慢，在開發的幫助下，我們可以在生産環境中輕易的測試這個操作，而且發現響應時間确實是比以前慢了。這一般是個好的開始，因為我們至少知道問題是什麼。

首先，我們通過session級别的trace，發現這個批處理在執行的過程中存在最多的等待事件是enqueue：

所有的迹象都指向了一個SQL的lock問題，讓我們看看這個SQL：

select rowid, bsr.*

from

BATCH_SUB_REQUEST bsr where bsr.BSR_REQUEST_ID=:1 and bsr.BSR_item=(select

  bsr2.BSR_ITEM from BATCH_SUB_REQUEST bsr2 /*+ INDEX (bsr2

  idx_batch_sub_request) */ where (bsr2.BSR_STATUS='new' )  and

  bsr2.BSR_request_id = bsr.BSR_request_id  and rownum=1) for update

這是一個select ... for update的語句，目的是為了從batch_sub_request這個表裡面選出一行，這裡面的條件rownum=1引起了我的注意，因為這會使select鎖定這張表的地一樣，我知道系統上一般會有10個左右程序會一起運作這個同樣的select，按照這種寫法，同一時間隻能有一個select ... for update可以成功，其他的程序必須等待，而等待事件就是enqueue。這顯然是有問題的。

我就這個問題和開發組的同僚交流了一下，開發的同僚一方面同意這個SQL是有問題，但另一方面也向我提出了另一個費解的問題，同樣的設計，為什麼在測試環境下性能就是好的呢？

這确實是個很奇怪的問題，我們通過在測試環境下重做這個批處理的操作，并且trace下來，發現測試環境下雖然最大的等待事件還是enqueue，但每次等待的時間都是很短的，隻需要零點幾秒，而在生産環境中每次enqueue的等待時間是2秒以上。

我仔細的看了生産上的trace檔案的每一個環節，發現select ... for update之後應該馬上做一個update并且commit，進而釋放鎖。從這個角度來看，其他程序等待lock的時間應該取決于這個update的速度，但是當我興高采烈的檢查update的性能的時候，發現這個update隻需要0.01秒都不到的時間。在10個左右的并發情況下，等待最長的那個程序也不應該等待超過0.1秒！

到底我們遺漏什麼？最後的可能性是select ... for update和update之間有些東西被我們忽略了，帶着這個問題我和開發再次對整個流程交流了一下，終于發現原來當我們做完select ... for update之後，系統還要做一件事情，那就是把select出來的結果發到前端伺服器的java程式，前端的java程式要做一些處理。“但是，”開發說，“我們肯定前端的這個處理是很快的，絕不會超過0.1秒”。如果資料庫和應用都很快，那慢的的東西就隻能有一個了--------網絡。

在我們的環境了，有9太前端伺服器，3台在美國，3台在亞洲，3台在歐洲，為了提高這個批處理操作的性能，我們允許所有的9台前端伺服器幫助處理一個批處理操作，這本來是個好的想法，但是當亞洲的前端成功的做完了select ... for update, 把結果發回給亞洲的前端，亞洲的前端處理好之後再傳回下一條指令給資料庫，這個過程中如果網絡很慢的話可能需要5秒以上，要知道，在這5秒裡，所有的其他8台前端的程序都在等待enqueue，這就是enqueue很大的原因！在我們的情況裡，本來希望更多的server可以提高性能，但由于網絡原因導緻部分網絡比較差的server堵塞了好的server。

之後我們單獨用美國的前端，亞洲的前端，歐洲的前端做測試，發現隻用美國前端的時候性能很好，而隻用亞洲或歐洲的前端的時候性能都很差，是以問題就是慢的網絡導緻了大量的enqueue。

這個案例其實很簡單，關鍵是：

1, 開始和開發溝通不充分，漏掉了很重要的環節，主要我自己沒多少開發經驗。

2, 我自己也有點思維定勢，看到SQL*Net message from client很高也沒在意，看到enqueue就一頭撲上去了。其實如果我們在系統級别監控如果發現SQL*Net message from client很高，可能是很難判斷到底應不應該忽略，但如果是在session級别監控到，就要具體分析了。

3，設計還是很重要，這裡如果在設計階段就把lock的影響考慮進去，也不會最後在生産上由于網絡不好導緻問題。

隻用AM的前端監控到的性能：