| DBMS_JOB: Numerous jobs [message #614054] |
Thu, 15 May 2014 02:51  |
 |
ibergo
Messages: 18
Registered: February 2014
|
Junior Member |
|
|
Hi
We have a procedure that run for (up to) several hours, doing the same calculation for different items. Since the job runs in an isolated environment (i.e. not the production database), we think the job is an obvious candidate for parallelization, which we plan to use dbms_job for by submitting a job for each of the items calculations mentioned.
The described approch would mean submitting a peak of around 15-20.000 jobs in a run.
Should I by any means be worried about that number?
I consider for example to submit the jobs in batches of XX jobs (500?), and wait for these to finish before submitting the next batch and so on. Am I just worrying without reason?
/Ibergo
|
|
|
|
| Re: DBMS_JOB: Numerous jobs [message #614055 is a reply to message #614054] |
Thu, 15 May 2014 02:56  |
John Watson
Messages: 8928
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Probably you should not use DBMS_JOB for this. You should use Scheduler lightweight jobs, which are specifically intended to handle this sort of situation.
DBMS_JOB is very twentieth century.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: DBMS_JOB: Numerous jobs [message #616565 is a reply to message #614054] |
Wed, 18 June 2014 06:29 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
ibergo wrote on Thu, 15 May 2014 13:21we plan to use dbms_job for by submitting a job for each of the items calculations mentioned.
IMO, use DBMS_SCHEDULER, and using CHAINS control the jobs flow.
Quote:
I consider for example to submit the jobs in batches of XX jobs (500?), and wait for these to finish before submitting the next batch and so on.
Use SET_SCHEDULER_ATTRIBUTE procedure to set the maximum value for max_job_slave_processes. In your case, set it to 500. Schedule them at same time. And using chain you can further control the flow.
|
|
|
|
|
|
| Re: DBMS_JOB: Numerous jobs [message #616567 is a reply to message #616556] |
Wed, 18 June 2014 06:49 |
|
ibergo
Messages: 18
Registered: February 2014
|
Junior Member |
|
|
Michel Cadot wrote on Wed, 18 June 2014 04:57
My opinion is "use dbms_job".
Thanks Michel for your advice. I think I will go for that, and "switch" to dbms_scheduler only if dbms_job ends up not being robust enough in terms of handling errors and exceptions.
/Ibergo
[Updated on: Wed, 18 June 2014 07:01] Report message to a moderator |
|
|
|
| Re: DBMS_JOB: Numerous jobs [message #616569 is a reply to message #616566] |
Wed, 18 June 2014 07:15 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
ibergo wrote on Wed, 18 June 2014 17:14
Ref this the use of MAX_JOB_SLAVE_PROCESSES is deprecated. In my case JOB_QUEUE_PROCESSES is set to 1000.
So from release 2 onwards you do ALTER SYSTEM set JOB_QUEUE_PROCESSES = value, doesn't stop you from using the feature. And although you mention 1000 doesn't guarentee that scheduler will start 1000 slaves. It will always be <1000 and might reach max 1000.
|
|
|
|
| Re: DBMS_JOB: Numerous jobs [message #616570 is a reply to message #616567] |
Wed, 18 June 2014 07:22 |
 |
Michel Cadot
Messages: 68637
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
I still use dbms_job, dbms_scheduler is only good when you want to do things that can't be done with dbms_job like chaining, natively calling program or shell scripts, using windows... otherwise dbms_job is the best one.
You can read DBMS JOB complex scheduling, the methods explain there can be used for things other than scheduling.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
