parsing a 10046 event trace file

Is there any script (PERL?) that goes through the entire trace file of a 10046 event trace and gives me the object name and type the query waited?

I have 200000 lines in the trace file and it is cumbersome to manually run the query on DBA_SEGMENTS to find each object name.


WAIT #79: nam='db file sequential read' ela= 6410 p1=69 p2=55175 p3=1
WAIT #79: nam='db file sequential read' ela= 10123 p1=69 p2=53785 p3=1
WAIT #79: nam='db file sequential read' ela= 6809 p1=70 p2=45315 p3=1
WAIT #79: nam='db file sequential read' ela= 8008 p1=70 p2=44967 p3=1
WAIT #79: nam='db file sequential read' ela= 8098 p1=69 p2=65413 p3=1
WAIT #79: nam='db file sequential read' ela= 10810 p1=77 p2=183858 p3=1
WAIT #79: nam='db file sequential read' ela= 7107 p1=70 p2=58215 p3=1
WAIT #79: nam='db file sequential read' ela= 5288 p1=78 p2=81639 p3=1
WAIT #79: nam='db file sequential read' ela= 8365 p1=69 p2=79209 p3=1
WAIT #79: nam='db file sequential read' ela= 8504 p1=69 p2=78834 p3=1
WAIT #79: nam='db file sequential read' ela= 7483 p1=68 p2=92668 p3=1
WAIT #79: nam='db file sequential read' ela= 9715 p1=79 p2=92561 p3=1
WAIT #79: nam='db file sequential read' ela= 8007 p1=69 p2=99014 p3=1
WAIT #79: nam='db file sequential read' ela= 9276 p1=69 p2=98696 p3=1
WAIT #79: nam='db file sequential read' ela= 116871 p1=41 p2=214130 p3=1
WAIT #79: nam='db file sequential read' ela= 12584 p1=41 p2=220265 p3=1
WAIT #79: nam='db file sequential read' ela= 23042 p1=72 p2=250472 p3=1
WAIT #79: nam='db file sequential read' ela= 13008 p1=70 p2=100682 p3=1
WAIT #79: nam='db file sequential read' ela= 6752 p1=70 p2=98923 p3=1
WAIT #79: nam='db file sequential read' ela= 11643 p1=69 p2=126332 p3=1
WAIT #79: nam='db file sequential read' ela= 15265 p1=77 p2=151339 p3=1
WAIT #79: nam='db file sequential read' ela= 9945 p1=75 p2=11971 p3=1
WAIT #79: nam='db file sequential read' ela= 7662 p1=69 p2=130204 p3=1
WAIT #79: nam='db file sequential read' ela= 5251 p1=32 p2=192680 p3=1

hmmmmm.....tkprof?

No... Tkprof output still have p1, p2, p3..

I am sure someone out there has a little perl script that can parse the entire script for p1, and p2 and run this query and format the output for waiting object name and type:

SELECT owner , segment_name , segment_type
FROM dba_extents
WHERE file_id = &AFN
AND &BLOCKNO BETWEEN block_id AND block_id + blocks -1;

Sure, that would take about 10 minutes... Why don't you write one and share with the rest of us...

Well, no perl script here (and, anyway, 9i tkprof gives all the wait details for us), but when I was restricted to 8i only, I used to do the following :

Code:

---

grep WAIT *.trc  | sort +3 -t: >  loadtrace.dat
 grep PARSE *.trc | sort +3 -t: >> loadtrace.dat
 grep EXEC *.trc  | sort +3 -t: >> loadtrace.dat
 grep FETCH *.trc | sort +3 -t: >> loadtrace.dat
 grep UNMAP *.trc | sort +3 -t: >> loadtrace.dat
 grep STAT *.trc  | sort +3 -t: >> loadtrace.dat
 
drop table waittrace;
create table waittrace (fname varchar2(30),
op varchar2(10),
opseq varchar2(10),
waithd varchar2(10),
wait varchar2(30),
elah varchar2(5),
ela number,
p1 varchar2(30),
p2 varchar2(30),
p3 varchar2(30));
drop table stattrace;
create table stattrace(
fname varchar2(30),
op varchar2(10),
opseq varchar2(10),
id number,
cnt number,
pid number,
pos number,
obj number,
operation varchar2(60));

drop table cputrace;
create table cputrace(
fname varchar2(30),
op varchar2(10),
opseq varchar2(10),
c number,
e number,
p number,
cr number,
cu number,
mis number,
r number,
dep number,
og number,
tim number(20));

