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

mac2022-06-30 131

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

以前分析Oracle的跟踪文件，主要靠手工阅读，最近发现ass109.awk文件是个不错的工具，在网上找到了一个，附在这里： ass109.rar通过ass109.awk文件，可以很容易将trace文件里的内容理出脉络来，清晰的发现问题所在，土了这么多年。解析了以前一个案例的80多M的跟踪文件，很快就可以找到其中的阻塞，Block信息：D:\Downloads\Mo\rpt1_ora_2341>awk -f ass109.awk ora_2341.trcStarting Systemstate 1........................................................................................................................................................................................................................................................................................Ass.Awk Version 1.0.9 - Processing ora_2341.trcSystem State 1~~~~~~~~~~~~~~~~1:2: waiting for 'DFS lock handle' wait3: waiting for 'DIAG idle wait' wait4: waiting for 'rdbms ipc message' wait5: waiting for 'rdbms ipc message' wait6: waiting for 'ges remote message' wait7: waiting for 'gcs remote message' wait8: waiting for 'gcs remote message' wait9: waiting for 'gcs remote message' wait10: waiting for 'gcs remote message' wait11: waiting for 'gcs remote message' wait12: waiting for 'gcs remote message' wait13: waiting for 'gcs remote message' wait14: waiting for 'gcs remote message' wait15: waiting for 'gcs remote message' wait16: waiting for 'gcs remote message' wait17: waiting for 'gcs remote message' wait18: waiting for 'gcs remote message' wait19: waiting for 'gcs remote message' wait20: waiting for 'gcs remote message' wait21: waiting for 'gcs remote message' wait22: waiting for 'gcs remote message' wait23: waiting for 'rdbms ipc message' wait24: waiting for 'rdbms ipc message' wait25: waiting for 'rdbms ipc message' wait26: waiting for 'rdbms ipc message' wait27: waiting for 'rdbms ipc message' wait28: waiting for 'rdbms ipc message' wait29: waiting for 'rdbms ipc message' wait30: waiting for 'rdbms ipc message' wait31: waiting for 'rdbms ipc message' wait32: waiting for 'rdbms ipc message' wait33: waiting for 'rdbms ipc message' wait34: waiting for 'rdbms ipc message' wait35: waiting for 'rdbms ipc message' wait36: waiting for 'control file sequential read' wait37: waiting for 'enq: PR - contention' [Enqueue PR-00000000-00000000] wait38: waiting for 'DFS lock handle' wait39: waiting for 'rdbms ipc message' wait40: waiting for 'enq: PR - contention' [Enqueue PR-00000000-00000000] wait41: last wait for 'os thread startup'42: waiting for 'buffer busy waits' (6a,2,d) wait43:44:45: waiting for 'rdbms ipc message' wait46: waiting for 'SQL*Net message from client' wait47: waiting for 'buffer busy waits' (41,8fb85,8) wait Cmd: Insert48: waiting for 'latch free' [Latch c0000000c2df3b70] wait49: waiting for 'log file switch (checkpoint incomplete)' wait50: waiting for 'SQL*Net message from client' wait51: for 'Streams AQ: waiting for messages in the queue' wait52: waiting for 'SQL*Net message from client' wait53: waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Insert54:55: waiting for 'SQL*Net message from client' wait56: waiting for 'SQL*Net message from client' wait57: waiting for 'SQL*Net message from client' wait58: waiting for 'SQL*Net message from client' wait59: waiting for 'SQL*Net message from client' wait60: waiting for 'SQL*Net message from client' wait61: waiting for 'log file switch (checkpoint incomplete)' wait62: waiting for 'SQL*Net message from client' wait63: waiting for 'SQL*Net message from client' wait64:65: waiting for 'SQL*Net message from client' wait Cmd: Select66: waiting for 'log file switch (checkpoint incomplete)' wait67: waiting for 'SQL*Net message from client' wait68: waiting for 'SQL*Net message from client' wait69: waiting for 'SQL*Net message from client' wait70: waiting for 'gc buffer busy' wait Cmd: Insert71: waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait72: waiting for 'PX Deq: Execution Msg' wait Cmd: Select73: waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait74: waiting for 'SQL*Net message from client' wait75: waiting for 'SQL*Net message from client' wait76: waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Select77: waiting for 'SQL*Net message from client' wait78: waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait79: waiting for 'gc buffer busy' wait Cmd: Insert80: waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Select81: waiting for 'buffer busy waits' (69,2,d) wait Cmd: Select82: waiting for 'buffer busy waits' (69,2,d) wait Cmd: Insert83: waiting for 'DFS lock handle' wait Cmd: Insert84: waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Select85: waiting for 'SQL*Net message from client' wait86: waiting for 'PX Deq: Execution Msg' wait Cmd: Select87:88: waiting for 'SQL*Net message from client' wait Cmd: Select89:90:91: waiting for 'gc buffer busy' wait Cmd: Insert92:93:94:95: waiting for 'SQL*Net message from client' wait96: waiting for 'SQL*Net message from client' wait97: waiting for 'gc buffer busy' wait Cmd: Insert98: waiting for 'SQL*Net message from client' wait99: waiting for 'SQL*Net message from client' wait100:waiting for 'SQL*Net message from client' wait101:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait Cmd: Drop Table102:waiting for 'gc buffer busy' wait Cmd: Insert103:waiting for 'SQL*Net message from client' wait104:105:waiting for 'SQL*Net message from client' wait106:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait Cmd: Select107:108:waiting for 'SQL*Net message from client' wait109:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait110:111:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait112:last wait for 'undo segment extension' Cmd: Insert113:waiting for 'buffer busy waits' (69,2,d) wait Cmd: Insert114:115:116:117:118:waiting for 'latch free' [Latch c0000000c2df3b70] wait119:waiting for 'SQL*Net message from client' wait Cmd: Select120:waiting for 'SQL*Net message from client' wait121:122:124:125:126:127:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait128:129:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Insert130:131:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Insert132:133:waiting for 'SQL*Net message from client' wait134:135:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Insert136:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Select137:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait138:waiting for 'SQL*Net message from client' wait139:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Insert140:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Insert141:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait142:waiting for 'SQL*Net message from client' wait143:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Insert144:145:146:147:148:149:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Insert150:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Insert151:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Insert152:153:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Insert154:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Insert155:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait Cmd: Insert156:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Delete157:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Insert158:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Insert159:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Delete160:waiting for 'SQL*Net message from client' wait161:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Delete162:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Delete163:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Delete164:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Insert165:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Delete166:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Delete167:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Delete168:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Delete169:waiting for 'buffer busy waits' (6,6bd86,8) wait Cmd: Insert170:waiting for 'buffer busy waits' (6,6bd85,8) wait Cmd: Insert171:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait Cmd: Select172:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Delete173:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Insert174:waiting for 'buffer busy waits' (6,6bd85,8) wait Cmd: Insert175:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Insert176:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait Cmd: Select177:waiting for 'enq: FB - contention' [Enqueue FB-00000006-0186BDC8] wait Cmd: Insert178:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait Cmd: Select179:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Delete180:waiting for 'buffer busy waits' (6,6bd85,8) wait Cmd: Insert181:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Delete182:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Delete183:waiting for 'buffer busy waits' (6,6bd86,8) wait Cmd: Insert184:waiting for 'buffer busy waits' (6,6bd85,8) wait Cmd: Insert185:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Select186:waiting for 'buffer busy waits' (6,6bd85,8) wait Cmd: Insert187:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Insert188:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Insert189:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Delete190:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Delete191:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Delete192:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait Cmd: Insert193:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait Cmd: Insert194:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Delete195:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Delete196:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Delete197:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait Cmd: Insert198:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Delete199:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Delete200:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Delete201:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait Cmd: Insert202:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait Cmd: Insert203:waiting for 'library cache lock' [LOCK: handle=c000000f388db3d0] wait Cmd: Truncate Table204:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Truncate Table205:waiting for 'library cache lock' [LOCK: handle=c000000f388db3d0] wait Cmd: Truncate Table206:waiting for 'library cache lock' [LOCK: handle=c000000f388db3d0] wait Cmd: Truncate Table207:waiting for 'library cache lock' [LOCK: handle=c000000f388db3d0] wait Cmd: Truncate Table208:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait Cmd: Insert209:waiting for 'SQL*Net message from client' wait210:waiting for 'SQL*Net message from client' wait211:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait Cmd: Insert212:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait Cmd: Insert213:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait Cmd: Insert214:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Update215:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait Cmd: Insert216:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Update217:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Update218:last wait for 'ksdxexeotherwait' [Rcache object=c000000f9fdf8160,]219:waiting for 'enq: US - contention' [Enqueue US-0000004C-00000000] wait Cmd: Update220:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Select221:waiting for 'cursor: pin S wait on X' wait Cmd: Select222:waiting for 'cursor: pin S wait on X' wait Cmd: Select223:waiting for 'cursor: pin S wait on X' wait Cmd: Select224:waiting for 'cursor: pin S wait on X' wait Cmd: Select225:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Select226:waiting for 'buffer busy waits' (41,8fb85,8) wait Cmd: Insert227:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Select228:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Select229:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Select230:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Update231:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Update232:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Update233:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Update234:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Update235:waiting for 'buffer busy waits' (31,85f86,8) wait Cmd: Insert236:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Insert237:waiting for 'buffer busy waits' (31,85f86,8) wait Cmd: Insert238:waiting for 'buffer busy waits' (31,85f85,8) wait Cmd: Insert239:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait240:241:waiting for 'buffer busy waits' (31,85f86,8) wait Cmd: Insert242:243:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Insert244:waiting for 'buffer busy waits' (31,85f85,8) wait Cmd: Insert245:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait246:waiting for 'log file switch (checkpoint incomplete)' wait Cmd: Insert247:waiting for 'buffer busy waits' (41,8fb85,8) wait Cmd: Insert248:waiting for 'buffer busy waits' (41,8fb85,8) wait Cmd: Insert249:waiting for 'buffer busy waits' (41,8fb85,8) wait Cmd: Insert250:waiting for 'buffer busy waits' (41,8fb85,8) wait Cmd: Insert251:waiting for 'buffer busy waits' (41,8fb85,8) wait Cmd: Insert252:waiting for 'buffer busy waits' (41,8fb85,8) wait Cmd: Insert253:waiting for 'buffer busy waits' (41,8fb85,8) wait Cmd: Insert254:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait255:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait256:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait257:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait258:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait259:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait260:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait261:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait262:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait263:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait264:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait265:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait266:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait267:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait268:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait269:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait270:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait271:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait272:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait273:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait274:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait275:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait276:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait277:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait278:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait279:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait280:waiting for 'row cache lock' [Rcache object=c000000f9fdf8160,] wait281:last wait for 'log file switch (checkpoint incomplete)'[Rcache object=c000000f9fdf8160,] waitBlockers~~~~~~~~ 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 StateEnqueue PR-00000000-00000000 41: last wait for 'os thread startup' Latch c0000000c2df3b70 ??? BlockerRcache 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-00000000Latch c0000000c2df3b70 holding (efd=5) c0000000c2df3b70 slave clRcache object=c000000f9fdf8160,Enqueue US-0000004C-00000000Enqueue FB-00000006-0186BDC8LOCK: handle=c000000f388db3d0 TABL:REPORT.STATQ_AGENT_SUBS_NEW1382711 Lines Processed.

转载于:https://www.cnblogs.com/iyoume2008/p/6100929.html