3

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 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?

1
  • I'd try the forums at Oracle Technet -- much more experience of this sort of thing there. Jul 8, 2013 at 23:49

2 Answers 2

1

This is a documented behavior so "it's not a bug, it's a feature".

http://docs.oracle.com/cd/B19306_01/server.102/b14231/scheduse.htm#CHDIAJEB says:

events that occur while the job is already running are ignored; the event gets consumed, but does not trigger another run of the job.

2
  • 1
    Thank you for pointing to this note. I did not somehow notice it when reading docs. As these event triggered jobs are not reliable in 10g and there are no company plans to migrate to 11g, then I had to think of some workaround. I changed these jobs to execute with a time period schedule (every minute), and they are calling and extra procedure that is checking and dequeueing these event messages and then processing business logic.
    – skujins
    Jul 22, 2013 at 16:06
  • Hmm "not reliable". Isn't it possible to have a plain and simple stored procedure (not a job) that is notified on each enqueue? I never tested this, so I cannot vouch, but I would say it is a traditional way and would be reliable (that is, it wouldn't silently dequeue events).
    – kubanczyk
    Jul 24, 2013 at 20:44
2

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

1
  • 2
    Unfortunately this attribute is only available from Oracle 11GR2.
    – skujins
    Feb 24, 2014 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.