RE: what package/procedure did SQL come from?

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
  • To: "kylelf@xxxxxxxxx" <kylelf@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 2 Jun 2009 18:35:11 -0400

Ah ha!

I was thinking about this yesterday....I could have sworn I learned this at 
some point in the past, but then forgot....and after much digging yesterday, I 
got busy w/ other stuff and forgot about it.

Well, today, I was swamping though some X$ tables, in pursuit of other 
information, and I stumbled across it!!

See X$KGLRD for the information you're looking for!
 
Here's an example:

MBOBAK@mstmfgpep> create or replace procedure my_test_proc is
  2  dummy varchar2(1);
  3  begin
  4  select /* this is from the my_test_proc procedure */ 'x' into dummy from 
dual;
  5  end;
  6  /

Procedure created.

Now, as SYS, I do:
SYS@mstmfgpep> exec print_table('select * from x$kglrd where 
kglnaown=''MBOBAK'' and kglnacnm=''MY_TEST_PROC''');
ADDR                          : 0000002A970454F0
INDX                          : 919
INST_ID                       : 1
KGLHDCDR                      : 0000000086468038
KGLNAOWN                      : MBOBAK
KGLNACNM                      : MY_TEST_PROC
KGLNACNL                      : 12
KGLNACHV                      : 524130581
KGLHDPDR                      : 00
KGLDEPNO                      : 0
KGLRDHDL                      : 000000008645CA78
KGLNADNM                      : SELECT 'x' FROM DUAL
KGLNADNL                      : 20
KGLNADHV                      : 4261573303
KGLRDFLG                      : 32
-----------------

PL/SQL procedure successfully completed.



Hope that helps,

-Mark

PS  The print_table procedure is a T.Kyte special, available here:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:455220177497#18001977820778



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of kyle Hailey
Sent: Monday, June 01, 2009 5:58 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: what package/procedure did SQL come from?

I want to correlate SQL to the packages and procedures they came from.
Is there a way to do this before 10.2.0.4?

Starting in 10.2.0.4 this is pretty easy  thanks to the fields

PLSQL_ENTRY_OBJECT_ID
PLSQL_ENTRY_SUBPROGRAM_ID
PLSQL_OBJECT_ID
PLSQL_SUBPROGRAM

in v$session and v$active_session_history. A nice output can be put
out using a script like

     http://www.perfvision.com/ash/ashpl2.sql

 to give

COUNT(*) SQL_ID        calling_code
--------- -------------
--------------------------------------------------------------------
        2 1xxksrhwtz3zf ORDERENTRY.NEWORDER  => DBMS_RANDOM.VALUE
        2 1xxksrhwtz3zf ORDERENTRY.NEWORDER  => DBMS_LOCK.SLEEP
        3 1xxksrhwtz3zf ORDERENTRY.NEWORDER  => DBMS_APPLICATION_INFO.SET_ACTION
       13 1xxksrhwtz3zf ORDERENTRY.NEWORDER
       76 dw2zgaapax1sg ORDERENTRY.NEWORDER
      131 75621g9y3xmvd ORDERENTRY.BROWSEANDUPDATEORDERS
      163 0uuqgjq7k12nf ORDERENTRY.NEWORDER

(the count could be changed to %activity or average active sessions )

Best
Kyle Hailey
http://oraclemonitor.com
--
//www.freelists.org/webpage/oracle-l




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


Other related posts: