Re: DBMS_JOB problem

Thank you. I did not know that sends would be blocked after the default pipe size. I thought I would get an error message.I will look into this more closely.

bhogak.
Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message news:9h5kvb$v0b$1_at_news.chatlink.com...
> 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_at_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 - 20:38:05 CDT