故障处理】队列等待之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      故障环境介绍

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  回滚段

2.   设置参数 _rollback_segment_count   ：表示有多少 rollback segment  要处于 online  的状态；可以将该数值设置为数据库最繁忙的时候的回滚段数目。

这里以  ＂  _  ＂  开头的为隐藏参数，通过  show parameter   是看不到的，可以通过以下语句：

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