使用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阻塞

 

来自为知笔记(Wiz)

附件列表

 

转载于:https://www.cnblogs.com/cure-t-x-y/p/4243984.html