Re: question on DBMS_SCHEDULER.

From: Martin Berger <>
Date: Thu, 14 May 2009 12:25:48 +0200
Message-ID: <>

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 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

On Wed, May 13, 2009 at 21:17, Paresh Patel <>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,*

Received on Thu May 14 2009 - 05:25:48 CDT

Original text of this message