問題現象
這次出問題的數據庫比較特殊,承接的系統交易要求很高,SQL基本都是短平快,響應時間基本不能超過50ms,某天凌晨的01:12-01:14在進行壓力測試的時候,突然出現短暫的交易延遲變長的情況,有部分交易超時。應用定位到是數據庫返回慢了,要求我們排查問題。
問題分析:
步驟一:come on v$ASH
分析這種問題,我是特別喜歡用v$active_session_history視圖的,雖然oracle也提供了ASH報告的功能,但是總感覺報告提供的內容太多,沒法抓住重點。直接查視圖,想看什么信息都可以。首先先看看問題時段整體的數據庫sql的執行情況,SQL執行時間最長已經到40s左右了,這個時間肯定是無法接受的,而且基本上都是兩條SQL:cuw23huyg926x和84m7xzxz0181g。

既然找到了慢SQL,那就看看他們的主要的等待事件吧, 通過下面的查詢可以看出84m7xzxz0181g主要等待事件是enq: US - contention和row cache lock,cuw23huyg926x的主要等待事件是enq: US - contention。

84m7xzxz0181g:

步驟二:等待事件分析
1) 先簡單看下這兩個等待事件哈:
row cache lock等待事件是一個共享池相關的等待事件,是由于對字典緩沖的訪問造成的。每一個行緩沖隊列鎖都對應一個特定的數據字典對象,這被叫做隊列鎖類型,并可以在V$ROWCACHE視圖中找到。在AWR中需要查看Dictionary Cache Stats部分用以確定問題。常見的原因有如下幾點:
① 序列沒有設置CACHE屬性,導致序列爭用。
② 表空間不足引起 表空間的擴展速度跟不上表空間的使用速度會發生該等待事件。
③ Shared Pool不足,需要增加共享池。
④ 用戶密碼錯誤或給出了空密碼并且頻繁登錄。
enq: US - contention:這個event說明事務在隊列中等待UNDO Segment,通常是由于UNDO空間不足導致的。
在對此事件說明之前,需要理解在使用AUM(atuomatic undo management)時,回滾段在何時聯機或脫機。AUM與RBU(rollback segment management)不同,回滾段的管理是Oracle自動完成的。使用AUM時,回滾段的聯機或脫機的時刻如下:
1)在執行alter database open的時候將回滾段聯機
2)通過alter system set undo_tablespace=xxx 修改撤銷表空間時,將原來的回滾段脫機后,再將新的回滾段聯機。
3)通過SMON,自動脫機或者聯機回滾段,如果一段時間內,事務量增加,聯機狀態的回滾段也會增加,一段時間內若是沒有實物或事務減少,回滾段就會被smon進程脫機。
為了同步將回滾段聯機或脫機的過程,執行該工作的服務器進程或后臺進程應獲得US鎖,每個回滾段非配一個US鎖,ID1=Undo segment#。若在獲得US鎖的過程中發生爭用,則等待enq:US-contention事件。服務器進程應該在開始事務時分配到回滾段,但如果不存在可用的回滾段時,應該創建新的回滾段或將脫機狀態的回滾段聯機。在實現此項工作期間,服務器進程為了獲得US鎖而等待,等待占有可用回滾段。
2)第一個等待事件是跟共享池相關的等待事件,我們可以通過v$ash看看具體等待的字典緩沖類型

3)看一下等待事件row cahce lock等待的對象是什么,row cache lock 等待事件的P1參數為cache id,根據cache id找到dc,可以看到大部分是等待獲取dc_rollback_segments,這個等待時間也是在等待獲取undo信息。

4)第一個等待事件是字典緩沖的爭用,爭用的對象是rollback segments,第二個等待事件enq: US - contention也是關于undo segment的等待。根據以上情況可以看出,row cache lock和enq:U - contention是有相關性的,都是由于獲取undo的時候產生的爭用。但是這個系統其實是采用的分庫分表的架構,有多個對等角色的數據庫,每套數據庫交易量基本一致,那為什么這套庫有問題,而別的庫沒問題呢?
步驟三、繼續AWR+v$ASH
awr報告,發現問題庫的rac節點2的等待事件中有log file sync等待,按理說這是一個正常等待,但是其等待時間超過了2ms,而其他幾套庫的該等待事件時間都是幾百us。

再看下log file parallel write事件,平均等待時間是1.09ms,其他對等數據庫的等待時間只有幾百us。

去V$ASH里查看這個等待事件的時間分布情況,發現在01:12:16s的時候,log file parallel write執行超過了1s。

到這里,基本上可以把證據鏈梳理下了:
壓力測試期間,交易量突然上升-----》online的undo segment不足-----》數據庫online undo segment-----》發生US鎖爭用
而IO突然的堵塞,事務無法正常提交,加劇了undo的爭用。針對這個問題,我們設置了_rollback_segment_count 參數,表示有多少rollback segment要處于online的狀態;可以將該數值設置為數據庫最繁忙的時候的回滾段數目。




