Oracle DBMS_ALERT Waitany Delay

I have a job, which will submit n other jobs. I have setup my pl/sql procedure to register for the n jobs using dbms_alert.register. Each of these jobs will send a dbms_alert.signal. I have issued a commit immediatly after the signal.

Here is the problem, when my program gets to the dbms_alert.waitany, not signal will be returned for 5 seconds (almost excactly every time). The procedure will submit another n jobs, and once again waitany will not recieve any signals for about 5 seconds.

I have done my research and have seen that the default polling inter开发者_JS百科val for dbms_alert.waitany is 5 seconds. This can be changed by using the dbms_alert.set_defaults procedure. I have done that with no effect. I have littered my code with dbms_alert.set_defaults(1) (also tried 600 as well), trying to get the signals less than 5 seconds, but nothing works.

Can anyone help me?

Thanks

What you're looking for is the deprecated initialization parameter _job_queue_interval. It can still be set with

alter system set "_job_queue_interval"=5 scope=spfile;

...and restarting the database.

The DBMS_ALERT.SET_DEFAULTS procedure sets the polling interval in case a polling loop is required (which is very rare). Refer to the Oracle 10g docs about when polling occurs (which is very rare).