The maximum number of database scheduler jobs that can run concurrently on each Oracle instance is primarily controlled by the parameter JOB_QUEUE_PROCESSES. The default value is the lesser of 20*CPU_COUNT or SESSIONS/4. I think 20 jobs per CPU is usually far too high because gives the scheduler the potential to swamp the CPU. Therefore, I usually reduce this parameter, often setting it to the same value as CPU_COUNT, so if you have 10 vCPUs per instance, you can run 10 concurrent jobs on each instance.
However, this is a database-wide parameter.
- What if you want to restrict different jobs to a different number of concurrently executing instances?
- Or, you may have a more complex rule where different jobs have different weights?
You can create a named resource with DBMS_SCHEDULER.CREATE_RESOURCE and give it a certain number of units. Then you can specify the number of units of which resource a particular job consumes with DBMS_SCHEDULER.SET_RESOURCE_CONSTRAINT. This must be done before the job is enabled, and then the job can be enabled afterward.
Test 1: Separate Resources For Each Job
In this test:
- Each TEST_An job runs for 30 seconds and consumes 2 units of resource A, which has 10 units, so five jobs can run.
- Each TEST_Bn job runs for 30 seconds and consumes 1 unit of resource B, which has 3 units, so three jobs continue.
- The constraints on the two types of jobs are independent.
BEGIN
DBMS_SCHEDULER.create_resource (
resource_name => 'TEST_RESOURCE_A',
units => 10,
status => 'ENFORCE_CONSTRAINTS',
constraint_level => 'JOB_LEVEL');
DBMS_SCHEDULER.create_resource (
resource_name => 'TEST_RESOURCE_B',
units => 3,
status => 'ENFORCE_CONSTRAINTS',
constraint_level => 'JOB_LEVEL');
END;
/
BEGIN
FOR i IN 1..10 LOOP
dbms_scheduler.create_job (
job_name=> 'TEST_A'||i,
job_type=> 'PLSQL_BLOCK',
job_action=> 'BEGIN DBMS_LOCK.SLEEP(30); END;',
start_date=> sysdate,
enabled=> false);
dbms_scheduler.create_job (
job_name=> 'TEST_B'||i,
job_type=> 'PLSQL_BLOCK',
job_action=> 'BEGIN DBMS_LOCK.SLEEP(30); END;',
start_date=> sysdate,
enabled=> false);
DBMS_SCHEDULER.set_resource_constraint (
object_name => 'TEST_A'||i,
resource_name => 'TEST_RESOURCE_A',
units => 2);
DBMS_SCHEDULER.set_resource_constraint (
object_name => 'TEST_B'||i,
resource_name => 'TEST_RESOURCE_B',
units => 1);
dbms_scheduler.enable('TEST_A'||i);
dbms_scheduler.enable('TEST_B'||i);
END LOOP;
END;
/set pages 99
column job_name format a8
column status format a10
clear screen
select log_id, log_date, job_name, status, actual_start_date, run_duration
from all_scheduler_job_run_details where job_name like 'TEST%'
and actual_start_date >= TRUNC(SYSDATE)+…/24
order by actual_start_date- The first five TEST_A jobs and the first 3 TEST_B jobs ran. As the groups all finished after exactly 30s, new groups were run. I've added spacing to illustrate the groups of jobs that run together.
LOG_ID LOG_DATE JOB_NAME STATUS ACTUAL_START_DATE RUN_DURATION
---------- ------------------------------------ -------- ---------- ------------------------------------------- -------------------
7747242 21/12/2023 06:42:42.106062000 -05:00 TEST_A1 SUCCEEDED 21/12/2023 11:42:11.578114000 EUROPE/LONDON +00 00:00:31.000000
7747244 21/12/2023 06:42:42.105168000 -05:00 TEST_A2 SUCCEEDED 21/12/2023 11:42:11.924307000 EUROPE/LONDON +00 00:00:30.000000
7747246 21/12/2023 06:42:42.615880000 -05:00 TEST_A3 SUCCEEDED 21/12/2023 11:42:12.171116000 EUROPE/LONDON +00 00:00:30.000000
7747248 21/12/2023 06:42:42.615938000 -05:00 TEST_A4 SUCCEEDED 21/12/2023 11:42:12.208987000 EUROPE/LONDON +00 00:00:30.000000
7747250 21/12/2023 06:42:42.615895000 -05:00 TEST_A5 SUCCEEDED 21/12/2023 11:42:12.247785000 EUROPE/LONDON +00 00:00:30.000000
7747210 21/12/2023 06:42:43.680210000 -05:00 TEST_B1 SUCCEEDED 21/12/2023 11:42:13.323724000 EUROPE/LONDON +00 00:00:30.000000
7747212 21/12/2023 06:42:43.681465000 -05:00 TEST_B5 SUCCEEDED 21/12/2023 11:42:13.356243000 EUROPE/LONDON +00 00:00:30.000000
7747214 21/12/2023 06:42:43.680210000 -05:00 TEST_B2 SUCCEEDED 21/12/2023 11:42:13.387883000 EUROPE/LONDON +00 00:00:30.000000
7747276 21/12/2023 06:43:17.947304000 -05:00 TEST_A6 SUCCEEDED 21/12/2023 11:42:47.543438000 EUROPE/LONDON +00 00:00:30.000000
7747278 21/12/2023 06:43:17.947331000 -05:00 TEST_B3 SUCCEEDED 21/12/2023 11:42:47.543510000 EUROPE/LONDON +00 00:00:30.000000
7747280 21/12/2023 06:43:17.949158000 -05:00 TEST_B4 SUCCEEDED 21/12/2023 11:42:47.758469000 EUROPE/LONDON +00 00:00:30.000000
7747282 21/12/2023 06:43:17.947824000 -05:00 TEST_A7 SUCCEEDED 21/12/2023 11:42:47.759084000 EUROPE/LONDON +00 00:00:30.000000
7747284 21/12/2023 06:43:18.457503000 -05:00 TEST_A8 SUCCEEDED 21/12/2023 11:42:47.966750000 EUROPE/LONDON +00 00:00:30.000000
7747286 21/12/2023 06:43:18.457438000 -05:00 TEST_B6 SUCCEEDED 21/12/2023 11:42:48.063658000 EUROPE/LONDON +00 00:00:30.000000
7747320 21/12/2023 06:43:19.008041000 -05:00 TEST_A10 SUCCEEDED 21/12/2023 11:42:48.846141000 EUROPE/LONDON +00 00:00:30.000000
7747322 21/12/2023 06:43:19.008081000 -05:00 TEST_A9 SUCCEEDED 21/12/2023 11:42:48.846239000 EUROPE/LONDON +00 00:00:30.000000
7747332 21/12/2023 06:43:49.215439000 -05:00 TEST_B9 SUCCEEDED 21/12/2023 11:43:19.165493000 EUROPE/LONDON +00 00:00:30.000000
7747334 21/12/2023 06:43:49.729057000 -05:00 TEST_B7 SUCCEEDED 21/12/2023 11:43:19.262625000 EUROPE/LONDON +00 00:00:30.000000
7747336 21/12/2023 06:43:49.726501000 -05:00 TEST_B8 SUCCEEDED 21/12/2023 11:43:19.262675000 EUROPE/LONDON +00 00:00:30.000000
7747290 21/12/2023 06:44:23.992734000 -05:00 TEST_B10 SUCCEEDED 21/12/2023 11:43:53.567006000 EUROPE/LONDON +00 00:00:30.000000Test 2: One Resource Used by Two Jobs
In this second test, both jobs use RESOURCE_A which still has 10 units.
BEGIN
FOR i IN 1..10 LOOP
dbms_scheduler.create_job (
job_name=> 'TEST_A'||i,
job_type=> 'PLSQL_BLOCK',
job_action=> 'BEGIN DBMS_LOCK.SLEEP(30); END;',
start_date=> sysdate,
enabled=> false);
dbms_scheduler.create_job (
job_name=> 'TEST_B'||i,
job_type=> 'PLSQL_BLOCK',
job_action=> 'BEGIN DBMS_LOCK.SLEEP(30); END;',
start_date=> sysdate,
enabled=> false);
DBMS_SCHEDULER.set_resource_constraint (
object_name => 'TEST_A'||i,
resource_name => 'TEST_RESOURCE_A',
units => 2);
DBMS_SCHEDULER.set_resource_constraint (
object_name => 'TEST_B'||i,
resource_name => 'TEST_RESOURCE_A',
units => 1);
dbms_scheduler.enable('TEST_A'||i);
dbms_scheduler.enable('TEST_B'||i);
END LOOP;
END;
/- So initially we had 2 TEST_A jobs (that consume 4 units) and 6 TEST_B jobs (that consume 6 units). This completely consumed RESOURCE_A which only has 10 units. No new jobs that use this resource could start until others were completed.
- Next, we got 4 TEST_A jobs (that consume 8 units) and 2 TEST_B jobs (that consume 2 units), so again this consumed the whole of RESOURCE_A and again no further jobs could run that require this resource until others were completed.
LOG_ID LOG_DATE JOB_NAME STATUS ACTUAL_START_DATE RUN_DURATION
---------- ------------------------------------ -------- ---------- ------------------------------------------- -------------------
7747540 21/12/2023 13:40:10.170737000 -05:00 TEST_B1 SUCCEEDED 21/12/2023 18:39:39.718608000 EUROPE/LONDON +00 00:00:30.000000
7747542 21/12/2023 13:40:10.169080000 -05:00 TEST_B2 SUCCEEDED 21/12/2023 18:39:39.971882000 EUROPE/LONDON +00 00:00:30.000000
7747544 21/12/2023 13:40:10.169451000 -05:00 TEST_B3 SUCCEEDED 21/12/2023 18:39:40.012029000 EUROPE/LONDON +00 00:00:30.000000
7747546 21/12/2023 13:40:10.680827000 -05:00 TEST_B4 SUCCEEDED 21/12/2023 18:39:40.258398000 EUROPE/LONDON +00 00:00:30.000000
7747548 21/12/2023 13:40:10.680943000 -05:00 TEST_B5 SUCCEEDED 21/12/2023 18:39:40.300213000 EUROPE/LONDON +00 00:00:30.000000
7747590 21/12/2023 13:40:10.680683000 -05:00 TEST_B6 SUCCEEDED 21/12/2023 18:39:40.343663000 EUROPE/LONDON +00 00:00:30.000000
7747574 21/12/2023 13:40:11.231396000 -05:00 TEST_A1 SUCCEEDED 21/12/2023 18:39:40.730872000 EUROPE/LONDON +00 00:00:30.000000
7747576 21/12/2023 13:40:11.231575000 -05:00 TEST_A5 SUCCEEDED 21/12/2023 18:39:40.786089000 EUROPE/LONDON +00 00:00:30.000000
7747594 21/12/2023 13:40:40.376871000 -05:00 TEST_A2 SUCCEEDED 21/12/2023 18:40:10.271696000 EUROPE/LONDON +00 00:00:30.000000
7747598 21/12/2023 13:40:40.888493000 -05:00 TEST_A6 SUCCEEDED 21/12/2023 18:40:10.679917000 EUROPE/LONDON +00 00:00:30.000000
7747600 21/12/2023 13:40:40.889568000 -05:00 TEST_A7 SUCCEEDED 21/12/2023 18:40:10.680655000 EUROPE/LONDON +00 00:00:30.000000
7747614 21/12/2023 13:40:41.401080000 -05:00 TEST_B10 SUCCEEDED 21/12/2023 18:40:11.304750000 EUROPE/LONDON +00 00:00:30.000000
7747656 21/12/2023 13:40:42.975067000 -05:00 TEST_A3 SUCCEEDED 21/12/2023 18:40:12.598174000 EUROPE/LONDON +00 00:00:30.000000
7747672 21/12/2023 13:40:51.168059000 -05:00 TEST_B9 SUCCEEDED 21/12/2023 18:40:21.061653000 EUROPE/LONDON +00 00:00:30.000000
7747678 21/12/2023 13:41:13.183397000 -05:00 TEST_A4 SUCCEEDED 21/12/2023 18:40:42.789196000 EUROPE/LONDON +00 00:00:30.000000
7747680 21/12/2023 13:41:13.182999000 -05:00 TEST_A8 SUCCEEDED 21/12/2023 18:40:43.093332000 EUROPE/LONDON +00 00:00:30.000000
7747682 21/12/2023 13:41:13.183455000 -05:00 TEST_A9 SUCCEEDED 21/12/2023 18:40:43.093346000 EUROPE/LONDON +00 00:00:30.000000
7747616 21/12/2023 13:41:16.729148000 -05:00 TEST_B7 SUCCEEDED 21/12/2023 18:40:46.287305000 EUROPE/LONDON +00 00:00:30.000000
7747618 21/12/2023 13:41:16.729207000 -05:00 TEST_B8 SUCCEEDED 21/12/2023 18:40:46.287313000 EUROPE/LONDON +00 00:00:30.000000
7747684 21/12/2023 13:41:23.423360000 -05:00 TEST_A10 SUCCEEDED 21/12/2023 18:40:53.317328000 EUROPE/LONDON +00 00:00:30.000000My thanks to Tim Hall for his blog post Scheduler (DBMS_SCHEDULER) Enhancements in Oracle Database 12c Release 2 (12.2).
@go-faster.co.uk on BlueSky
Posts
No comments :
Post a Comment