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

guocun09發表於2022-08-04
佇列ENQ

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

1  BLOG文件結構圖

wps6E72.tmp 

 

 

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是需要特別關注的地方；而命令一般使用黃色背景和紅色字型標注；對程式碼或程式碼輸出部分的注釋一般採用藍色字型表示。

  List of Archived Logs in backup set 11

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

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

  1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29 11:15:48

  1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29 11:15:58

  2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29 11:15:49

  2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29 11:15:53

 

[ZHLHRDB1:root]:/>lsvg -o

T_XDESK_APP1_vg

rootvg

[ZHLHRDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

 

====》2097152*512/1024/1024/1024=1G 

 

本文如有錯誤或不完善的地方請大家多多指正，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報告，說是系統響應很慢，我簡單看了下，簡單分析下吧：

wps6E83.tmp 

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

wps6E84.tmp 

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

wps6E85.tmp 

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

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

wps6E86.tmp 

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

wps6E87.tmp 

"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鎖而等待，等待佔有可用回滾段。

這是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));

wps6E98.tmp 

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

wps6E99.tmp 

看看時間段是哪個區間：

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

wps6E9A.tmp 

看來問題幾種在這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');

wps6E9B.tmp 

 

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

解決方案：

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

 

查詢無資料。

 

  About Me

..........................................................................................................................................................................................................                        

●  本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用

● 本文在itpub（http://blog.itpub.net/26736162）、部落格園(http://www.cnblogs.com/lhrbest)和個人微信公眾號（xiaomaimiaolhr）上有同步更新，推薦pdf檔案閱讀

● QQ群：230161599 微信群：私聊

● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2124767/ 部落格園地址：http://www.cnblogs.com/lhrbest/articles/5858001.html

● 本文pdf版：http://yunpan.cn/cdEQedhCs2kFz （提取碼：ed9b）

● 小麥苗分享的其它資料：http://blog.itpub.net/26736162/viewspace-1624453/

●  聯絡我請加QQ好友(642808185)，註明新增緣由

● 於 2016-09-08 09:00~2016-09-08 19:00 在中行完成

● 【版權所有，文章允許轉載，但須以連結方式註明源地址，否則追究法律責任】

..........................................................................................................................................................................................................

長按識別二維碼或微信客戶端掃描下邊的二維碼來關注小麥苗的微信公眾號：xiaomaimiaolhr,學習最實用的資料庫技術。

wps6E9C.tmp

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2124767/，如需轉載，請註明出處，否則將追究法律責任。

相關文章