RE: StoredProc SQL statistics

From: <Reginald.W.Bailey_at_jpmorgan.com>
Date: Tue, 10 Jun 2003 09:24:14 -0700
Message-ID: <F001.005AE562.20030610090949@fatcity.com>

Mr Shakir:

Can you tell me the URL for the "Tim Gorman website, and Steve Adams website"?

RWB

Reginald W. Bailey
IBM Global Services - ETS SW GDSD - Database Management Your Friendly Neighborhood DBA
713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager)

                                                                                       
                                      
                    [EMAIL PROTECTED]                                                  
                                         
                    ahoo.com             To:     [EMAIL PROTECTED]                     
                                   
                    Sent by:             cc:                                           
                                      
                    [EMAIL PROTECTED]       Subject:     RE: StoredProc SQL statistics 
                                         
                    ity.com                                                            
                                      
                                                                                       
                                      
                                                                                       
                                      
                    06/09/2003                                                         
                                      
                    07:34 PM                                                           
                                      
                    Please respond                                                     
                                      
                    to ORACLE-L

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



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  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 Tue Jun 10 2003 - 11:24:14 CDT