-
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
-
I'm stmontgo and I approve of this message
-
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;
Last edited by newbie5; 04-15-2004 at 05:49 PM.
-
Sure, that would take about 10 minutes... Why don't you write one and share with the rest of us...
Jeff Hunter
-
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.
Last edited by newbie5; 04-15-2004 at 06:43 PM.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|