It is nice to have good tools to find what and how want quickly.
I simply use statspack to find out my most time consuming SQL. To me
SQL is SQL. Whether I execute it using SQL prompt, or it comes from one
of my PL/SQL procedures.
What I look for it is, how many time I am executing each sql, how many
time it is loaded, how many physical reads I have, and how does it
compare to consistent reads. If I am executing a SQL 100K time and it
takes 1 second, I look at it. If a SQL takes 5 seconds and I execute it
only once, it is not worth my time.
There are many scripts in the DBA books, Tim Gorman website, and Steve
Adams website you could try.
HTH
Mohammed Shakir
- Cary Millsap <[EMAIL PROTECTED]> wrote:
> The information you need is all in the extended (10046 level 8) SQL
> trace
> file. You just have to know how to determine all the recursive SQL
> parent-child relationships. Tkprof and TFA don't even try. This is
> one of
> the problems we wrote our Hotsos Profiler to solve.
>
> DBMS_PROFILER will help if your problem is not SQL; that is, if your
> problem
> happens to be in the PL/SQL code lines that don't make database
> calls.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Hotsos Clinic 101 in Reykjavik, Ottawa, Dallas, Washington, Denver,
> Sydney
> - Visit www.hotsos.com for schedule details...
> - Hotsos Symposium 2004 / March 7-10 Dallas
>
>
> -----Original Message-----
> Sent: Wednesday, June 04, 2003 12:55 AM
> To: Multiple recipients of list ORACLE-L
>
> Hi People,
> Is there any way to find the statistics(like tkprof
> gives) of SQL's within storedproc , when storedrpoc is
> called.
>
> Basically I want to find culprit SQL within
> StoredProc.
>
>
> Any help is appreciated
> Regards
> Sam
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> http://calendar.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: sam d
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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.net
> --
> Author: Cary Millsap
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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).
>
Mohammed Shakir
CompuSoft, Inc.
11 Heather Way
East Brunswick, NJ 08816-2825
(732) 672-0464 (Cell)
(732) 257-6001 (Home)
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mohammed Shakir
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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).
Received on Mon Jun 09 2003 - 19:10:57 CDT
