Re: question on DBMS_SCHEDULER.
Date: Thu, 14 May 2009 12:25:48 +0200
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
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.
On Wed, May 13, 2009 at 21:17, Paresh Patel <paresh.patel_at_mantis-tgi.com>wrote:
> 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…
> *Paresh Patel,*