使用ass109.awk分析systemstate

本篇介绍工具ass109.awk使用,大大节约分析systemstate dump文件时间.当然如果要获得详细信息,还是需要人工去读相关进程的dump文件.
模拟会话被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阻塞

下载:ass109.awk