sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

学习笔记:Oracle数据表锁 通过Hanganalyze分析session会话阻塞情况

时间:2016-05-29 22:27   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净

Oracle数据库表锁时,通过Hanganalyze分析会话阻塞详细情况

 

1.模拟Oracle数据库阻塞session会话

--会话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住

2.做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: 
             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阻塞

3.查询视图验证Oracle数据表锁情况

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分析基本一致

 


--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle数据表锁 通过Hanganalyze分析session会话阻塞情况

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/244.html

Oracle研究中心

关键词:

Oracle数据表锁

通过Hanganalyze分析session会话之间表锁阻塞情况