Oracle 基于事件的作业和资源约束

你好，团队，

不会对基于事件的作业强制执行资源约束:https://livesql.oracle.com/apex/livesql/s/lds9uurcvh96teetz7urti48z
19.9上最后两个查询的输出清楚地证实了:

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

根据调度程序资源TEST_SCR施加的限制，我希望在这种情况下仅运行三个作业。
我找不到Oracle文档中记录的位置。看起来像是一个错误，或者很可能是此功能的当前限制。
请告知是否是有意的行为，还是我遗漏了什么。

相同的资源对不同的作业适当地执行约束，例如，我不能同时运行以下声明的三个以上的作业:

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;
/

问候，
米哈伊尔。

如果您查看创建的事件作业的类型，您将看到它们是轻量级的:

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

这些:

* 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

轻量级工作也有一些限制。我不是如果/所有这些都记录在哪里-我找不到确切的列表。

但看起来资源约束是你不能用这些做的事情之一:

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