==> put in loadtrace.ctl the following:

LOAD DATA INFILE 'loadtrace.dat' APPEND
INTO TABLE waittrace
  WHEN op = 'WAIT'
    FIELDS TERMINATED BY " "
  trailing nullcols
  (fname  char  terminated by ":",
  op    char  terminated by " ",
  opseq  char  terminated by ":",
  waithd char  terminated by "=",
  wait  char  enclosed by "'",
  elah  char ,
  ela    char,
  p1    char,
  p2    char,
  p3    char
 )
INTO TABLE cputrace
  WHEN op = 'PARSE'
  trailing nullcols
  (fname POSITION(1) char terminated by ":",
  op    char    terminated by " " ,
  opseq char    terminated by ":" ,
  c            enclosed by "c="  AND ",",
  e            enclosed by "e="  AND ",",
  p            enclosed by "p="  AND ",",
  cr            enclosed by "cr="  AND ",",
  cu            enclosed by "cu="  AND ",",
  mis          enclosed by "mis=" AND ",",
  r            enclosed by "r="  AND ",",
  dep          enclosed by "dep=" AND ",",
  og            enclosed by "og="  AND ",tim=",
  tim terminated by ","
  )
INTO TABLE cputrace
  WHEN op = 'EXEC'
  trailing nullcols
  (fname POSITION(1) char        terminated by ":",
  op    char    terminated by " " ,
  opseq        char    terminated by ":" ,
  c            enclosed by "c="  AND ",",
  e            enclosed by "e="  AND ",",
  p            enclosed by "p="  AND ",",
  cr            enclosed by "cr="  AND ",",
  cu            enclosed by "cu="  AND ",",
  mis          enclosed by "mis=" AND ",",
  r            enclosed by "r="  AND ",",
  dep          enclosed by "dep=" AND ",",
  og            enclosed by "og="  AND ",tim=",
  tim terminated by ","
  )
INTO TABLE cputrace
  WHEN op = 'FETCH'
  trailing nullcols
  (fname POSITION(1) char        terminated by ":",
  op    char    terminated by " " ,
  opseq        char    terminated by ":" ,
  c            enclosed by "c="  AND ",",
  e            enclosed by "e="  AND ",",
  p            enclosed by "p="  AND ",",
  cr            enclosed by "cr="  AND ",",
  cu            enclosed by "cu="  AND ",",
  mis          enclosed by "mis=" AND ",",
  r            enclosed by "r="  AND ",",
  dep          enclosed by "dep=" AND ",",
  og            enclosed by "og="  AND ",tim=",
  tim terminated by ","
  )
INTO TABLE cputrace
  WHEN op = 'UNMAP'
  trailing nullcols
  (fname POSITION(1) char        terminated by ":",
  op    char    terminated by " " ,
  opseq        char    terminated by ":" ,
  c            enclosed by "c="  AND ",",
  e            enclosed by "e="  AND ",",
  p            enclosed by "p="  AND ",",
  cr            enclosed by "cr="  AND ",",
  cu            enclosed by "cu="  AND ",",
  mis          enclosed by "mis=" AND ",",
  r            enclosed by "r="  AND ",",
  dep          enclosed by "dep=" AND ",",
  og            enclosed by "og="  AND ",tim=",
  tim terminated by ","
  )
INTO TABLE stattrace
  WHEN op = 'STAT'
  trailing nullcols
  (fname POSITION(1) char        terminated by ":",
  op            char    terminated by " " ,
  opseq        char    terminated by " " ,
  id            enclosed by "id="  AND " ",
  cnt          enclosed by "cnt=" AND " ",
  pid          enclosed by "pid=" AND " ",
  pos          enclosed by "pos=" AND " ",
  obj          enclosed by "obj=" AND " ",
  operation    enclosed by "op='" AND "'"
  )

---

==> and finally invoke SQL*Loader

sqlldr userid/passwd control=loadtrace.ctl direct=true

from here is easy, you can select the tables in any way you like.

Regards,

Chiappa

Thanks JChiappa. I will try something similar.

Thanks marist89. That is the way to go. But you know about not reinventing the wheel, if it has already been invented.

Quote:

---

Originally posted by newbie5
Thanks JChiappa. I will try this.

Thanks marist89. That is the way to go. But you know about not reinventing the wheel, if it has already been invented.

---

what if you invent a better wheel?

in 8i we can use trace analyzer