【故障處理】佇列等待之enq: US - contention案例

1    BLOG 文件結構圖

 

 

2    前言部分

2.1    導讀和注意事項

各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識， ~O(∩_∩)O~ ：

① enq: US - contention 等待事件的解決

② 一般等待事件的解決辦法

③ 佇列等待的基本知識

  Tips：

① 本文在 ITpub （ http://blog.itpub.net/26736162 ）、部落格園 ( http://www.cnblogs.com/lhrbest )和微信公眾號（ xiaomaimiaolhr ）有同步更新

② 文章中用到的所有程式碼，相關軟體，相關資料請前往小麥苗的雲盤下載（ http://blog.itpub.net/26736162/viewspace-1624453/ ）

③  若文章程式碼格式有錯亂，推薦使用搜狗 、 360 或QQ 瀏覽器，也可以下載 pdf 格式的文件來檢視， pdf 文件下載地址： http://blog.itpub.net/26736162/viewspace-1624453/ ，另外 itpub 格式顯示有問題，可以去部落格園地址閱讀

④  本篇 BLOG 中命令的輸出部分需要特別關注的地方我 都用 灰色背景和粉紅色字型 來表 示，比如下邊的例子中， thread 1 的最大歸檔日誌號為 33 ， thread 2 的最大歸檔日誌號為 43 是需要特別關注的地方；而命令 一般使用 黃色背景和紅色字型 標 注；對程式碼或程式碼輸出部分的注 釋一般採用 藍色字型 表示 。

 

本文如有錯誤或不完善的地方請大家多多指正， ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。

 

3    故障分析及解決過程

 

3.1    故障環境介紹

 

專案	source db
db  型別	RAC
db version	11.2.0.4.0
db  儲存	ASM
OS版本及 kernel 版本	AIX 64位  7.1.0.0

 

3.2    故障發生現象及報錯資訊

最近系統做壓測，碰到的問題比較多，今天同事發了個 AWR 報告，說是系統響應很慢，我簡單看了下，簡單分析下吧：

 

270分鐘時間而 DB Time 為 2000 多分鐘， DB Time 太高了，負載很大，很可能有異常的等待事件，系統配置還是比較牛逼的。

 

事務量很大，其它個別引數有點問題，不一一解說了。 等待事件很明顯了：

 

AWR的其它部分就不分析了，首先 這個等待事件： enq: US - contention 比較少見，查了一下 資料，有點收穫：

SELECT   *   FROM  V$EVENT_NAME  WHERE   NAME   =   'enq: US - contention' ;

 

  SELECT   *   FROM  v$lock_type d  WHERE  d.TYPE = 'US' ;

 

"enq: US - contention" ，這個 event 說明事務在佇列中等待 UNDO Segment ，通常是由於 UNDO 空間不足導致的。

在對此事件說明之前，需要理解在使用 AUM （ atuomatic undo management ）時，回滾段在何時聯機或離線。 AUM 與 RBU （ rollback segment management ）不同，回滾段的管理 是 O racle 自動完成的。使用 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 鎖而等待，等待佔有可用回滾段。

這是 oracle10g 中開始出現的 bug( 在 11.1.0.7 中仍有這個 BUG) ，當因為系統 activity 增加或者降低的時候， oracle SMON 程式會自動 ONLINE 或者 OFFLINE rollback segments 。這樣導致某些與 undo segments 相關的 latch 或者 enqueue 被 hold 住太長時間，導致系統很多活躍 session 都開始等待 enq: US - contention 。可以同時使用以下解決方法 :

1.  設定 event 讓 SMON 不自動 OFFLINE 回滾段

alter system set events '10511 trace name context forever, level 1';

 

2.  設定引數 _rollback_segment_count  ：表示有多少 rollback segment 要處於 online 的狀態；可以將該數值設定為資料庫最繁忙的時候的回滾段數目。

alter system set "_rollback_segment_count"=1000 SID='*';

這裡以 ＂ _ ＂ 開頭的為隱藏引數，透過 show parameter  是看不到的，可以透過以下語句：

select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from xksppia,xksppia,xksppcv b
where a.indx = b.indx
and a.ksppinm like '%_rollback_segment_count%';

 

3.  undo autotune bug 多多。最好 disable 。

alter system set "_undo_autotune"= false;

這種方法就是關閉了 UNDO 的自動調整功能， 同時 也能解決掉 UNDO 表空間會在很長時間都一直保持著使用率是接近 100% 的問題。

 

4.   有一個 patch: A fix to bug 7291739 is to set a new hidden parameter, _highthreshold_undoretention to set a high threshold for undo retention completely distinct from maxquerylen.

alter system set "_highthreshold_undoretention"=;

 

5.   增加undo 表空間

alter tablespace UNDOTBS1 add datafile '+DATA1' size 30G;

 

6.   設定undo表空間的 NOGUARANTEE

select tablespace_name, retention from dba_tablespaces where tablespace_name like 'UNDO%';

ALTER TABLESPACE UNDOTBS RETENTION NOGUARANTEE;

 

