Re: question on DBMS_SCHEDULER.

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Thu, 14 May 2009 12:25:48 +0200
Message-ID: <cd8f74560905140325k29cb7480uae51b1a86789ff23_at_mail.gmail.com>

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_at_mantis-tgi.com>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,*
>

--
http://www.freelists.org/webpage/oracle-l

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