Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Running multiple instances of the same job
Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0407291227.3cdb7481_at_posting.google.com>...
> manishrb_at_hotmail.com (Manish) wrote in message news:<55e6afeb.0407290638.50242f69_at_posting.google.com>...
> > Hi All,
> >
> > I have a plsql stored procedure that I am submitting as a job using
> > dbms_job.submit. The procedure accepts user id as a param writes to a
> > single table that has a user_id column apart from other columns
> > (distinct rows for users). Also the amount of execution time is
> > different for different user (ranging anywhere from 3 mins to 25
> > mins). I have a situation where multiple users would submit requests
> > for the job and I want to run them in parallel. I read on AskTom that
> > I could set the job_queue_processes to the number of jobs that I want
> > to run and this would enable execution in parallel. I would like to
> > know if this approach would work in my case as the plsql proc is
> > actually writing to the same table ? Will this lead into any kind of
> > contention.
> >
> >
> > Thanks much for your help
>
> Since each job would be accessing rows specific to each user then
> there should be no locking conflict as long as one user did not submit
> multiple jobs. If the jobs only read then there is no locking
> conflict to begin with.
>
> Depending on DML activity (inserts, updates, and deletes) you could
> run into some resource contention for ITL and freelists if the
> activity is directed to the same blocks, but this does not seem
> likely.
>
> HTH -- Mark D Powell --
I'd like to add ...
segment compression based upon a job_number could be used to direct
each job to its own blocks in the tables of interest.
GTTs might help if the info need not survive past the session.
If you go with just heap tables and no compression, don't forget to hike freelists and ITLs for any indexes on those tables also.
hth.
Pd Received on Fri Jul 30 2004 - 00:41:25 CDT