7.   減少UNDO_RETENTION 的時間

SQL> show parameter undo

NAME                                  TYPE         VALUE

------------------ ---------------------- --------

undo_management                     string        AUTO

undo_retention                      integer       10800

 

8.   重啟資料庫節點

 

3.3    故障分析及解決

我們查詢 ASH 檢視看看當時的情況：

  SELECT  D.SQL_ID , CHR ( BITAND ( P1 ,   - 16777216 )   /   16777215 )   ||

         CHR ( BITAND ( P1 ,   16711680 )   /   65535 )  "Lock" ,

         BITAND ( P1 ,   65535 )  "Mode" ,   COUNT ( 1 ), COUNT ( DISTINCT  d.session_id  )

   FROM  DBA_HIST_ACTIVE_SESS_HISTORY D

  WHERE  D.SAMPLE_TIME  BETWEEN  TO_DATE ( '2016-09-07 17:39:44' ,   'YYYY-MM-DD HH24:MI:SS' )   AND

       TO_DATE ( '2016-09-07 22:13:41' ,   'YYYY-MM-DD HH24:MI:SS' )

    AND  D.EVENT  =   'enq: US - contention'

  GROUP   BY  D.SQL_ID ,( CHR ( BITAND ( P1 ,   - 16777216 )   /   16777215 )   ||

         CHR ( BITAND ( P1 ,   16711680 )   /   65535 )),( BITAND ( P1 ,   65535 ));

 

LOCK為 US ， MODE 為 6 ，看看具體的 SQL 內容：

  SELECT  A.SQL_TEXT ,  A.EXECUTIONS ,  A.MODULE ,  A.SQL_ID

  FROM  V$SQL A

WHERE  A.SQL_ID  IN   ( '5ww8x9u15a90y' ,

  'ayngk81z8fh0m' ,

  '1cmnjddakrqbv' ,

  '26ad9zvt5xgb3' );

 

看看時間段是哪個區間：

SELECT  D.SQL_ID ,  TO_CHAR ( D.SAMPLE_TIME ,   'YYYY-MM-DD HH24:MI' ),   COUNT ( 1 )

   FROM  DBA_HIST_ACTIVE_SESS_HISTORY D

  WHERE  D.EVENT  =   'enq: US - contention'

    AND  D.SQL_ID  IN   ( '5ww8x9u15a90y' ,   '26ad9zvt5xgb3' )

  GROUP   BY  D.SQL_ID ,  TO_CHAR ( D.SAMPLE_TIME ,   'YYYY-MM-DD HH24:MI' );

 

看來問題幾種在這 2分鐘之內。 基本都是對同一個表做插入或者更新操作：

  SELECT   DISTINCT  D.CURRENT_OBJ#

    FROM  DBA_HIST_ACTIVE_SESS_HISTORY D

   WHERE  D.SAMPLE_TIME  BETWEEN

        TO_DATE ( '2016-09-07 17:39:44' ,   'YYYY-MM-DD HH24:MI:SS' )   AND

        TO_DATE ( '2016-09-07 22:13:41' ,   'YYYY-MM-DD HH24:MI:SS' )

     AND  D.EVENT  =   'enq: US - contention'

   GROUP   BY  D.CURRENT_OBJ# ;

SELECT   *   FROM  DBA_OBJECTS a  WHERE  a.object_id  IN    ( '87620' , '87632' , '87663' , '87667' , '87686' , '87684' , '87688' , '87626' , '87646' , '87642' , '87639' , '87661' , '87628' , '87675' , '87643' , '87677' , '87660' , '87631' , '87629' , '87668' , '87682' , '87685' , '87654' , '87640' , '87627' , '87636' , '87664' , '87655' , '87645' , '87637' , '87669' , '87673' , '87666' , '87634' , '87644' , '87672' , '87648' , '87649' , '87662' , '87651' , '87641' , '87653' , '87659' , '87680' , '87681' , '0' , '87625' , '87670' , '87658' , '87674' , '87671' , '87633' , '87679' , '87647' );

 

 

可以看到操作的表是一個分割槽表。

解決方案：

alter tablespace UNDOTBS1 add datafile '+DATA1' size 30G;

alter system set events '10511 trace name context forever,level 1';

ALTER SYSTEM SET "_rollback_segment_count"=1000 SID='*';

 

執行之後經過開發進行壓測，已經沒有該等待事件的產生了：

SELECT  D.SQL_ID ,  TO_CHAR ( D.SAMPLE_TIME ,   'YYYY-MM-DD HH24:MI' ),   COUNT ( 1 )

   FROM  DBA_HIST_ACTIVE_SESS_HISTORY D

  WHERE  D.EVENT  =   'enq: US - contention'  

  GROUP   BY  D.SQL_ID ,  TO_CHAR ( D.SAMPLE_TIME ,   'YYYY-MM-DD HH24:MI' );

 

查詢無資料。

轉自： 【故障處理】佇列等待之enq: US - contention案例_ITPUB部落格