I did write an awk script to summarize the wait events in a 10046 trace
file. It lists the SQL, and sums the count and time of the waits for the
SQL.
Henry
# Script for analyzing Oracle Trace files with WAIT statistics
# Usage: wait_scan.awk <filename>
# Written: Henry Poras
# 5/16/00
# Modified: 12/3/01 Initially assumes all wait states for a cursor are
between
# parse statements.
#
#
nawk ' # need nawk,
not awk
BEGIN {N=""
PARSE_FLAG=0 # PARSE_FLAG
= 0 (normal state)
printf("\n\n%-35s %-12s %-18s\n\n", # PARSE_FLAG
= 1 (previous line PARSING)
"WAIT EVENT", "# OF TIMES", "ELAPSED TIME (sec)") # print
column headers
}
{if (PARSE_FLAG==1) # if previous
line started
{SQL[N]=$0 # with
PARSING, print
PARSE_FLAG=0 # the SQL.
N=""
}
}
/^PARSING/ {FS=" "
N=$4
sub("#","",N)
if (N in SQL)
prinfo(N)
PARSE_FLAG=1
}
/^WAIT/ {FS="#| nam=|ela=|p1="
N=$2
sub(":","",N)
PARSE_FLAG=2
n_wait[N,$3] += 1
ela_wait[N,$3] += $4
}
END {for (N in SQL) { # Print Wait
statistics for final
printf "\n\n\n%s\n\n", SQL[N] # SQL
statement in file
for (k in n_wait) {
split(k,arg,SUBSEP)
if (arg[1]==N && n_wait[k]!=0) {
printf "%-35s %-12s %12.2f\n",
arg[2],n_wait[k],ela_wait[k]/100
n_wait[k]=0
ela_wait[k]=0
}
}
printf "\n\n"
}
for (k in n_wait) {
split(k,arg,SUBSEP)
if (n_wait[k] != 0) {
printf "%-35s %-12s %12.2f\n",
arg[2],n_wait[k],ela_wait[k]/100
n_wait[k]=0
ela_wait[k]=0
}
}
}
function prinfo(N, k) {
printf "\n\n\n%s\n\n", SQL[N]
for (k in n_wait){
split(k,arg,SUBSEP)
if (arg[1]==N && n_wait[k]!=0) {
printf "%-35s %-12s %12.2f\n",
arg[2],n_wait[k],ela_wait[k]/100
n_wait[k]=0
ela_wait[k]=0
}
}
}
' $1
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, May 08, 2002 12:08 PM
> Is there a commercial tool that will do the same thing
> as the tool from hotsos? I am sort of angry with oracle
> for not providing such tool to all of it's users. I'm more
> inclined to test my abilities as a DBA (and those have been put to
> test once or twice) then to pay $50/month to a 3rd party company
> whose only advantage is that it has access to the information
> and the tool that I do not have. If the information was available
> to me, I could have used a little bit of flex/bison in conjunction
> with OCI to write such a thing.
> If I have to buy the tool, so be it, but then I want to use it
> whenever I want, without the monthly fee. This, with all due respect
> looks like milking a gullible cash cow and, with all due respect, I
> don't like that.
> I think that oracle should make public the information needed for such a
> tool so that the rest of us who know how to put together a yacc grammar
> can at least take a shot at writing such a tool.
>
> > -----Original Message-----
> > From: Gaja Krishna Vaidyanatha [mailto:[EMAIL PROTECTED]]
> > Sent: Tuesday, May 07, 2002 9:13 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: formating 10046 (level 12) trace file
> >
> >
> > Mohammed,
> >
> > You can look at 2 known options :-
> >
> > 1) The Hotsos Profiler at
> > http://www.hotsos.com/products/profiler/
> >
> > 2) ITRProf on http://www.ubtools.com/main.html
> >
> > Hope that helps,
> >
> > Gaja
> >
> > --- [EMAIL PROTECTED] wrote:
> > > Hello,
> > >
> > > I was wondering if there is any tool or script which
> > > can get the SQL and its
> > > associated bind values from 10046 (level 12) trace
> > > file and format it in a
> > > readable way..
> > >
> > > Any hints will be highly appreciated..
> > >
> > > Thanks in advance.
> > >
> > > Mohammed Ahsanuddin
> > > Oracle DBA
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > --
> > > Author:
> > > INET: [EMAIL PROTECTED]
> > >
> > > Fat City Network Services -- (858) 538-5051 FAX:
> > > (858) 538-5051
> > > San Diego, California -- Public Internet
> > > access / Mailing Lists
> > >
> > --------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an
> > > E-Mail message
> > > to: [EMAIL PROTECTED] (note EXACT spelling of
> > > 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB
> > > ORACLE-L
> > > (or the name of mailing list you want to be removed
> > > from). You may
> > > also send the HELP command for other information
> > > (like subscribing).
> >
> >
> > =====
> > Gaja Krishna Vaidyanatha
> > Director, Storage Management Products,
> > Quest Software, Inc.
> > Co-author - Oracle Performance Tuning 101
> > http://www.osborne.com/database_erp/0072131454/0072131454.shtml
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Yahoo! Health - your guide to health and wellness
> > http://health.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Gaja Krishna Vaidyanatha
> > INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Gogala, Mladen
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Henry Poras
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
