Database Software

We are using a custom application to submit jobs using dbms_scheduler. A client found that, when configuring many infinite jobs, each using dbms_alert, it took a very long time for all jobs to reach the state RUNNING. I have been able to reproduce the situation using the following code:

create or replace procedure dummy_proc ( i_name IN VARCHAR2 )
is
begin
dbms_alert.register( i_name );

dbms_lock.sleep(60);

dbms_alert.remove( i_name );
end;
/

begin
for i in 1 .. 18
loop
dbms_scheduler.create_job( job_name => 'name_' || to_char(i)
, job_type => 'PLSQL_BLOCK'
, job_action => 'BEGIN dummy_proc(' || to_char(i) || '); END;'
, number_of_arguments => 0
, start_date => SYSTIMESTAMP
, repeat_interval => NULL
, end_date => NULL
, job_class => 'DEFAULT_JOB_CLASS'
, enabled => TRUE
, auto_drop => FALSE
, comments => 'description'
);
end loop;
end;
/

In our currrent setup, the query
select state, count(*) from user_scheduler_jobs group by state order by state
showed 9 jobs as RUNNING, and 9 as SCHEDULED.

- It took tens of seconds for additional jobs (typically 2 at a time) to get RUNNING. The clients reports 3 minutes for approx. 35 jobs.
- When a RUNNING job finished its sleep, more jobs became RUNNING.
- Removing the the dbms_alert line and re-executing, showed them all as RUNNING immediately.
- Adding commit statements did not change anything.
- The database is 10.2.0.3.0, running on Windows.

Even when dbms_alert would be the cause of the delay, I would expect that the job state would be RUNNING by the time dbms_alert would come in to play.

I searched quite a bit and found no similar problems described. Hopefully someone here knows more ...

[Edit 20081016]
As it turns out, this seems to be a problem limited to a Windows installation; when the above code was run on an AIX installation, all jobs became RUNNING immediately. Also, on the Windows platform, when the statement "dbms_lock.sleep(1)" was inserted prior to the "dbms_alert.register" call, all jobs appeared in user_scheduler_jobs as RUNNING immediately as well.

I have the impression that the sequence hits a limit of some OS resource. I suspect that dbms_scheduler uses dbms_alert as well and that, simply said, an initial block of jobs is entered as RUNNING, which are then blocking the specific resource: from a trace I can see a Select on dbms_alert_info, which has a wait of approx. 4.5 seconds on Windows, and 3 seconds on AIX.

The remaining jobs cannot run because dmbs_scheduler is unable to allocate the resource, and are therefore entered as "Scheduled". After that, the job coordinator periodically (typically every 16.5 seconds) checks whether additional jobs can be activated. This happens in smaller chunks (typically 2 jobs at a time) than the initial block of jobs.

Still, I would like to find out what would be a generic way of determining whether there is room for a new job to be entered. This could then be used in our custom application, before calling dbms_scheduler.create_job. To sleep 1 second works for now, but it does not really guarantee success for the future ...

Edited by: user10419852 on 16-okt-2008 1:35