Re: formating 10046 (level 12) trace file
ait[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
> > &
Re: formating 10046 (level 12) trace file
Torben Holm from Miracle created the Trace File Repository, which is a neat
little utility to put data from the 10046 output files into a couple of tables.
Then it's entirely up to you to either use one of Torbens pre-created output
formats or create your own. Torben - where can these guys get it from?
A couple of comments:
- As has already been said, the information available in the 10046 output
files, can roughly be figured out by anyone. The public notes on Metalink
have been there for years. It's been discussed in books. This has nothing
to do with being inside Oracle or not. And people who have done serious SQL
tuning and research should all have known about this feature since many of
us have been discussing it, talking about it, lecturin about it and dreamed
abou it for many years now. You'd have to be working for Microsoft not to
have heard about it :).
- The trick - what in my opinion sets Cary's tool apart from the rest, including
our TFR - is the very hard, very elaborate research that Jeff Holt and Cary
has put into this project. It's easy to get the first, rough data out of
the trace files. But as you dig into them you'll find a lot of problems with
the interpretation that you cannot find the answer to anywhere, so it's a
question of R&D for months on end. If you study the other tools, including
the new facility available in the 9i tkprof, you'll find that those areas
are not adressed properly. So they might be good enough for your purpose,
but if you want it to be professional you have to use Hotsos - and I think
it's reasonable enough to charge money for work performed, isn't it (in this
case a LOT of work)? Or have I misunderstood the whole IT business?
Mogens
[EMAIL PROTECTED] wrote:
Henry,Thanks for the script..tkprof in 9i has some enhanced functionality whichincludes the wait events from the trace file..which is very similar to whatyour script does in addition to regular tkprof statistics..I have installed and used 9i tkprof with 8i generated trace file. Worksfine.The following is a sample output of the new addition from 9i tkprof.Elapsed times include waiting on following events: Event waited on Times Max. Wait TotalWaited Waited -- SQL*Net message to client 10.000.00 db file sequential read 40.010.02 SQL*Net message from client 10.000.00***
*Also, I would like to thank everyone else for their comments andsuggestions..Mohammed AhsanuddinOracle DBA-Original Message-Sent: Thursday, May 09, 2002 2:46 PMTo: Multiple recipients of list ORACLE-LI did write an awk script to summarize the wait events in a 10046 tracefile. It lists the SQL, and sums the count and time of the waits for theSQL.Henry# Script for analyzing Oracle Trace files with WAIT statistics# Usage: wait_scan.awk # Written:Henry Poras# 5/16/00# Modified: 12/3/01 Initially assumes all wait states for a cursor arebetween# 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)") # printcolumn headers } {if (PARSE_FLAG==1) # if previousline started{SQL[N]=$0 # withPARSING, 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){ # Prin
t Waitstatistics for finalprintf "\n\n\n%s\n\n", SQL[N] # SQLstatement in filefor (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
RE: formating 10046 (level 12) trace file
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 containi
RE: formating 10046 (level 12) trace file
formation 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). -- 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).
Re: formating 10046 (level 12) trace file
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
# 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..
> > >
> &g
