Re: formating 10046 (level 12) trace file

Hi,

I resisted, but I decided to join in this thread after reading some indirect comments about our products.

• As said by others, trace file format is documented. But, there are many-many traps in raw trace files. Reading them by eyes or by a simple script may give wrong results. In other words, it's not easy as seen. I'm sharing my knowlege if somone posts a question about event 10046 trace files. I've never hided my knowledge through my life.
• I've developed itrprof in 6 months*15 hours. It consists of 5500+ lines. Many of you used it. I didn't want to meantion about it. But, If I see "other products are wrong" in ORACLE-L, I have right to post this. If someone claims that "others are wrong" they should put the real cases to confirm their thoughs.I do it. We trust our products as our customers.
• Yes, we are not selling itrprof. We offer it as service. This is our business policy. You may understand us. We are living in the same world too. But, our other products iOraBugFinder, iOraHangAnalyzer, iOraDumpReader are free. Many of you used them. We are giving free support for these products. Some of you got free support for their ORA-600/ORA-7445 trace files.

Sorry for this kind of email.

btw,

you should not format 8i trace files by tkprof of 9i. Time granularity is different in 8i and 9i.

danisment...

Mohammed.Ahsanuddin_at_verizonwireless.com wrote:

> Henry,
>
> Thanks for the script..tkprof in 9i has some enhanced functionality which
> includes the wait events from the trace file..which is very similar to what
> your script does in addition to regular tkprof statistics..
>
> I have installed and used 9i tkprof with 8i generated trace file. Works
> fine.
>
> 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 Total
> Waited
> ---------------------------------------- Waited ----------
> ------------
> SQL*Net message to client 1 0.00
> 0.00
> db file sequential read 4 0.01
> 0.02
> SQL*Net message from client 1 0.00
> 0.00
> ****************************************************************************
> ****
>
> Also, I would like to thank everyone else for their comments and
> suggestions..
>
> Mohammed Ahsanuddin
> Oracle DBA
>
> -----Original Message-----
> Sent: Thursday, May 09, 2002 2:46 PM
> To: Multiple recipients of list ORACLE-L
>
> 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" <ORACLE-L_at_fatcity.com>
> 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:oraperfman_at_yahoo.com]
> > > 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
> > >
> > > --- Mohammed.Ahsanuddin_at_VerizonWireless.com 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: Mohammed.Ahsanuddin_at_VerizonWireless.com
> > > >
> > > > 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: ListGuru_at_fatcity.com (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: oraperfman_at_yahoo.com
> > >
> > > 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: ListGuru_at_fatcity.com (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: MGogala_at_oxhp.com
> >
> > 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: ListGuru_at_fatcity.com (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: hporas_at_attbi.com
>
> 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: ListGuru_at_fatcity.com (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: Mohammed.Ahsanuddin_at_VerizonWireless.com
>
> 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: ListGuru_at_fatcity.com (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).

--
Danisment Gazi Unal
http://www.ubTools.com


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Danisment Gazi Unal
  INET: dunal_at_ubTools.com

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: ListGuru_at_fatcity.com (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).