一个RESOURCE MANAGER引起的问题分析

Chains most likely to have caused   the hang: 《《《最 终 阻塞源都是由于 resmgr:cpu quantum 引起。

 [a] Chain 1 Signature: ' resmgr:cpu quantum '<='buffer busy   waits'<='buffer busy waits'<='enq: TX - index contention'

     Chain 1 Signature Hash: 0xe7466825

 [b] Chain 2 Signature: ' resmgr:cpu quantum '<='buffer busy waits'

     Chain 2 Signature Hash: 0x23972dee

 [c] Chain 3 Signature: ' resmgr:cpu quantum '<='buffer busy waits'

     Chain 3 Signature Hash: 0x23972dee

====================================================================

Chain   1:

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

    oracle session identified by:

    {

                instance: 1 (jfdb.jfdb1)

                   os id: 78941

              process id: 1240, oracle@XXX-JF-DB03

              session   id: 22 <<<< session 22

        session serial #: 32445

    }

    is waiting for 'enq: TX - index   contention' with wait info:

    {

                      p1:   'name|mode'=0x54580004

                      p2: 'usn<<16 |   slot'=0x1cb0015

                      p3: 'sequence'=0xf3fa7

            time in wait: 0.122389 sec

           timeout after: never

                 wait id: 199367

                blocking: 0 sessions

            wait history:

              * time between current wait and   wait #1: 0.000318 sec

             ………

    }

and is blocked by <<<< 被阻塞

 => Oracle session identified by:

{

                instance: 1 (jfdb.jfdb1)

                   os id: 1349

process   id: 2756, oracle@XXX-JF-DB03

session id: 3320 <<<<session 3320

        session serial #: 3849

}

which   is waiting for ‘buffer busy waits’ with wait info:

{

p1:   ‘file#’=0x8c

p2:   ‘block#’=0x286540

p3:   ‘class#’=0x1

time   in wait: 0.218850 sec

           timeout after: never

                 wait id: 51286

                blocking: 58 sessions

wait   history:

 ……….

}

     and is blocked by <<<< 被阻塞

 => Oracle session identified by:

    {

                instance: 1 (jfdb.jfdb1)

                   os id: 3182

              process id: 2975, oracle@XXX-JF-DB03

              session   id: 5658 <<<session 5658

        session serial #: 181

    }

    which is waiting for 'buffer busy waits'   with wait info:

    {

                      p1: 'file#'=0x8c

                      p2: 'block#'=0x285b1f

                      p3: 'class#'=0x1

            time in wait: 0.219271 sec

           timeout after: never

                 wait id: 38737

                blocking: 63 sessions

            wait history:

              。。。。。。

    }

    and is blocked by<<<< 被阻塞

 => Oracle session identified by:

    {

                instance: 1 (jfdb.jfdb1)

                   os id: 27602

              process id: 2384, oracle@XXX-JF-DB03

              session id: 334   《《《session 334

        session serial #: 757

    }

    which is waiting for 'resmgr:cpu quantum'   with wait info:

    {

                      p1: 'location'=0x2

                      p2: 'consumer group   id'=0x3259

                      p3: ' '=0x0

            time in wait: 0.040860 sec

           timeout after: never

                 wait id: 95941

                blocking: 114 sessions

            wait history:

            。。。。。。。

    }

Chain   1 Signature: 'resmgr:cpu quantum'<='buffer busy waits'<='buffer busy   waits'<='enq: TX - index contention'

Chain   1 Signature Hash: 0xe7466825

《 WAITEVENT: "resmgr:cpu quantum" Reference   Note ( 文档 ID 2097889.1) 》

·   Event   'resmgr: cpu quantum' is a standard event used by resource manager to control the allocation of   CPU to processes .   When a session waits for 'resmgr: cpu quantum' that session is waiting to be   allocated a quantum of CPU time.

等待事件 'resmgr ： cpu quantum' 是 资源管理器用来控制 CPU 分配给进程 的标准事件。   当会话等待 'resmgr ： cpu quantum' 时， 会话正在等待分配一个 CPU 时间额度 。

This wait occurs when the resource   manager is enabled and is throttling CPU consumption. To reduce the   occurrence of this wait event, increase the CPU allocation for the session's   current consumer   group .

当启用资源管理器并限制 CPU 消耗时会发生此等待。 为了减少此等待事件的发生，请增加会话当前消费组的 CPU 分配 。

《 11g: Scheduler Maintenance Tasks or Autotasks ( 文档 ID 756734.1) 》

Current log# 6 seq# 23750 mem# 0: +DG_DATA/jfdb/onlinelog/group_6.279.909112437

Sun Apr 01 05:51:29 2018

ArcHived Log entry 46019 added for thread   1 sequence 23749 ID 0x4d5ab97e dest 1:

Sun Apr 01 06:00:00 2018 《《《《 6 点整开启自动化维护窗口

Setting Resource Manager plan   SCHEDULER[0x32DF]:DEFAULT_MAINTENANCE_PLAN via scheduler window

Setting Resource Manager plan   DEFAULT_MAINTENANCE_PLAN via parameter

Sun Apr 01 06:00:00 2018

Starting background process   VKRM 《《《启动相应的进程

Sun Apr 01 06:00:00 2018

VKRM started with pid=361, OS id=48797