what package/procedure did SQL come from?

From: kyle Hailey <kylelf_at_gmail.com>
Date: Mon, 1 Jun 2009 14:58:25 -0700
Message-ID: <6f373fd20906011458n22f84f9au82099f122db75485_at_mail.gmail.com>

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

Received on Mon Jun 01 2009 - 16:58:25 CDT