Skip to Main Content
  • Questions
  • Event Based Jobs and Resource Constraints

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Mikhail.

Asked: February 16, 2021 - 2:24 pm UTC

Last updated: February 17, 2021 - 2:55 pm UTC

Version: 19.9

Viewed 100+ times

You Asked

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.

with LiveSQL Test Case:

and we said...

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    LIGHTWEIGHT


These:

* 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

Rating

  (1 rating)

Comments

A reader, February 16, 2021 - 5:58 pm UTC

> If you look at the type of the event jobs created, you'll see they are LIGHTWEIGHT:
> But it looks like resource constraints are one of the things you can't do with these

I can conclude it is either bug in the documentation, or in the implementation.
With all due respect, your answer does not explain where it is.

The USER_SCHEDULER_RESOURCES query shows that the resources are counted properly:
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

Although the resource allows only 3 units, 10 units are used.
Following your logic, I would expect my lightweight jobs to be not counted at all. It is not the case here as the output above demonstrates. Definitely, the jobs are counted properly, but not restricted from running.

Chris Saxon
February 17, 2021 - 2:55 pm UTC

I'm not sure where the issue is either!

Following your logic, I would expect my lightweight jobs to be not counted at all

I'm not sure that necessarily follows; just that you can't restrict these using resource constraints.

Either way, it looks like you're out of luck if you need to limit the number of event-based jobs that can run at the same time.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database