patternMinor
DBMS_SCHEDULER event schedule jobs not executed when triggered almost at the same time
Viewed 0 times
samethealmosteventtimejobsexecutedwhennotdbms_scheduler
Problem
I moved this question from stackoverflow to here as it was marked on hold as off-topic. On Oracle 10g2 DB I have set up multiple event triggered DMBS_SCHEDULER jobs and all of them are subscribed for the same event queue. The problem is when I call the function that enqueues the message in the advanced queue, therefore triggering the scheduler event all of the subscribed jobs are executed only once.
-
In the loop I am calling procedure that enqueues the messages in the
queue. (I did check the message count in the queue and it matches the
loop count);
-
There are multiple jobs (or a single job - I did test both cases the
result is the same) that have event schedule and are subscribed to
the particular queue;
-
After executing this loop, I do check the message count in the queue
and the instance count of the new jobs in sys.dba_scheduler_job_log.
Result - all the messages in the queue have been processed (meaning
it is empty) and only one instance per each job have been registered
in scheduler_job_log table;
-
The thing is that if I add
Why do all of the other queue messages processed from the queue without running the job? Are there any DBMS_SCHEDULER settings to be adjusted?
-
In the loop I am calling procedure that enqueues the messages in the
queue. (I did check the message count in the queue and it matches the
loop count);
-
There are multiple jobs (or a single job - I did test both cases the
result is the same) that have event schedule and are subscribed to
the particular queue;
-
After executing this loop, I do check the message count in the queue
and the instance count of the new jobs in sys.dba_scheduler_job_log.
Result - all the messages in the queue have been processed (meaning
it is empty) and only one instance per each job have been registered
in scheduler_job_log table;
-
The thing is that if I add
DBMS_LOCK.sleep(1); after at the end of the each iteration in the loop then all of the job instances are registered in the scheduler_log_table fine.Why do all of the other queue messages processed from the queue without running the job? Are there any DBMS_SCHEDULER settings to be adjusted?
Solution
And this documented behaviour can be bypassed by setting this attribute to the job before its enabled:
DBMS_SCHEDULER.set_attribute ('my_job_name', 'parallel_instances', TRUE);
DBMS_SCHEDULER.set_attribute ('my_job_name', 'parallel_instances', TRUE);
Context
StackExchange Database Administrators Q#45719, answer score: 2
Revisions (0)
No revisions yet.