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

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

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

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

③ 隊列等待的基本知識

Tips：

④ 本篇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皆可，您的批評指正是我寫作的最大動力。

項目

source db

db 類型

RAC

db version

11.2.0.4.0

db 存儲

ASM

OS版本及kernel版本

AIX 64位 7.1.0.0

最近系統做壓測，碰到的問題比較多，今天同僚發了個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）不同，復原段的管理是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. 重新開機資料庫節點

我們查詢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)

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 system set events '10511 trace name context forever,level 1';

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

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

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

查詢無資料。

About Me

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

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

● QQ群：230161599 微信群：私聊

● 聯系我請加QQ好友(642808185)，注明添加緣由

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

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

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

繼續閱讀

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

httpd服務的部署、啟動、配置和簡單優化一、部署二、啟動三、配置檔案

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

nginx 安裝錯誤資訊解決

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

Ambari介紹和架構原理

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述