Re: DBMS_JOB problem

Could it be that you are filling the pipe ? I think you get 8k by default after which subsequent sends are blocked until the pipe is cleared.

hth
connor

--
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."
"bhogak" <bhogak_at_yahoo.com> wrote in message
news:9h4vhu$ars$1_at_bob.news.rcn.net...


>

> 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

> >

>

>