联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
前两篇分别大概的介绍了Systemstates分析会话阻塞—锁表和使用ass109.awk分析systemstate,这篇也大概的学习下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]