Hello Team,
Resource constraints are not enforced for event-based jobs:
https://livesql.oracle.com/apex/livesql/s/lds9uurcvh96teetz7urti48z The output of last two queries on 19.9 clearly confirms that:
SQL> select sid, action from v$session where module='DBMS_SCHEDULER';
SID ACTION
---------- ------------------------------
136 TEST_SCJ
149 TEST_SCJ
264 TEST_SCJ
272 TEST_SCJ
273 TEST_SCJ
380 TEST_SCJ
396 TEST_SCJ
629 TEST_SCJ
747 TEST_SCJ
753 TEST_SCJ
10 rows selected.
SQL> select status, resource_units, units_used, jobs_running_count from user_scheduler_resources where resource_name='TEST_SCR';
STATUS RESOURCE_UNITS UNITS_USED JOBS_RUNNING_COUNT
------------------- -------------- ---------- ------------------
ENFORCE_CONSTRAINTS 3 10 10
I expect to have only three jobs running in this scenario, based on the restriction imposed by the scheduler resource TEST_SCR.
I cannot find where it is documented in the Oracle documentation. Looks like a bug, or most likely the current limitation of this functionality.
Please advise whether it is intended behavior, or I am missing something.
The same resource enforces constraints properly for different jobs, e.g. I cannot run more than three jobs declared as follows simultaneously:
begin
for i in 1..5
loop
dbms_scheduler.create_job(job_name=> 'test_scj'||i, job_type=> 'plsql_block', job_action=>'dbms_session.sleep(15);', enabled=>false);
dbms_scheduler.set_resource_constraint('test_scj'||i, 'test_scr', 1);
dbms_scheduler.enable('test_scj'||i);
end loop;
end;
/
Regards,
Mikhail.
If you look at the type of the event jobs created, you'll see they are LIGHTWEIGHT:
declare
enq_opts dbms_aq.enqueue_options_t;
msg_props dbms_aq.message_properties_t;
msg_id raw(16);
begin
for i in 1..10
loop
dbms_aq.enqueue('test_q', enq_opts, msg_props, msg_t(i), msg_id);
end loop;
commit;
end;
/
select job_name, job_style
from dba_scheduler_running_jobs;
JOB_NAME JOB_STYLE
TEST_SCJ LIGHTWEIGHT
TEST_SCJ LIGHTWEIGHT
TEST_SCJ LIGHTWEIGHT
TEST_SCJ LIGHTWEIGHT
TEST_SCJ LIGHTWEIGHT
TEST_SCJ LIGHTWEIGHT
TEST_SCJ LIGHTWEIGHT
TEST_SCJ LIGHTWEIGHT
TEST_SCJ LIGHTWEIGHT
TEST_SCJ LIGHTWEIGHTThese:
* Unlike regular jobs, they are not schema objects.
* They have significantly better create and drop times over regular jobs because they do not have the overhead of creating a schema object.
* They have lower average session create time than regular jobs.
* They have a small footprint on disk for job metadata and run-time data. https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/oracle-scheduler-concepts.html#GUID-4529B212-84F4-4C93-9D6C-00B7917D1B42 There are also some restrictions for lightweight jobs. I'm not if/where these are all documented - I can't find a definitive list.
But it looks like resource constraints are one of the things you can't do with these:
begin
dbms_scheduler.create_program(
'test_scp',
'stored_procedure',
'p',
0,
true);
dbms_scheduler.create_resource('test_scr', 3);
end;
/
begin
dbms_scheduler.create_job(
job_name => 'test_scj',
program_name => 'test_scp',
enabled => false,
job_style => 'LIGHTWEIGHT'
);
dbms_scheduler.set_resource_constraint('test_scj', 'test_scr', 1);
end;
/
ORA-27494: operation not permitted on lightweight and in-memory jobs