在以前,很多客户和朋友曾经各种寻找ass109.awk脚本,用意分析systemstate生成的trace文件。
因为最初ass109.awk文件是Oracle内部一个老外大牛个人写脚本,还不算是Oracle公司产品化的东西,以为不能提供支持。
在LTOM431版本中,已经自带了ass109.awk脚本:
sftp> lpwd
E:/CRT-temp/tools/ltom431/ltom/tom_base/tom/src
sftp> pwd
/rootwork
sftp> lls
ass109.awk dumptracebuffer.sh errorstack.sh
flush.sh goiostat.sh govmstat.sh
ha.sh haLevel.sh hfile.sh
initiostat.sh initvmstat.sh ksdwrf.sh
lc2.sh lc4.sh liststatspack.sh
ltom.jar ltomHeader.txt ltommail.sh
monlatch.lst pha.sh prelim.sh
prelimss.sh prha.sh processstate.sh
prss.sh pss.sh rhaLevel.sh
rss.sh runfile.sh session_statspack.sh
spcpkg.lis spctab.lis spcusr.lis
sprepins.sql ss.sh statspack.sh
testee.sh tom92spreport.sql tomsprepins.sql
tomspreport.sql topaix.sh tracebufferoff.sh
tracebufferon.sh
sftp>
ass.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.
从上面的分析可以看出:
Rcache object=4f4e57138, 16: waiting for 'Wait for shrink lock'
表示当前数据库的blocker是数据库的16号进程(systemstate中的pid 16),他持有了对象4f4e57138的Row Cache锁,而pid 16正在等待:Wait for shrink lock。
29: last wait for 'ksdxexeotherwait' [Rcache object=4f4e57138,]
Cmd: Insert
而这里清晰的指出,29号进程最后的等待是在4f4e57138对象,目前处于挂起状态,无法执行INSERT操作,阻塞他的正式pid 16
不过LTOM目前已经不再开发了,他的很多功能集成到ASH里面了:
IMPORTANT: LTOM is a legacy tool and is no longer being developed.
Much of the functionality is now available within the Active Session History (ASH).
For details see: Document 243132.1 Analysis of Active Session History (Ash) Online and Offline