RE: run PL/SQL in background

<<It can be an acceptable solution if you increase job_processes accordingly.>>

Yes, the developers approach is to have these processes sleep using dbms_lock.

My main concern with starting these via dbms_jobs is that if the number of such long running PL/SQL procedures grows, so will the number of job_processes. I am vary of having too many ora_snp processes running since presumably they use system resources in their own right?

John  

> -----Original Message-----
> From: Stephane Faroult [SMTP:sfaroult_at_oriolecorp.com]
> Sent: 05 April 2002 14:18
> To: Multiple recipients of list ORACLE-L
> Subject: RE: run PL/SQL in background
>
> John,
>
> A PL/SQL procedure run from dbms_job doesn't run continually - it is
> regularly restarted, which is quite different. The problem with
> continually running processes, PL/SQL or others, is that they have an
> innate tendency to take the CPU and not release it. If you want your
> solution to be viable, you need the process to block on something to give
> a chance to the other, competing processes - or you put it in polling mode
> (there's a kind of SLEEP function in one of the supplied packages, cannot
> remember which), or (what I'd prefer) you make it regularly wait on a
> DBMS_PIPE.RECEIVE or a DBMS_ALERT.WAITxxx, with or without a timeout
> shorter than your life expectancy. Now, for starting such a process, if
> you use DBMS_JOB it means that you will 'sacrifice', so to speak, a job
> process which will not be able to do anything else. It can be an
> acceptable solution if you increase job_processes accordingly. Or you can
> have the procedure called by SQL*Plus conveniently wrapped into an OS sc!
> !
> ript, and make the OS script a detached process.
>
> HTH
>
> S Faroult
>
> >----- Original Message -----
> >From: John Dunn <john.dunn_at_sefas.co.uk>
> >To: Multiple recipients of list ORACLE-L
> ><ORACLE-L_at_fatcity.com>
> >Sent: Fri, 05 Apr 2002 02:13:32
> >
> >We have a package that we want to run effectively
> >in the background/batch
> >mode, i.e detached from the pl/sql that initates
> >it. Basically the package
> >would run continually until a table value tells it
> >to stop. Is there an
> >alternative to using dbms_job?
> >
> >John
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Stephane Faroul
> INET: sfaroult_at_oriolecorp.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Dunn
  INET: john.dunn_at_sefas.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).