top of page
  • Writer's picturekyle Hailey

Correlating SQL to Procedures

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


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

using SQL like ( http://www.perfvision.com/ash/ashpl2.sql)

1 view0 comments

Recent Posts

See All

for all questions on the site ashmasters.com, please asked them in the comment section on this blog post. Due to the enormously high amount of spam on that site, I am no longer responding to the posts

ASH is now at 93 fields in 11gR2, starting from an original 30 in 10gR1 Here is a spread sheet across 10.1.0, 10.2.0.1, 10.2.0.3,11.1, 11.2 http://spreadsheets.google.com/pub?key=t7c5YZ1hD_I25jLa2D6Dd

bottom of page