故障處理】佇列等待之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  回滾段

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

 

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

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

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

1 2 3 4

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'  );