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

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).