RE: what package/procedure did SQL come from?

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_at_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_at_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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of kyle Hailey Sent: Monday, June 01, 2009 5:58 PM
To: oracle-l_at_freelists.org
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
--

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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Jun 02 2009 - 17:35:11 CDT