本文為墨天輪社群作者 [**張sir**](https://www.modb.pro/u/2623?1020moli) 原創作品，記錄了日常運維Oracle資料庫過程中遇到的一個慢SQL問題的解決、最佳化過程，文章內容全面具體、分析到位，且含有經驗總結，分享給各位。

![](https://oscimg.oschina.net/oscnet/up-689a19bf628bac22feb211b575a46c76d94.png)

## 問題現象

這次出問題的資料庫比較特殊，承接的系統交易要求很高，SQL基本都是短平快，響應時間基本不能超過50ms，某天凌晨的01:12-01:14在進行壓力測試的時候，突然出現短暫的交易延遲變長的情況，有部分交易超時。應用定位到是資料庫返回慢了，要求我們排查問題。

## 問題分析：

#### 步驟一：come on  v$ASH

分析這種問題，我是特別喜歡用v$active\_session\_history檢視的，雖然oracle也提供了ASH報告的功能，但是總感覺報告提供的內容太多，沒法抓住重點。直接查檢視，想看什麼資訊都可以。首先先看看問題時段整體的資料庫sql的執行情況，SQL執行時間最長已經到40s左右了，這個時間肯定是無法接受的，而且基本上都是兩條SQL：cuw23huyg926x和84m7xzxz0181g。

![](https://oscimg.oschina.net/oscnet/up-4ff068e72613f7eb9d1b0850db3246613c3.png)

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

![](https://oscimg.oschina.net/oscnet/up-a8cf4b5cce1d852b6bd2b5336ff18ff34be.png)

84m7xzxz0181g：

![](https://oscimg.oschina.net/oscnet/up-5d912daa87062a585bef0f5af839f668352.png)

####  步驟二：等待事件分析

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看看具體等待的字典緩衝型別

![](https://oscimg.oschina.net/oscnet/up-134cb3ed80e57de8733b8992ce3c8c8a6b7.png)

3）看一下等待事件row cahce lock等待的物件是什麼，row cache lock 等待事件的P1引數為cache id，根據cache id找到dc,可以看到大部分是等待獲取dc\_rollback\_segments，這個等待時間也是在等待獲取undo資訊。

![](https://oscimg.oschina.net/oscnet/up-a09518dab0b62cf7951bc4cb229d57e54c7.png)

4）第一個等待事件是字典緩衝的爭用，爭用的物件是rollback segments，第二個等待事件enq: US - contention也是關於undo segment的等待。根據以上情況可以看出，row cache lock和enq:U - contention是有相關性的，都是由於獲取undo的時候產生的爭用。但是這個系統其實是採用的分庫分表的架構，有多個對等角色的資料庫，每套資料庫交易量基本一致，那為什麼這套庫有問題，而別的庫沒問題呢？

#### 步驟三、繼續AWR+v$ASH

以上問題也是我自己的疑問，多套配置相同的資料庫，交易量一致，如果說是由於undo引發的，那為啥其他庫沒問題，單單這套庫有問題？  感覺問題的根源還不在這裡。繼續仔細檢視

awr報告，發現問題庫的rac節點2的等待事件中有log file sync等待，按理說這是一個正常等待，但是其等待時間超過了2ms，而其他幾套庫的該等待事件時間都是幾百us。

![](https://oscimg.oschina.net/oscnet/up-1f7b4ac3fb8169fdde53ac3eb8aee6ab08f.png)

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

![](https://oscimg.oschina.net/oscnet/up-fe7d875b7fdd728d816d4072be00d32aef9.png)

去V$ASH裡檢視這個等待事件的時間分佈情況，發現在01:12:16s的時候，log file parallel write執行超過了1s。

![](https://oscimg.oschina.net/oscnet/up-50e9a67f078a89afe4726faf68bd8b74052.png)

到這裡，基本上可以把證據鏈梳理下了：

壓力測試期間，交易量突然上升-----》online的undo segment不足-----》資料庫online undo segment-----》發生US鎖爭用

而IO突然的堵塞，事務無法正常提交，加劇了undo的爭用。針對這個問題，我們設定了\_rollback\_segment_count 引數，表示有多少rollback segment要處於online的狀態；可以將該數值設定為資料庫最繁忙的時候的回滾段數目。

## 總結：

**1、** 如果這個系統只有一套資料庫，分析到第二步可能就結案了，有同樣分庫發表的兄弟庫做對比，別人都沒問題，就你有問題，那說明還有更深層次的原因。

**2、** 這種IO瞬時的延遲，基本上無解，尤其對於高併發系統，一個IO抖動就可能導致資料庫堵塞。整個儲存鏈路也不可能百分之百的一直處於高質量的狀態。

**3、** 對於從v$ash中查到log file parallel write有1s的延遲，這個值的準確性我持懷疑態度，從底層的os、虛擬化、光纖交換機、儲存都沒看到這麼高的延遲。有對這個有研究的老鐵，可以討論下。

---
閱讀原文：[https://www.modb.pro/db/486407](https://www.modb.pro/db/486407?1020moli)

> 本文為[【墨力原創作者計劃】](https://www.modb.pro/db/513210?1020moli)徵文活動投稿作品，活動收錄了數百篇Oracle、MySQL、PostgreSQL以及國產資料庫相關的文章，包含資料庫安裝配置、效能調優、故障處理、高可用搭建等主題，此外也有K8s、Java、VUE等優質稿件。大家[**點選此處**](https://www.modb.pro/tag/%E5%A2%A8%E5%8A%9B%E8%AE%A1%E5%88%92?type=knowledge)可檢視有所技術文章。更多**資料庫故障處理文章**可以[**點選此處**](https://www.modb.pro/tag/%E6%95%85%E9%9A%9C%E5%A4%84%E7%90%86?type=knowledge)檢視。

【墨力原創作者計劃】活動長期進行中，首次參與活動即有機會獲得定製護腰靠枕；參與月更挑戰，還可以獲得定製隨身碟、羅技滑鼠、華為手環、100-300元現金獎勵等獎品，期待您的參與！
> 具體活動規則可以檢視：[https://www.modb.pro/db/513210](https://www.modb.pro/db/513210?1020moli)

好文分享 | 記一次Oracle12c資料庫SQL短暫緩慢問題分析

相關文章