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: Mogens Nørgaard <mln_at_miracleas.dk>
Date: Fri, 10 May 2002 03:53:20 -0800
Message-ID: <F001.0045E1B3.20020510035320@fatcity.com>


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:

Mogens

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: Mogens =?ISO-8859-1?Q?N=F8rgaard?=
  INET: mln_at_miracleas.dk

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 Fri May 10 2002 - 06:53:20 CDT

Original text of this message

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