Scheduler and Interval Questions

Hello Group,

I have a couple of questions surrounding the Oracle8i job scheduler. First, here's some background info about my problem. I have a very long running pl/sql procedure that needs to execute on a regular basis for the next 4 days. The problem with this procedure is that it performs a complicated select with several outer joins and then has to delete at least 50,000 records returned by the query before committing, and this all has to happen in an EXTREMELY busy production environment. With the long running process and the added mystery of how busy the DB is at different times of the day, it's hard to say how long the process will take to run any given time (last time I ran it manually and it took 4 hours and 5 minutes to complete). Because of the nature of the process, I don't want more than one instance of it running, but there is a serious time constraint as well - 2.7 million records need to be deleted in the next 4 days!

The Oracle8i reference seems to be stating that the NEXT_DATE on a scheduled job is set from the BEGINNING of the execution time (as opposed to when the process completes), so if the process begins at 1PM, the NEXT_DATE is set to 5PM, but I can't find anything about how it is set if the process hasn't completed by 5PM. My assumption is that another 4 hours would be added to the first NEXT_DATE calculation, thus setting the NEXT_DATE to 9PM. If this is the case, then my job (which may only take 5 minutes more than the 4 hour interval) won't run again for 8 hours after the first time it began running, which is a problem with my 4 day time constraint.

So here's what it all boils down to...If I just assume the job will take 4 hours to run on average, and I use the job scheduler to schedule it for every 4 hours, then (based on my assumption) it could potentially be 8 hours before it begins running again. In this case, wouldn't it be wiser to schedule it for a short period of time, like 1 hour, so the next start time is never too long from the last completion time? If my assumption about how the scheduler works is incorrect, then I need clarification about that and suggestions on how to best schedule this routine.

THANKS!

-JMack

http://asktom.oracle.com/pls/ask/f?p...0399314507501,

Axr2,

Thanks for the info, but it didn't really answer my questions. I can't set up my job to run at specific times of day because I never know how long it will take to run. I need to know what happens to the next execution time (NEXT_DATE) when a job runs longer than its prescribed interval time, and how to best schedule my long running job so that it runs as frequently as possible.

Thanks,
JMack

Maybe DBMS_JOB is not the answer here, and you just need to loop the procedure with a chosen interval between end and start ... DBLS_LOCK.SLEEP() would do this for you.

Having said that, the NEXT_DATE is calculated at the end of the job execution. If you wanted the job to start on the next hour after it last finished, your next_date expression would be ...

Trunc(sysdate,'HH')+(1/24)

Does that help?

Quote:

---

Originally posted by slimdave
Having said that, the NEXT_DATE is calculated at the end of the job execution. If you wanted the job to start on the next hour after it last finished, your next_date expression would be ...

Trunc(sysdate,'HH')+(1/24)

---

Not quite so. The NEXT_DATE is calculated *at the very beginning of the job execution*, not at the end. However, the catch is, this newly calculated time is not updated (in fact, this update is merely not commited) in the job queue untill the currently running process finished. However once the current job finished (implicit commit performed regardles whether the job finished successfuly or with error), the vaalue of the next_date that was calculated when the job was started becomes wisible to the outsside world.

Let's take an example. The interval for the job is set to "Trunc(sysdate,'HH')+(1/24)", meaning that it should run at every full hour. Lets say it is executed at 8:00. As soon as the job starts to execute, the NEXT_DATE is calculated to 9:00. The job queue tables is updated immediately with this value, however if you query DBA_JOBS while it is running, you won't be able to see this, because the update is not yet commited. Let's say that job runs for 55 minutes and finishes at 8:55. As soon as it finishes, the NEXT_DATE=9:00 becomes visible. So at approximately 9:00 the job is started again and the NEXT_DATE is recalculated to 10:00. Now let's say that this time job runs for 65 minutes. At 10:00 the process that checks job que table can't see that the next_date for this job is allready set to this new value. But at 10:05, when it finishes, you are able to see that the NEXT_DATE of this job is set to 10:00,and so does the process that checks the job queue table. The first time it checks the queue after job has finished it finds that this job was due to start 5 minutes ago and will run it immediately.

If you realy wanted to schedule the job to restart an hour after it has finished, you should reset the NEXT_DATE from within the job's body code (by finding the job_number of the current job and then using DBM_JOB.NEXT_DATE() procedure). Again, you can't set something like this from the outside when you define a job, it must be done dinamicaly from within the job itself.

Thanks jmodic. That was very helpful.