jump to navigation

Track or Trace the PL/SQL call made from sql June 3, 2009

Posted by sendtoshailesh in Uncategorized.
trackback
Starting in Oracle 10.2.0.3 (ie not 10.2.0.2 and below) it’s easy to track what package and/or procedure a SQL execution came from using v$session and or v$active_session_history (ASH) which have the new fields
PLSQL_ENTRY_OBJECT_ID
PLSQL_ENTRY_SUBPROGRAM_ID
PLSQL_OBJECT_ID
PLSQL_SUBPROGRAM_ID
For example with ASH we can generate a quick output report like
(http://www.perfvision.com/ash/ashpl2.txt)
COUNT(*) SQL_ID        calling_code
— ———-   ——————–
————–
2 1xxksrhwtz3zf OE.NEWORDER  => DBMS_RANDOM.VALUE
2 1xxksrhwtz3zf OE.NEWORDER  => DBMS_LOCK.SLEEP
13 1xxksrhwtz3zf OE.NEWORDER
76 dw2zgaapax1sg OE.NEWORDER
131 75621g9y3xmvd OE.
BROWSEANDUPDATEORDERS
163 0uuqgjq7k12nf ORDERENTRY.NEWORDER


Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: