Re: Link procedure to sql statement in v$sqlarea

Hi Ajay,
There is an X$ structure for mapping the read-only dependencies between stored objects and shared cursors, namely x$kglrd. You can use it to link the x$kglob record for the procedure XYZ to the x$kglcursor record for the select statement as follows ... 

select /*+ ordered use_hash(d) use_hash(c) */
  -- whatever you want
from
  sys.x$kglob  o,
  sys.x$kglrd  d,
  sys.x$kglcursor  c,
  -- other tables in join
where
  d.kglhdcdr = o.kglhdadr and
  c.kglhdpar = d.kglrdhdl and
  -- other predicates
Then all you need to do is to see if both are currently pinned (x$kglpn) by the same session. 

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/         - For DBAs
@   http://www.christianity.net.au/  - For all

-----Original Message-----
Subject: Link procedure to sql statement in v$sqlarea
Date: Tue, 2 Oct 2007 15:35:13 -0400
From: "Thotangare, Ajay \(GTI\)" <Ajay_Thotangare@xxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>


Hi,

I have a question about v$sqlarea. Appreciate your help/inputs on this.
Background
-----------------
I have a procedure named 'XYZ' and inside this procedure I have select statement. When I execute this procedure I can see a entry for procedure 'XYZ' and also a entry for select statement (which is actually inside procedure). How do I know if this select statement has come from this procedure or somebody else fired this statement as standalone (not using procedure) 

 e.g.
create or replace procedure PQR as
begin execute immediate 'select object_name AS INSIDE_PQR_PRC from user_objects'; end; 
/
Procedure created.

SQL> exec PQR;
PL/SQL procedure successfully completed.
SQL> select rownum,sql_text,executions from v$sqlarea where sql_text like '%PQR%'; ROWNUM SQL_TEXT EXECUTIONS ------ ----------------------------------------------------------------- ---------- 1 select rownum,sql_text,executions from v$sqlarea where sql_text l 3 
       ike '%PQR%'
2 BEGIN PQR; END; 1 3 select object_name AS INSIDE_PQR_PRC from user_objects 1 3 rows selected.
How do I know that statement "select object_name AS INSIDE_PQR_PRC from user_objects" is actually fired from procedure and not from outside. 

--
http://www.freelists.org/webpage/oracle-l

Other related posts: