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

Search Discussions

  • Gaja Krishna Vaidyanatha at May 8, 2002 at 1:13 am
    Mohammed,

    You can look at 2 known options :-

    The Hotsos Profiler at
    http://www.hotsos.com/products/profiler/

    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).
  • DENNIS WILLIAMS at May 8, 2002 at 3:58 pm
    I very much appreciate these programs being offered for free. I really
    appreciate the people who have blazed the trail in this area and well feel
    they deserve success.
    However, I am curious whether anyone has tried to create their own with Perl
    (or similar tool)?
    Just put me down as more of a "do-it-yourselfer".
    Dennis Williams
    DBA

    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    Sent: Tuesday, May 07, 2002 8:13 PM
    To: Multiple recipients of list ORACLE-L

    Mohammed,

    You can look at 2 known options :-

    The Hotsos Profiler at
    http://www.hotsos.com/products/profiler/

    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: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.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).
  • Gogala, Mladen at May 8, 2002 at 4: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
    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).
  • Paul Vallee at May 8, 2002 at 4:52 pm
    You can also look up George Schlossnagle's excellent "trace2perl" utility on
    freshmeat.net.
    HTH,

    Paul

    ---
    www.pythian.com -- vallee_at_pythian.com -- 877-PYTHIAN
    Smarter than adding another team member, Pythian has new services for
    supplementing DBAs: get our help with monitoring, 24x7 on-call, daily
    verifications, storage management, performance and more.

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Wednesday, May 08, 2002 11:58 AM

    I very much appreciate these programs being offered for free. I really
    appreciate the people who have blazed the trail in this area and well feel
    they deserve success.
    However, I am curious whether anyone has tried to create their own with Perl
    (or similar tool)?
    Just put me down as more of a "do-it-yourselfer".
    Dennis Williams
    DBA
    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    Sent: Tuesday, May 07, 2002 8:13 PM
    To: Multiple recipients of list ORACLE-L

    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: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.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: Paul Vallee
    INET: dbalist_at_pythian.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).
  • Tim Gorman at May 8, 2002 at 5:08 pm
    Mladen,

    The people who put those tools together (at Hotsos and UBTools) did not
    "receive secret information" or "have access to information" or use some
    other shortcut not available to you. Rather, they exhaustively and
    laboriously researched the mechanisms involved to develop these tools.
    These tools are the result of a tremendous amount of effort on their part:
    research, observation, testing, and persistence (often "after hours"). They
    have doubtlessly shared these tools for free with friends and colleagues,
    but like anyone else, they deserve compensation if someone (not a friend or
    colleague) is going to make use of efforts. That's just the way the world
    works.

    I was speaking to a CIO recently who said, "Hey, there's no magic in this
    stuff. I can write SQL too!". Yup, but is that how you want to spend your
    time and can you really do all the other stuff that comes with creating
    tuned applications? Consider the "build-vs-buy" aspects as well...

    Oracle Corporation protects its intellectual property quite vigorously, but
    even they cannot stop someone from "reverse-engineering" in order to
    understand the mechanisms. Nobody at Hotsos or UBTools has secret passwords
    (Oracle Legal would freak out!). Only inspiration, a good network of
    talented friends, and a helluva work ethic...

    ...just my $0.02...

    -Tim

    Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Wednesday, May 08, 2002 10:08 AM
    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
    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: Tim Gorman
    INET: Tim_at_SageLogix.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).
  • Gogala, Mladen at May 8, 2002 at 8:53 pm
    I'm not talking about secret passwords, I'm taking
    about the format of a trace file which is not documented
    anywhere and the interpretation of all those wait events.
    I believe that his documentation is only available to those
    who are (or were) working for oracle. I agree about the
    compensation, I have no problem with that, that is why I am
    asking for the information which would enable me to write such
    a program, not about the program itself. Normally, compensation
    for a program is achieved by selling the program, as with Oracle
    RDBMS. If I buy oracle database, I'm not charged for a monthly
    usage and the software resides on my machine.
    I've already done the same thing with conversion from
    Sqlforms 2.3 -> Forms 3.0. It was a month worth of flex & bison
    getting all those "success steps" and "failure steps"
    rewritten as "if then else" constructs in PL/SQL. The company that
    was selling that stuff was trying to charge PER TRIGGER! No way in....
    The same logic applies here. If they're selling the tool, that's OK,
    but I'm not willing to be charged for service. I want to have the tool
    and the documentation available on my machine whenever I like it.
    They're great consultants, and they are attempting to establish themselves
    as an online service provider (the same, I believe, is the case with
    Jenna Jameson's web site) but I started my career on RSX and KED and
    assembling NCP for 3084, which means that I have different ideas about
    software sales. After all, I've Been Mugged with IMS, CICS DL/I and alike
    much before Oracle.
    -----Original Message-----
    From: Tim Gorman
    Sent: Wednesday, May 08, 2002 1:09 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: formating 10046 (level 12) trace file


    Mladen,

    The people who put those tools together (at Hotsos and
    UBTools) did not
    "receive secret information" or "have access to information"
    or use some
    other shortcut not available to you. Rather, they exhaustively and
    laboriously researched the mechanisms involved to develop these tools.
    These tools are the result of a tremendous amount of effort
    on their part:
    research, observation, testing, and persistence (often "after
    hours"). They
    have doubtlessly shared these tools for free with friends and
    colleagues,
    but like anyone else, they deserve compensation if someone
    (not a friend or
    colleague) is going to make use of efforts. That's just the
    way the world
    works.

    I was speaking to a CIO recently who said, "Hey, there's no
    magic in this
    stuff. I can write SQL too!". Yup, but is that how you want
    to spend your
    time and can you really do all the other stuff that comes
    with creating
    tuned applications? Consider the "build-vs-buy" aspects as well...

    Oracle Corporation protects its intellectual property quite
    vigorously, but
    even they cannot stop someone from "reverse-engineering" in order to
    understand the mechanisms. Nobody at Hotsos or UBTools has
    secret passwords
    (Oracle Legal would freak out!). Only inspiration, a good network of
    talented friends, and a helluva work ethic...

    ...just my $0.02...

    -Tim

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Wednesday, May 08, 2002 10:08 AM

    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
    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: Tim Gorman
    INET: Tim_at_SageLogix.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).
  • Paul.Parker_at_bmwna.com at May 8, 2002 at 9:33 pm
    Actually, the trace file format is very well documented in Note :39817.1 in
    Metalink

    Paul

    -----Original Message-----
    Sent: Wednesday, May 08, 2002 4:53 PM
    To: Multiple recipients of list ORACLE-L

    I'm not talking about secret passwords, I'm taking
    about the format of a trace file which is not documented
    anywhere and the interpretation of all those wait events.
    I believe that his documentation is only available to those
    who are (or were) working for oracle. I agree about the
    compensation, I have no problem with that, that is why I am
    asking for the information which would enable me to write such
    a program, not about the program itself. Normally, compensation
    for a program is achieved by selling the program, as with Oracle
    RDBMS. If I buy oracle database, I'm not charged for a monthly
    usage and the software resides on my machine.
    I've already done the same thing with conversion from
    Sqlforms 2.3 -> Forms 3.0. It was a month worth of flex & bison
    getting all those "success steps" and "failure steps"
    rewritten as "if then else" constructs in PL/SQL. The company that
    was selling that stuff was trying to charge PER TRIGGER! No way in....
    The same logic applies here. If they're selling the tool, that's OK,
    but I'm not willing to be charged for service. I want to have the tool
    and the documentation available on my machine whenever I like it.
    They're great consultants, and they are attempting to establish themselves
    as an online service provider (the same, I believe, is the case with
    Jenna Jameson's web site) but I started my career on RSX and KED and
    assembling NCP for 3084, which means that I have different ideas about
    software sales. After all, I've Been Mugged with IMS, CICS DL/I and alike
    much before Oracle.
    -----Original Message-----
    From: Tim Gorman
    Sent: Wednesday, May 08, 2002 1:09 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: formating 10046 (level 12) trace file


    Mladen,

    The people who put those tools together (at Hotsos and
    UBTools) did not
    "receive secret information" or "have access to information"
    or use some
    other shortcut not available to you. Rather, they exhaustively and
    laboriously researched the mechanisms involved to develop these tools.
    These tools are the result of a tremendous amount of effort
    on their part:
    research, observation, testing, and persistence (often "after
    hours"). They
    have doubtlessly shared these tools for free with friends and
    colleagues,
    but like anyone else, they deserve compensation if someone
    (not a friend or
    colleague) is going to make use of efforts. That's just the
    way the world
    works.

    I was speaking to a CIO recently who said, "Hey, there's no
    magic in this
    stuff. I can write SQL too!". Yup, but is that how you want
    to spend your
    time and can you really do all the other stuff that comes
    with creating
    tuned applications? Consider the "build-vs-buy" aspects as well...

    Oracle Corporation protects its intellectual property quite
    vigorously, but
    even they cannot stop someone from "reverse-engineering" in order to
    understand the mechanisms. Nobody at Hotsos or UBTools has
    secret passwords
    (Oracle Legal would freak out!). Only inspiration, a good network of
    talented friends, and a helluva work ethic...

    ...just my $0.02...

    -Tim

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Wednesday, May 08, 2002 10:08 AM

    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
    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: Tim Gorman
    INET: Tim_at_SageLogix.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:
    INET: Paul.Parker_at_bmwna.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).
  • Henry Poras at May 9, 2002 at 6:45 pm
    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
    # 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"
    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
    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).
  • Mohammed.Ahsanuddin_at_VerizonWireless.com at May 9, 2002 at 11:13 pm
    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
    # 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"
    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
    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).
  • Khedr, Waleed at May 10, 2002 at 12:13 am
    That's nice to know Oracle 9i has this feature now.

    Regards,

    Waleed

    -----Original Message-----

    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

    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
    # 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"
    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
    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).
  • Mogens Nørgaard at May 10, 2002 at 11:53 am
    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
    # 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"
    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
    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).
  • Danisment Gazi Unal at May 10, 2002 at 7:48 pm
    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
    # 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"
    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
    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).

Related Discussions

People

Translate

site design / logo © 2022 Grokbase