Hi Paresh, there are some directions you can investigate: To check, if the session really hangs, just sample SEQ#, EVENT FROM v$SESSION_WAIT WHERE SID=xxx only if both of them stay the same for all the samples, the session really hangs (or is in non-instrumented code-parts). Otherwise it is doing anything. (But just doesn't tell you, what). If your SQL_* columns in V$SESSION are empty, you might try to grab PREV_* columns instead to narrow the part where the session is currently working. As you mentioned in later posts, you have some DMLs, a ROLLBACK is a possibility all the time. maybe you want to check http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_30.shtml#IstheTransactionMovingForwardorRollingBack and sample v$transaction a little bit, just to check if you are within a rollback. v$open_cursor will give you a set of recently used cursors by the session, but as you have a pl/sql procedure (job) you might have pre-parsed any of your DMLs, so without knowing the exact code it might be misleading. If you are on 10g+ and proper licensed, v$active_session_history will give you a good information about the past minutes of your session (if the statements speed is not below the sampling interval). at the end, tracing (event 10046) is a possibility all the time, if you have no other way, but it conflicts with your initial goal to only use the data dictionary. I hope this gave you some starting vectors. br Martin On Wed, May 13, 2009 at 21:17, Paresh Patel <paresh.patel@xxxxxxxxxxxxxx>wrote: > Folks, > > > > I have one procedure which contains no of DML statements. We have used > DBMS_SCHEDULER to kick off this procedure every night. This procedure gets > stuck intermittently and we tried to find out the exact SQL statement which > is getting stuck using data dictionary views but we couldn’t as Oracle > server doesn’t provide SQL_ID/SQL_HASH_VALUE/SQL_ADDRESS or in v$SESSION. > If anyone has any idea please do let me know. > > > > Appreciate your help… > > > > *Thanks,* > > *Paresh Patel,* >