hanganalyse(文档 ID 130874.1)

最新推荐文章于 2021-04-09 02:53:30 发布
逝鸿
最新推荐文章于 2021-04-09 02:53:30 发布
阅读量296
点赞数
hanganalyse(文档 ID 130874.1)

事件：HANGANALYZE
~~~~~~~~~~~~~~~~~
注意事项:不应由客户设置，除非Oracle支持服务建议这样做。 在设置任何事件之前请阅读文档：75713.1。

版本/使用
~~~~~~~~~~~
8.0.6以上，尝试转储信息来诊断挂起的数据库

摘要语法：
~~~~~~~~~~~~~~
  ALTER SESSION SET EVENTS 'immediate trace name HANGANALYZE level LL';
  EVENT="60 trace name HANGANALYZE level 5"
  ORADEBUG hanganalyze LL

请注意，在11g +中，“ORADEBUG HANGANALYZE NN”表单还将尝试在3级或更高级别的hanganalyze链中包含SHORT_STACK转储。 事件触发的HANGANALYZE（如来自ALTER SESSION）和ORADEBUG HANGANALYZE nn中的“ORADEBUG DUMP HANGANALYZE nn”都不包含在短堆栈中，仅来自ORADEBUG HANGANALYZE nn（没有DUMP关键字）。

级别：
~~~~~~
级别确定系统上的哪些进程被要求转储错误检测。 主要级别是：
 10	转储所有进程（不是一个好主意）
 6	等待链中涉及的进程的转储错误检查（可能是高开销）
 5	转储等待链中涉及的所有进程（可以是很多）
 4	在等待链中转储叶节点
 3	只转储过程被认为是在一个挂起<<<最常见的级别
 2	最小输出
 1	非常小的输出

说明/步骤：
~~~~~~~~~~~~~~~~~~
这个事件通常在数据库hang住的场景中使用，或者在可以设置为在ORA-60上触发的死锁情况下使用。
Hanganalyze试图通过构建等待链来确定谁正在等待谁，然后根据级别请求各种进程来转储他们的错误信息。 这与手动操作非常相似，但速度更快。

一个合理的默认选项是使用“ORADEBUG HANGANALYZE 3”，因为这将尝试包括短堆栈（11g +），没有额外的转储输出。

注意：
   **使用太高的级别会导致大量的进程被要求转储他们的堆栈。 这可能是非常消耗资源的。

   **这个事件可能导致实例崩溃（可能性很小）

   **请注意，该命令可能无法在NT等平台上运行。

一旦hanganlyze完成，你需要收集所有转储的跟踪文件。 

示例输出/解释输出：
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  输出是这种格式(这里我手动做了一个行锁，会话39在等会话1)：

===============================================================================
HANG ANALYSIS:
  instances (db_name.oracle_sid): orcl.orcl
  oradebug_node_dump_level: 3
  os thread scheduling delay history: (sampling every 1.000000 secs)
    0.000000 secs at [ 07:30:49 ]
      NOTE: scheduling delay has not been sampled for 1.017626 secs    0.000000 secs from [ 07:30:46 - 07:30:51 ], 5 sec avg
    0.000000 secs from [ 07:29:51 - 07:30:51 ], 1 min avg
    0.000000 secs from [ 07:25:51 - 07:30:51 ], 5 min avg
  vktm time drift history
===============================================================================

Chains most likely to have caused the hang:
 [a] Chain 1 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'
     Chain 1 Signature Hash: 0x38c48850

===============================================================================
Non-intersecting chains:

-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
    Oracle session identified by:
    {
                instance: 1 (orcl.orcl)
                   os id: 1944
              process id: 29, oracle@11g (TNS V1-V3)
              session id: 39
        session serial #: 7
    }
    is waiting for 'enq: TX - row lock contention' with wait info:
    {
                      p1: 'name|mode'=0x54580006
                      p2: 'usn<<16 | slot'=0x30003
                      p3: 'sequence'=0x371
            time in wait: 50.580944 sec
           timeout after: never
                 wait id: 12
                blocking: 0 sessions
            wait history:
              * time between current wait and wait #1: 0.000844 sec
              1.       event: 'SQL*Net message from client'
                 time waited: 23.999476 sec
                     wait id: 11              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #1 and #2: 0.000000 sec
              2.       event: 'SQL*Net message to client'
                 time waited: 0.000000 sec
                     wait id: 10              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #2 and #3: 0.000007 sec
              3.       event: 'SQL*Net message from client'
                 time waited: 0.000008 sec
                     wait id: 9               p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
    }
    and is blocked by
 => Oracle session identified by:
    {
                instance: 1 (orcl.orcl)
                   os id: 1836
              process id: 19, oracle@11g (TNS V1-V3)
              session id: 1
        session serial #: 5
    }
    which is waiting for 'SQL*Net message from client' with wait info:
    {
                      p1: 'driver id'=0x62657100
                      p2: '#bytes'=0x1
            time in wait: 1 min 26 sec
           timeout after: never
                 wait id: 1453
                blocking: 1 session
            wait history:
              * time between current wait and wait #1: 0.000003 sec
              1.       event: 'SQL*Net message to client'
                 time waited: 0.000002 sec
                     wait id: 1452            p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #1 and #2: 0.000810 sec
              2.       event: 'SQL*Net message from client'
                 time waited: 25.418984 sec
                     wait id: 1451            p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #2 and #3: 0.000002 sec
              3.       event: 'SQL*Net message to client'
                 time waited: 0.000001 sec
                     wait id: 1450            p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
    }

Chain 1 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'
Chain 1 Signature Hash: 0x38c48850
-------------------------------------------------------------------------------

===============================================================================
Extra information that will be dumped at higher levels:
[level  4] :   1 node dumps -- [LEAF] [LEAF_NW]
[level  5] :   1 node dumps -- [NO_WAIT] [INVOL_WT] [SINGLE_NODE] [NLEAF] [SINGLE_NODE_NW]

State of ALL nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
[0]/1/1/5/0xa53b65a0/1836/LEAF/
[38]/1/39/7/0xa5342460/1944/NLEAF/[0]

*** 2017-11-07 07:30:50.684
===============================================================================
END OF HANG ANALYSIS
===============================================================================

*** 2017-11-07 07:30:50.687
===============================================================================
HANG ANALYSIS DUMPS:
  oradebug_node_dump_level: 3
===============================================================================

State of LOCAL nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
[0]/1/1/5/0xa53b65a0/1836/LEAF/
[38]/1/39/7/0xa5342460/1944/NLEAF/[0]


No processes qualify for dumping.

===============================================================================
HANG ANALYSIS DUMPS: END
===============================================================================    
    
    
节点状态
~~~~~~~~~~~
    IGN		忽略
    LEAF	等待的叶节点
    LEAF_NW	正在运行（使用CPU？）叶节点
    NLEAF	链中的一个元素，但不在结尾（不是叶）
    
    
有关：
  <事件：ERRORSTACK>
  <事件：PROCESSSTATE>
  <事件：SYSTEMSTATE>