Re: DBMS_JOB problem

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:9h4so7018mq_at_drn.newsguy.com...
> 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.

Thanks for the reply, but I am sure this is not the problem. The Stored Procedures do not receive messages. They just send messages if they find any recs in a table to process. In other words, they don't wait for anything. The receiving part is done by Pro*C listeners and I don't have any problems with them.
>
> 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.
>

I will try this. I was querying V$sqlarea and V$session to see the actual SQL Text, but it doesn't tell me what exactly it is doing. I will make changes as you suggested.

> 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
>
Received on Sun Jun 24 2001 - 10:05:20 CDT