RE: StoredProc SQL statistics

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