Parsing a 10046 trace with python
This needs work (I am just learning python), but try the following…
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 | #!/usr/bin/python #-------------------------------------------------------------------------------------------------- #Author: Steve Howard #Date: March 23, 2009 #Organization: AppCrawler #Purpose: Simple script to print statements in executed order from 10046 trace file. It also # prints the milliseconds between the current and previous execution. This # provides a high level overview of gaps between statements. Large gaps # warrant further investigation. #-------------------------------------------------------------------------------------------------- import fileinput import string import sys import time b=0 sqls = [None] * 400 #need to make array/list size dynamic deps = [None] * 400 #need to make array/list size dynamic tok="" c=0 tim=0 start=0 prevtim=0 i=0 printarray = [] printarray1 = [] printarray2 = [] printarray3 = [] printarray4 = [] printarray5 = [] for line in fileinput.input([sys.argv[1:][0]]): i=i+1 if (b==1 and line.startswith("END OF STMT")): sqls[c]=tok deps[c]=dep tok="" b=0 elif line.startswith("PARSING IN CURSOR"): c=int(string.replace(string.split(line," ")[3],"#","")) dep=int(string.replace(string.split(line," ")[5],"dep=","")) b=1 elif line.startswith("EXEC #"): c=int(string.replace(string.split(line,":")[0],"EXEC #","")) if deps[c] == 0: if tim==0: prevtim=tim tim=int(string.replace(string.split(line,",")[9],"tim=","")) start=tim else: prevtim=tim tim=int(string.replace(string.split(line,",")[9],"tim=","")) printarray.append("#-------------------------------------------------------------------"); printarray.append(str((tim - prevtim) / 1000) + " milliseconds since the previous top level execution statement.") printarray.append(str((tim - start) / 1000) + " milliseconds since the start of the trace.") printarray.append("LINE# = " + str(i)) printarray.append(sqls[c]); for sql in range(len(printarray1)): printarray.append(" " + printarray1[sql]); del printarray1[0:] elif deps[c] == 1: printarray1.append(sqls[c]); for sql in range(len(printarray2)): printarray1.append(" " + printarray2[sql]); del printarray2[0:] elif deps[c] == 2: printarray2.append(sqls[c]); for sql in range(len(printarray3)): printarray1.append(" " + printarray3[sql]); del printarray3[0:] elif deps[c] == 3: printarray3.append(sqls[c]); elif (b==1): tok=tok + line for sql in range(len(printarray)): print printarray[sql] |
What is nice is this will actually print in sequentially executed order all statements found in the trace. it also indents by dependency level. In other words, if a function is called by a select statement, the SQL in the function will show up below the select, but be indented so you can see it was only run because the select above it called it.
This is really good for understanding program flow without reading source code.