Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: formating 10046 (level 12) trace file

RE: formating 10046 (level 12) trace file

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Thu, 09 May 2002 16:13:28 -0800
Message-ID: <F001.0045DDD4.20020509161328@fatcity.com>


That's nice to know Oracle 9i has this feature now.

Regards,

Waleed

-----Original Message-----
[mailto:Mohammed.Ahsanuddin_at_VerizonWireless.com] Sent: Thursday, May 09, 2002 7:13 PM
To: Multiple recipients of list ORACLE-L

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

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
> 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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.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).
Received on Thu May 09 2002 - 19:13:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US