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 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) { # Prin t 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_wai t[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 PMIs 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
__________________________________________________
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).
