Re:StoredProc SQL statistics

Thanks a lot everyone.
I will start working on these things.

Regards
Sam
-----Original Message-----
[mailto:sfaroult_at_oriolecorp.com]
Sent: Wednesday, June 04, 2003 1:50 PM
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
>

x$kglrd, x$kglcursor, sys.obj$, x$kgldp and sys.dependency$ ...
I don't have the details here, so I let you work it out :-).
The first three will let you find out by what a statement (from V$SQL) is fired, which is probably what you need.
If x$kglnaown is null in x$kglrd, the row refers to a PL/SQL block (command type 47 in V$SQL) and you find the corresponding (address, hash_value) in x$kglcursor. Otherwise, it refers to the x$glnaown.x$kglcnam stored object.
All this out of memory (you are lucky I was into this not long ago), so it's not guaranteed to be flawless but it should get you started.

Regards,

Stephane Faroult
Oriole

-- 
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

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


__________________________________
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: sam_orafan_at_yahoo.com

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