RE: what package/procedure did SQL come from?
Date: Tue, 2 Jun 2009 18:35:11 -0400
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);
4 select /* this is from the my_test_proc procedure */ 'x' into dummy from dual; 5 end;
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,
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
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
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
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 )
http://www.freelists.org/webpage/oracle-l Received on Tue Jun 02 2009 - 17:35:11 CDT