Re: DBMS_JOB problem

In article <9h39kl$adt$1_at_bob.news.rcn.net>, "bhogak" says...
>
>Hi all,
>
>We have around 22 stored procedures scheduled thru DBMS_JOB. At least, once
>every week, the jobs hang. If I query DBA_JOBS/DBS_JOBS_RUNNING views, I see
>2-3 jobs running for more than 6-7 hours and the next_run_time column is not
>updated for all the other jobs. In such situations, we tried to remove all
>the jobs and resubmit them again. But it doesn't help. Once the DBA bounces
>the database, everything works fine.
>
>We are using Oracle 8i (8.1.7) on Sun Solaris. The Job_Queue_Processes
>parameter in the init.ora is set to 4. (I think we have an Open TAR with
>Oracle on this and our DBA doesn't want to increase this parameter until
>Oracle asks him to do so).
>
>All the stored procedures scheduled through DBA_JOBS have similar
>functionality. They periodically send a message thru DBMS_PIPE. So they do
>run pretty quickly when executed from SQL*Plus.
>
>Any suggestions are appreciated.
>
>Thanks.
>bhogak.
>
>
>
>

You'll need to take a serious look at the logic in your stored procedures. I doubt they are hanging -- rather you are waiting on a pipe message that is never coming.

I would suggest you instruement your code liberally with calls to dbms_application_info so you can set the client_info, action and module columns in v$session with useful state information. Eg, before making a dbms_pipe call you might set these columns to the name of the subroutine being run and provide information about what action you are performing.

In that fashion, the next time your procedures get stuck, you can at least see what they were trying to do.

It sounds like the protocol you've set up using dbms_pipes is getting out of sync and restarting just flushes all of the pipes and starts you over fresh (hence it works).

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp