Hanganalyze分析会话阻塞—锁表

Hanganalyze分析会话阻塞—锁表

模拟阻塞会话

--会话1 SQL> select * from v$version;   BANNER ------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production PL/SQL Release 11.2.0.3.0 - Production CORE    11.2.0.3.0      Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production   SQL> select * from t_xifenfei;            ID NAME ---------- ----------------------------------------           1 xifenfei           2 www.xifenfei   SQL> delete from t_xifenfei where id=2;   1 row deleted.   --会话2 SQL> delete from t_xifenfei where id=2; --hang住

做hanganalyze

--sys登录 SQL> ORADEBUG setmypid Statement processed. SQL> oradebug unlimit; Statement processed. SQL> oradebug hanganalyze 3   Hang Analysis in /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_13719.trc

分析hanganalyze 文件

--HANG ANALYSIS基本信息 =============================================================================== HANG ANALYSIS:    instances (db_name.oracle_sid): ora11g.ora11g    oradebug_node_dump_level: 3    analysis initiated by oradebug    os thread scheduling delay history : (sampling every 1.000000 secs)      0.000000 secs at [ 15:53:16 ]        NOTE: scheduling delay has not been sampled for 0.356486 secs 0.000000 secs from [ 15:53:12 - 15:53:17 ], 5 sec avg      0.000000 secs from [ 15:52:17 - 15:53:17 ], 1 min avg      0.000000 secs from [ 15:48:17 - 15:53:17 ], 5 min avg ===============================================================================    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 (ora11g.ora11g)                     os id : 13634                process id : 21, oracle@xifenfei (TNS V1-V3)                session id : 143          session serial #: 281      } --等待信息      is waiting for 'enq: TX - row lock contention' with wait info:      {                        p1: 'name|mode' =0x54580006 --54580006 is split into ASCII 54 + ASCII 58 (TX) + Mode 0006 (X) ...  在等待TX mode=6                        p2: 'usn<<16 | slot' =0x20010                        p3: 'sequence' =0x356              time in wait: 1 min 56 sec             timeout after: never                   wait id : 24                  blocking: 0 sessions               current sql: delete from t_xifenfei where id =2               short stack: --省略              wait history :                * time between current wait and wait #1: 0.001471 sec                1.       event: 'SQL*Net message from client'                   time waited: 10.776765 sec                       wait id : 23              p1: 'driver id' =0x62657100                                                p2: '#bytes' =0x1                * time between wait #1 and #2: 0.000001 sec                2.       event: 'SQL*Net message to client'                   time waited: 0.000001 sec                       wait id : 22              p1: 'driver id' =0x62657100                                                p2: '#bytes' =0x1                * time between wait #2 and #3: 0.000028 sec                3.       event: 'SQL*Net message from client'                   time waited: 0.000032 sec                       wait id : 21              p1: 'driver id' =0x62657100                                                p2: '#bytes' =0x1      }      and is blocked by   => Oracle session identified by: --阻塞会话信息      {                  instance: 1 (ora11g.ora11g)                     os id : 13546                process id : 20, oracle@xifenfei (TNS V1-V3)                session id : 15          session serial #: 189      } --该会话处于空闲状态      which is waiting for 'SQL*Net message from client' with wait info:      {                        p1: 'driver id' =0x62657100                         p2: '#bytes' =0x1              time in wait: 2 min 26 sec             timeout after: never                   wait id : 29                  blocking: 1 session               current sql: <none>               short stack: --省略              wait history :                * time between current wait and wait #1: 0.000019 sec                1.       event: 'SQL*Net message to client'                   time waited: 0.000007 sec                       wait id : 28              p1: 'driver id' =0x62657100                                                p2: '#bytes' =0x1                * time between wait #1 and #2: 0.049656 sec                2.       event: 'SQL*Net message from client'                   time waited: 9.759067 sec                       wait id : 27              p1: 'driver id' =0x62657100                                                p2: '#bytes' =0x1                * time between wait #2 and #3: 0.000216 sec                3.       event: 'SQL*Net message to client'                   time waited: 0.000002 sec                       wait id : 26              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 -------------------------------------------------------------------------------    =============================================================================== 通过上述分析:大概可以得出sid=143因为请求enq: TX - row lock contention(TX mode=6)被sid=15阻塞

查询视图验证

SQL> select sid,event from v$session where wait_class#<>6;           SID EVENT ---------- ------------------------------          20 SQL*Net message to client         143 enq: TX - row lock contention   SQL> select * from v$lock where type in ( 'TX' , 'TM' );   ADDR     KADDR           SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK -------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ---------- 352F8BEC 352F8C18        143 TX       131088        854          0          6       1862          0 B6B9C7A8 B6B9C7D8         15 TM        75928          0          3          0       1892          0 B6B9C7A8 B6B9C7D8        143 TM        75928          0          3          0       1862          0 343C0E54 343C0E94         15 TX       131088        854          6          0       1892          1 --查询结果sid=15的会话持有TX MODE=6阻塞sid=143的TX MODE=6的请求,和HANG ANALYSIS分析基本一致

参考文档:USING AND READING HANGANALYZE或者[ID 215858.1]