DBMS_JOB consistent scheduling

A week or so ago there was a brief discussion WRT to getting DBMS_JOB to successfully start a the same job every 5 minutes without having any schedule creep. I don't recall anyone posting a possible solution to this situation. Since I recently wanted to have such functionality on a couple of my instances, I have been working towards a solution.

I'm not claiming my approach is the best, it does seem to be working. I have a PL/SQL task that I want to start running twelve times an hour; 00, 05, 10, ..., 50, 55 minutes after the hour. This procedure takes 1+ minutes to 3+ minutes to complete after being invoked.

At the start of the PL/SQL procedure, a couple of "preparatory" tasks are completed. 1) DBMS_JOB.BROKEN is called to "break" the job. 2) a record within the database is updated with the "next" time this job is to be run. This "next" time is appropriately rounded to ALWAYS be an integer multiple(0-11) of FIVE minutes.

As the last task of the procedure, a DBMS_JOB.NEXT_DATE call is made using the value that was stored in the database at the start of the procedure. All of the above are NECESSARY but are not sufficient to achive the desired scheduling.

A key to accomplishing this scheduling goal seems to be to establish a sub-minute job_queue_interval in the initSID.ora file. After I went from job_queue_interval=60 to job_queue_interval=20, I started to get the scheduling desired!

HTH & YMMV!

-- 
Charlie Mengler                               Maintenance Warehouse  
charliem_at_mwh.com                              5505 Morehouse Drive   
858-552-6229                                  San Diego, CA 92121    
Always be sincere about your enthusiasm, whether you mean it or not.