使用ass109.awk分析systemstate
使用ass109.awk分析systemstate
模拟会话被hang住
--会话1
SQL> select * from t_xifenfei;
ID NAME
---------- ----------------------------------------
1 xifenfei
2 www.xifenfei
SQL> delete from t_xifenfei where id=1;
1 row deleted.
--会话2
SQL> delete from t_xifenfei where id=1;
--hang住
做systemstate
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump systemstate 10
Statement processed.
SQL> oradebug tracefile_name
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_9976.trc
SQL> exit
使用ass109.awk分析dump文件
[oracle@xifenfei ~]$ awk -f ass109.awk /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_9976.trc
Starting Systemstate 1
..................................
Ass.Awk Version 1.0.9 - Processing /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_9976.trc
System State 1
~~~~~~~~~~~~~~~~
1:
2: 0: waiting for 'pmon timer'
3: 0: waiting for 'rdbms ipc message'
4: 0: waiting for 'VKTM Logical Idle Wait'
5: 0: waiting for 'rdbms ipc message'
6: 0: waiting for 'DIAG idle wait'
7: 0: waiting for 'rdbms ipc message'
8: 0: waiting for 'DIAG idle wait'
9: 0: waiting for 'rdbms ipc message'
10: 0: waiting for 'rdbms ipc message'
11: 0: waiting for 'rdbms ipc message'
12: 0: waiting for 'rdbms ipc message'
13: 0: waiting for 'smon timer'
14: 0: waiting for 'rdbms ipc message'
15: 0: waiting for 'rdbms ipc message'
16: 0: waiting for 'rdbms ipc message'
17:
18:
19: 0: waiting for 'Space Manager: slave idle wait'
20: 0: waiting for 'SQL*Net message from client'
21: 0: waiting for 'enq: TX - row lock contention'[Enqueue TX-000A0020-0000024F]
Cmd: Delete
22: 0: waiting for 'rdbms ipc message'
23: 0: waiting for 'rdbms ipc message'
24: 0: waiting for 'rdbms ipc message'
25: 0: waiting for 'rdbms ipc message'
26: 0: waiting for 'Streams AQ: qmn coordinator idle wait'
27:
28:
30: 0: waiting for 'Streams AQ: qmn slave idle wait'
31: 0: waiting for 'rdbms ipc message'
33: 1: waited for 'Streams AQ: waiting for time management or cleanup tasks'
35: 0: waiting for 'rdbms ipc message'
41:
44:
Blockers
~~~~~~~~
Above is a list of all the processes. If they are waiting for a resource
then it will be given in square brackets. Below is a summary of the
waited upon resources, together with the holder of that resource.
Notes:
~~~~~
o A process id of '???' implies that the holder was not found in the
systemstate.
Resource Holder State
Enqueue TX-000A0020-0000024F 20: 0: waiting for 'SQL*Net message from client'
Object Names
~~~~~~~~~~~~
Enqueue TX-000A0020-0000024F
30586 Lines Processed.
--从这里马上就可以知道pid 21 请求Enqueue TX被pid 20阻塞
附件列表
