1

Is there any way to limit number of IMMEDIATE/ONCE type jobs that can run simultanously.

Scenario: Parameter job_queue_processes is set to 10.

There's one recurring job (Name: TEST_JOB) configured which runs after every 1 seconds interval and performs a critical application processing task.

Application also has some batches which can be executed on request basis and these batches also use DBMS_SCHEDULER jobs for faster processing. These jobs are created automatically by the batch with AUTO_DROP=TRUE configuration and are to be executed only once. The number of jobs created by batch can range between 50 to 100 and these jobs are created under DEFAULT_CLASS.

Problem is, when a batch is executed, since the whole job_queue_processes slots/jobs are consumed by the jobs submitted by batch (as number of submitted jobs is higher than job_queue_processes value), the important recurring TEST_JOB has to wait for a running job to finish.

Is there any way to configure DBMS_SCHEDULER to use only 9 (out of 10) slots/jobs for one time execution jobs and keep 1 slot/job always available for recurring TEST_JOB.

Thanks for the help.

user1140840
  • 79
  • 10

1 Answers1

1

I'm not aware of a way to reserve a job process like this. That said, you have a couple of options:

Make the critical job always run

Instead of ending the job when the critical task completes, loop round to run it again:

begin
  loop important_task; end loop;
end;

This means the job never stops, so no other process can consume all the available job slots.

I'd add some kind of option to break out of the loop (e.g. check a row in a table and exit if this is STOPPED) so you can disable it for upgrades, etc. and you may want a short sleep between runs, but the principle remains the same.

Prioritize the critical job

The scheduler has powerful job prioritization capabilities; you may be able to use this to ensure the critical job is able to execute by limiting the resources available to the other batch jobs.

Chris Saxon
  • 9,105
  • 1
  • 26
  • 42
  • Thanks Chris for the answer. I see keeping the job always running is the only option as batch jobs are created under a different job class than critical task job and priority only works within same class. – user1140840 Feb 02 '21 at 06:15