DBMS_SCHEDULER event schedule jobs not executed when triggered almost at the same time

jobsoracle

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?

Best Answer

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.

Related Solutions

Disabling DBMS_SCHEDULER queue

I can't test it right now but I think you could do this with open/close scheduler window:

first define a window with DBMS_SCHEDULER.create_window. This window could have a 24 hour duration and repeat every day so that it is always open.
modify your job submission so that they run under this window (parameter schedule_name of the DBMS_SCHEDULER.create_job procedure).
when you need to enter maintenance and/or release resource, use DBMS_SCHEDULER.close_window. When your maintenance is done, use DBMS_SCHEDULER.open_window.

The jobs submitted while the window is closed should be queued and run later when the window is opened.

Why does dbms_scheduler max_run_duration not raise event JOB_OVER_MAX_DUR

When adding multiple attributes, try:

DBMS_SCHEDULER.set_attribute(
    name      => 'first_job',
    attribute => 'raise_events',
    value     => DBMS_SCHEDULER.job_succeeded + DBMS_SCHEDULER.some_attribute);

The valid values for event types (11gr2) is here. And according to docs, job_over_max_dur is enabled already for 11gr2.

Now, for 10g or 11gr1, I don't see job_over_max_dur in the list of event types raised by the scheduler. But you mentioned in earlier post that you will very soon migrate to 11gr2, so I assume you have a test box setup already that you can test this with 11gr2. If you really want to go down the rabbit hole with AQ, you can do more custom events, but it would probably be much easier to wait a bit until you're on 11gr2.

Edit

To add more info, first some basic scheduler stuff you can view to see whats running (or scheduled, or stalled, or whatever state):

select * from dba_scheduler_jobs
where owner = 'SOME_OWNER'
and job_name = 'SOME_JOB';

You can see if max_run_duration column is set and also the "raise_events" column will show a list of event types that are to be raised by each job. You should see "JOB_OVER_MAX_DUR" type here.

If you want to test if your db environment is handling the JOB_OVER_MAX_DUR properly, then you can setup a quick test:

BEGIN
    -- setup simple scheduler job
   --DBMS_SCHEDULER.DROP_JOB('EVENT_RAISING_JOB',false);
   DBMS_SCHEDULER.create_job (
      job_name        => 'EVENT_RAISING_JOB',
      job_type        => 'PLSQL_BLOCK',
      job_action      => 'BEGIN
                            dbms_lock.sleep(70);
                          END;',
      start_date      => SYSTIMESTAMP,
      end_date        => SYSTIMESTAMP + 2, -- 2 day
      repeat_interval => 'freq=daily; byhour=1',
      enabled         => TRUE);

    DBMS_SCHEDULER.set_attribute(name=>'event_raising_job', attribute=>'max_run_duration', value=>interval '60' second);

   DBMS_SCHEDULER.set_attribute(name => 'event_raising_job', attribute => 'raise_events',value => DBMS_SCHEDULER.job_all_events );

END;

-- setup email notification
exec dbms_scheduler.add_job_email_notification( job_name=>'EVENT_RAISING_JOB',recipients=>'someuser@somedomain.com', events=>'job_all_events');

-- launch job
exec dbms_scheduler.run_job('EVENT_RAISING_JOB',false);

You should get 3 emails that this job: STARTED, SUCCEEDED, and JOB_OVER_MAX_DUR

Best Answer

Related Solutions

Disabling DBMS_SCHEDULER queue

Why does dbms_scheduler max_run_duration not raise event JOB_OVER_MAX_DUR

Related Question