[Oracle-l] Re: question on DBMS_SCHEDULER.

I will try again.

What does 'stuck' actually mean?

Error messages?

Is this a procedure you can modify?

If so, then instrument it to do logging, and you can easily see where it
dies.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

What I meant here is, I want to know current DML statement processed by
ORACLE server when ORACLE scheduler kicks off the job? So let say I have
10 DML statements in procedure(which is kicked off by ORACLE scheduler), so far ORACLE server has processed first 4 statements and currently
processing 5th one, is there any way I can find out ORACLE server is
processing 5th one by looking at the data dictionary views?



Thanks,

Paresh Patel,



From: Jared Still
Sent: Wednesday, May 13, 2009 3:25 PM
To: Paresh Patel
Cc: oracle-l
Subject: Re: question on DBMS_SCHEDULER.





On Wed, May 13, 2009 at 12:17 PM, Paresh Patel
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.

Hmm, hit the space bar and gmail thought it was ...

I will try again.

What does 'stuck' actually mean?

Error messages?

Is this a procedure you can modify?

If so, then instrument it to do logging, and you can easily see where it
dies.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

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