工具: ass109.awk 分析 Oracle 的跟踪文件

原文链接：http://www.eygle.com/archives/2009/11/awk_ass109.html

以前分析Oracle的跟踪文件，主要靠手工阅读，最近发现ass109.awk文件是个不错的工具，在网上找到了一个，附在这里： ass109.rar

通过ass109.awk文件，可以很容易将trace文件里的内容理出脉络来，清晰的发现问题所在，土了这么多年。

解析了以前一个案例的80多M的跟踪文件，很快就可以找到其中的阻塞，Block信息：
D:DownloadsMo pt1_ora_2341>awk -f ass109.awk ora_2341.trc

Starting Systemstate 1
..............................................................................
...............................................................................
...............................................................................
............................................
Ass.Awk Version 1.0.9 - Processing ora_2341.trc

System State 1
~~~~~~~~~~~~~~~~
1:
2:  waiting for 'DFS lock handle'       wait
3:  waiting for 'DIAG idle wait'        wait
4:  waiting for 'rdbms ipc message'     wait
5:  waiting for 'rdbms ipc message'     wait
6:  waiting for 'ges remote message'    wait
7:  waiting for 'gcs remote message'    wait
8:  waiting for 'gcs remote message'    wait
9:  waiting for 'gcs remote message'    wait
10: waiting for 'gcs remote message'    wait
11: waiting for 'gcs remote message'    wait
12: waiting for 'gcs remote message'    wait
13: waiting for 'gcs remote message'    wait
14: waiting for 'gcs remote message'    wait
15: waiting for 'gcs remote message'    wait
16: waiting for 'gcs remote message'    wait
17: waiting for 'gcs remote message'    wait
18: waiting for 'gcs remote message'    wait
19: waiting for 'gcs remote message'    wait
20: waiting for 'gcs remote message'    wait
21: waiting for 'gcs remote message'    wait
22: waiting for 'gcs remote message'    wait
23: waiting for 'rdbms ipc message'     wait
24: waiting for 'rdbms ipc message'     wait
25: waiting for 'rdbms ipc message'     wait
26: waiting for 'rdbms ipc message'     wait
27: waiting for 'rdbms ipc message'     wait
28: waiting for 'rdbms ipc message'     wait
29: waiting for 'rdbms ipc message'     wait
30: waiting for 'rdbms ipc message'     wait
31: waiting for 'rdbms ipc message'     wait
32: waiting for 'rdbms ipc message'     wait
33: waiting for 'rdbms ipc message'     wait
34: waiting for 'rdbms ipc message'     wait
35: waiting for 'rdbms ipc message'     wait
36: waiting for 'control file sequential read' wait
37: waiting for 'enq: PR - contention' [Enqueue PR-00000000-00000000] wait
38: waiting for 'DFS lock handle'       wait
39: waiting for 'rdbms ipc message'     wait
40: waiting for 'enq: PR - contention' [Enqueue PR-00000000-00000000] wait
41: last wait for 'os thread startup'
42: waiting for 'buffer busy waits' (6a,2,d) wait
43:
44:
45: waiting for 'rdbms ipc message'     wait
46: waiting for 'SQL*Net message from client' wait
47: waiting for 'buffer busy waits' (41,8fb85,8) wait
     Cmd: Insert
48: waiting for 'latch free'           [Latch c0000000c2df3b70] wait
49: waiting for 'log file switch (checkpoint incomplete)' wait
50: waiting for 'SQL*Net message from client' wait
51: for 'Streams AQ: waiting for messages in the queue' wait
52: waiting for 'SQL*Net message from client' wait
53: waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Insert
54:
55: waiting for 'SQL*Net message from client' wait
56: waiting for 'SQL*Net message from client' wait
57: waiting for 'SQL*Net message from client' wait
58: waiting for 'SQL*Net message from client' wait
59: waiting for 'SQL*Net message from client' wait
60: waiting for 'SQL*Net message from client' wait
61: waiting for 'log file switch (checkpoint incomplete)' wait
62: waiting for 'SQL*Net message from client' wait
63: waiting for 'SQL*Net message from client' wait
64:
65: waiting for 'SQL*Net message from client' wait
     Cmd: Select
66: waiting for 'log file switch (checkpoint incomplete)' wait
67: waiting for 'SQL*Net message from client' wait
68: waiting for 'SQL*Net message from client' wait
69: waiting for 'SQL*Net message from client' wait
70: waiting for 'gc buffer busy'        wait
     Cmd: Insert
