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_at_ml.com> To: <oracle-l_at_freelists.org>

> 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