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)
Kyle
ReplyDeleteIf used your script (ashpl2.sql) and changed
v$active_session_history to dba_hist_active_sess_history with a sample_time. I noticed that sometimes the SQL_ID is null. Could you explain why ?
COUNT(*) SQL_ID calling_code
---------- ------------- --------------------------------------------------
13 bj92fs2zm13bf
20 bj92fs2zm13bf FL_STATUS.FLEET_STATUS
21 WQ_PRECALCS.PRECALCS
29 ccczm338847c0
33 10nq9jd4x273q ING_RUN_ACCESSORIES
35 050a9f1ud2c15
45 WQ_PRECALCS.PRECALCS .DISCOUNT_SETUP
60 c9zg2kyk9ky0a
79 WQ_PRECALCS.PRECALCS => TEST.INFO
160
169 3n2sptrjvufwz
231 0nq13d4p2mpn8
4885 WQ_PRECALCS.PRECALCS
not all actions are associated with SQL, for example I could be in a plsql loop summing variable values an and just burning CPU, or I could do a dbms_lock.sleep and my process would be waiting but no SQL. Some background processes register IO and CPU with no SQL associated such as LGWR.
ReplyDeleteOn the other hand I see some background processes doing what must be SQL as they are doing IO on tables. I'm not sure if such SQL is intentionally hidden by Oracle or not.
That what I've noticed so far - definitely something I want to quantify more precisely