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;
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 "'"
)
Reply With Quote
Bookmarks