71: waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
72: waiting for 'PX Deq: Execution Msg' wait
     Cmd: Select
73: waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
74: waiting for 'SQL*Net message from client' wait
75: waiting for 'SQL*Net message from client' wait
76: waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Select
77: waiting for 'SQL*Net message from client' wait
78: waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
79: waiting for 'gc buffer busy'        wait
     Cmd: Insert
80: waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Select
81: waiting for 'buffer busy waits' (69,2,d) wait
     Cmd: Select
82: waiting for 'buffer busy waits' (69,2,d) wait
     Cmd: Insert
83: waiting for 'DFS lock handle'       wait
     Cmd: Insert
84: waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Select
85: waiting for 'SQL*Net message from client' wait
86: waiting for 'PX Deq: Execution Msg' wait
     Cmd: Select
87:
88: waiting for 'SQL*Net message from client' wait
     Cmd: Select
89:
90:
91: waiting for 'gc buffer busy'        wait
     Cmd: Insert
92:
93:
94:
95: waiting for 'SQL*Net message from client' wait
96: waiting for 'SQL*Net message from client' wait
97: waiting for 'gc buffer busy'        wait
     Cmd: Insert
98: waiting for 'SQL*Net message from client' wait
99: waiting for 'SQL*Net message from client' wait
100:waiting for 'SQL*Net message from client' wait
101:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
     Cmd: Drop Table
102:waiting for 'gc buffer busy'        wait
     Cmd: Insert
103:waiting for 'SQL*Net message from client' wait
104:
105:waiting for 'SQL*Net message from client' wait
106:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
     Cmd: Select
107:
108:waiting for 'SQL*Net message from client' wait
109:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
110:
111:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
112:last wait for 'undo segment extension'
     Cmd: Insert
113:waiting for 'buffer busy waits' (69,2,d) wait
     Cmd: Insert
114:
115:
116:
117:
118:waiting for 'latch free'           [Latch c0000000c2df3b70] wait
119:waiting for 'SQL*Net message from client' wait
     Cmd: Select
120:waiting for 'SQL*Net message from client' wait
121:
122:
124:
125:
126:
127:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
128:
129:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Insert
130:
131:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Insert
132:
133:waiting for 'SQL*Net message from client' wait
134:
135:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Insert
136:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Select
137:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
138:waiting for 'SQL*Net message from client' wait
139:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Insert
140:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Insert
141:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
142:waiting for 'SQL*Net message from client' wait
143:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Insert
144:
145:
146:
147:
148:
149:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Insert
150:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Insert
151:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Insert
152:
153:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Insert
154:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Insert
155:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait
     Cmd: Insert
156:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Delete
157:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Insert
158:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Insert
159:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Delete
160:waiting for 'SQL*Net message from client' wait
161:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Delete
162:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Delete
163:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Delete
164:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Insert
165:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Delete
166:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Delete
167:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Delete
168:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Delete
169:waiting for 'buffer busy waits' (6,6bd86,8) wait
     Cmd: Insert
170:waiting for 'buffer busy waits' (6,6bd85,8) wait
     Cmd: Insert
171:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait
     Cmd: Select
172:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Delete
173:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Insert
174:waiting for 'buffer busy waits' (6,6bd85,8) wait
     Cmd: Insert
175:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Insert
176:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait
     Cmd: Select
177:waiting for 'enq: FB - contention' [Enqueue FB-00000006-0186BDC8] wait
     Cmd: Insert
178:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait
     Cmd: Select
179:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Delete
180:waiting for 'buffer busy waits' (6,6bd85,8) wait
     Cmd: Insert
181:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Delete
182:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Delete
183:waiting for 'buffer busy waits' (6,6bd86,8) wait
     Cmd: Insert
184:waiting for 'buffer busy waits' (6,6bd85,8) wait
     Cmd: Insert
185:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Select
186:waiting for 'buffer busy waits' (6,6bd85,8) wait
     Cmd: Insert
187:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Insert
188:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Insert
189:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Delete
190:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Delete
191:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Delete
192:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait
     Cmd: Insert
193:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait
     Cmd: Insert
194:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Delete
195:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Delete
196:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Delete
197:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait
     Cmd: Insert
198:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Delete
199:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Delete
200:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Delete
201:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait
     Cmd: Insert
202:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait
     Cmd: Insert
