oracle 12c - delay before run of event-based scheduler job

Viewed 40

Environment: Oracle 12.2 64-bit under Linux

parameters:

job_queue_processes = 4000

aq_tm_processes = 1

Can you please explain me – why there is a delay between event processing in event-based job?

I fixed a 15-20 seconds delay between job calls.

Test process is simpliest – it places a message into the queue.

And job begins to run only after delay mentioned above.

I expected a milliseconds for such a simpliest scenario…

Test case:

create or replace type t_test_type
as object(id number, msg varchar2(50));
/
create or replace procedure test_q_proc
(
 p_job_name in varchar2,
 p_event_message in t_test_type
)
as
begin 
  dbms_output.put_line('msg= '||p_event_message.msg);
end;
/

begin 
  dbms_aqadm.create_queue_table('test_q_table','t_test_type',multiple_consumers => true);
  dbms_aqadm.create_queue('test_q','test_q_table');
  dbms_aqadm.start_queue('test_q');
  dbms_scheduler.create_program('test_q_prog','stored_procedure','test_q_proc',2, false);
  dbms_scheduler.define_metadata_argument('test_q_prog','job_name',1,'p_job_name');
  dbms_scheduler.define_metadata_argument('test_q_prog','event_message',2,'p_event_message');
  dbms_scheduler.enable('test_q_prog');
  dbms_scheduler.create_job('test_q_job','test_q_prog',
                            event_condition=> 'tab.user_data is not null',
                            queue_spec=>'test_q',enabled=> true);
 end;         
 /
 declare
  opts dbms_aq.enqueue_options_t;
  props dbms_aq.message_properties_t;
  pl t_test_type := t_test_type(1, 'test');
  msg_id raw(16);
 begin 
   dbms_aq.enqueue('test_q',opts, props, pl, msg_id);
   commit;
 end;                  
 / 

Any help would be very appreciated.

TIA, Andrew.

1 Answers

I observed the same phenomenon. Though several things can cause it (see links below) i suggest setting the parallel instances to true - it did the magic for me.

DBMS_SCHEDULER.SET_ATTRIBUTE(name => <your_job_name>, attribute => 'parallel_instances', value => TRUE);

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/scheduling-jobs-with-oracle-scheduler.html#GUID-EDF02141-2BC4-47F0-8142-D3868B6466DF

Could u pls try it and gives us a feedback how it (not) worked?

Thx, Marcello

http://www.dba-oracle.com/t_job_queue_interval.htm

https://community.oracle.com/tech/apps-infra/discussion/929112/difference-between-req-start-date-and-acutal-start-date

https://community.oracle.com/tech/developers/discussion/4151349/oracle-scheduler-job-req-start-date-differs-from-actual-start-date

Related