« DBA手记:System State转储之ROW CACHE对象 | Blog首页 | DBA手记:System State之Row Cache对象定位解析 »

DBA手记:System State之ass109.awk辅助分析

在《Oracle DBA手记 3》即将出版之际，我将《Oracle DBA手记 2》上收录的一些文章发布出来，与大家分享。
前文参考：
http://www.eygle.com/archives/2011/05/dbasystem_state_file.html
http://www.eygle.com/archives/2011/05/dbasystem_state_rowcache.html

对于类似的跟踪文件，可以通过Oracle提供的ass109.awk脚本来分析，分析之后可以获得简明的输出（我的Windows上做了Unix工具增强，可以直接运行awk工具进行分析）：

D:\>awk -f ass109.awk edw_ora_8371.trc

..........................

Ass.Awk Version 1.0.9 - Processing edw_ora_8371.trc

System State 1

~~~~~~~~~~~~~~~~

1:                                     

2:  waiting for 'rdbms ipc message'     wait

3:  waiting for 'pmon timer'            wait

4:  waiting for 'rdbms ipc message'     wait

5:  waiting for 'rdbms ipc message'     wait

6:  waiting for 'rdbms ipc message'     wait

7:  waiting for 'rdbms ipc message'     wait

8:                                     

9:  waiting for 'rdbms ipc message'     wait

10:                                    

11: waiting for 'rdbms ipc message'     wait

12: for 'Streams AQ: waiting for time management or cleanup tasks' wait

13: waiting for 'rdbms ipc message'     wait

14: waiting for 'Streams AQ: qmn coordinator idle wait' wait

15: waiting for 'rdbms ipc message'     wait

16: waiting for 'Wait for shrink lock'  wait

17: waiting for 'smon timer'            wait

18: waiting for 'SQL*Net message from client' wait

19: waiting for 'rdbms ipc message'     wait

21: waiting for 'rdbms ipc message'     wait

23: waiting for 'rdbms ipc message'     wait

25: waiting for 'SQL*Net message from client' wait

27: waiting for 'SQL*Net message from client' wait

29: last wait for 'ksdxexeotherwait'   [Rcache object=4f4e57138,]

     Cmd: Insert

30: waiting for 'Streams AQ: qmn slave idle wait' wait

33: for 'Streams AQ: waiting for messages in the queue' wait

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

    Rcache object=4f4e57138,    16: waiting for 'Wait for shrink lock'

 

Object Names

~~~~~~~~~~~~

Rcache object=4f4e57138,                                  

 

 

77807 Lines Processed.

注意，输出文件中清晰地指出，数据库的16号进程持有了对象4f4e57138的Row Cache锁，正在等待：Wait for shrink lock。而29号进程正是在4f4e57138对象上产生了等待，处于挂起状态，无法执行INSERT操作。

By eygle on 2011-05-27 09:37 | Comments (1) | Case | 2805 |

awkCase