203:waiting for 'library cache lock'   [LOCK: handle=c000000f388db3d0] wait
     Cmd: Truncate Table
204:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Truncate Table
205:waiting for 'library cache lock'   [LOCK: handle=c000000f388db3d0] wait
     Cmd: Truncate Table
206:waiting for 'library cache lock'   [LOCK: handle=c000000f388db3d0] wait
     Cmd: Truncate Table
207:waiting for 'library cache lock'   [LOCK: handle=c000000f388db3d0] wait
     Cmd: Truncate Table
208:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait
     Cmd: Insert
209:waiting for 'SQL*Net message from client' wait
210:waiting for 'SQL*Net message from client' wait
211:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait
     Cmd: Insert
212:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait
     Cmd: Insert
213:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait
     Cmd: Insert
214:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Update
215:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait
     Cmd: Insert
216:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Update
217:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Update
218:last wait for 'ksdxexeotherwait'   [Rcache object=c000000f9fdf8160,]
219:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait
     Cmd: Update
220:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Select
221:waiting for 'cursor: pin S wait on X' wait
     Cmd: Select
222:waiting for 'cursor: pin S wait on X' wait
     Cmd: Select
223:waiting for 'cursor: pin S wait on X' wait
     Cmd: Select
224:waiting for 'cursor: pin S wait on X' wait
     Cmd: Select
225:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Select
226:waiting for 'buffer busy waits' (41,8fb85,8) wait
     Cmd: Insert
227:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Select
228:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Select
229:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Select
230:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Update
231:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Update
232:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Update
233:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Update
234:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Update
235:waiting for 'buffer busy waits' (31,85f86,8) wait
     Cmd: Insert
236:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Insert
237:waiting for 'buffer busy waits' (31,85f86,8) wait
     Cmd: Insert
238:waiting for 'buffer busy waits' (31,85f85,8) wait
     Cmd: Insert
239:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
240:
241:waiting for 'buffer busy waits' (31,85f86,8) wait
     Cmd: Insert
242:
243:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Insert
244:waiting for 'buffer busy waits' (31,85f85,8) wait
     Cmd: Insert
245:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
246:waiting for 'log file switch (checkpoint incomplete)' wait
     Cmd: Insert
247:waiting for 'buffer busy waits' (41,8fb85,8) wait
     Cmd: Insert
248:waiting for 'buffer busy waits' (41,8fb85,8) wait
     Cmd: Insert
249:waiting for 'buffer busy waits' (41,8fb85,8) wait
     Cmd: Insert
250:waiting for 'buffer busy waits' (41,8fb85,8) wait
     Cmd: Insert
251:waiting for 'buffer busy waits' (41,8fb85,8) wait
     Cmd: Insert
252:waiting for 'buffer busy waits' (41,8fb85,8) wait
     Cmd: Insert
253:waiting for 'buffer busy waits' (41,8fb85,8) wait
     Cmd: Insert
254:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
255:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
256:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
257:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
258:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
259:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
260:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
261:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
262:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
263:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
264:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
265:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
266:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
267:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
268:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
269:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
270:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
271:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
272:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
273:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
274:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
275:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
276:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
277:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
278:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
279:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
280:waiting for 'row cache lock'       [Rcache object=c000000f9fdf8160,] wait
281:last wait for 'log file switch (checkpoint incomplete)'[Rcache object=c000000f9fdf8160,] 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
Enqueue PR-00000000-00000000    41: last wait for 'os thread startup'
      Latch c0000000c2df3b70    ??? Blocker
Rcache object=c000000f9fdf8160,    61: waiting for 'log file switch (checkpoint incomplete)'
Enqueue US-0000004C-00000000   185: waiting for 'log file switch (checkpoint incomplete)'
Enqueue FB-00000006-0186BDC8   187: waiting for 'log file switch (checkpoint incomplete)'
LOCK: handle=c000000f388db3d0   204: waiting for 'log file switch (checkpoint incomplete)'

Object Names
~~~~~~~~~~~~
Enqueue PR-00000000-00000000
Latch c0000000c2df3b70        holding    (efd=5) c0000000c2df3b70 slave cl
Rcache object=c000000f9fdf8160,
Enqueue US-0000004C-00000000
Enqueue FB-00000006-0186BDC8
LOCK: handle=c000000f388db3d0   TABL:REPORT.STATQ_AGENT_SUBS_NEW


1382711 Lines Processed.