FAQ
Several years (not sure how many) and Oracle versions ago, I seem to
recall testing how DBMS_JOB sets the next_date to run a job. It seemed
to use the INTERVAL setting for the job with SYSDATE at the time the job
started to calculate next_date. However, if the job ran past what that
calculation computed, it actually used the INTERVAL setting using the
SYSDATE of the time the job ended to calculate next_date. So, if a job
was set to run every hour, and a run lasted 1.5 hours, the next run
would begin one hour after the last one finished. If it ran for less
than one hour, the next run would begin one hour from the start of the
previous run.


That is not the behavior we saw with a long running job recently, so I
tested this again. On both a 9i and 10g database, if a DBMS_JOB job
runs past the time the INTERVAL is set for, Oracle seems to place the
SYSDATE at job completion into next_date. So, if a job runs longer than
the INTERVAL is set for, it will start back up again as soon as it
finishes.



I have looked at Oracle documentation, and it says that the INTERVAL is
evaluated before the job is executed. It says it must be a time in the
future. This date becomes the new NEXT_DATE after the job executes
successfully. I can't find anything that says what Oracle does if the
calculated date is not in the future at that point in time.



Is there a parameter setting that could affect how NEXT_DATE is
calculated? I know scheduler has more flexibility, but we have many
DBMS_JOB jobs, and I want to be able to say with certainty what will
happen if a job runs long.



Thanks,



John Reimer

Search Discussions

  • Joel Patterson at Jan 6, 2012 at 6:49 pm
    Not sure if this is what you are looking for, but to stop the start time slippage you can use the truncate command.

    By Truncating, you can reset the start time to the same time each run without slipping.

    Here is an example


    select to_char(sysdate, 'YYYY-Mon-DD HH24:MI:SS') now FROM dual;
    prompt
    prompt trunc(sysdate + 1/24,'HH') (top of next hour)
    prompt trunc(sysdate) + 11/24 (11 hours from now).
    prompt trunc(sysdate + 5) + 2/24 ( 5 days from today at 02:00am)
    prompt

    execute dbms_job.interval(&jobnum, '&interval_string');

    execute dbms_job.next_date(&job_num,&next_date);



    Joel Patterson
    Database Administrator
    904 727-2546

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Reimer, John J.
    Sent: Wednesday, January 04, 2012 2:17 PM
    To: Oracle-L
    Subject: DBMS_JOB next_date calculation

    Several years (not sure how many) and Oracle versions ago, I seem to
    recall testing how DBMS_JOB sets the next_date to run a job. It seemed
    to use the INTERVAL setting for the job with SYSDATE at the time the job
    started to calculate next_date. However, if the job ran past what that
    calculation computed, it actually used the INTERVAL setting using the
    SYSDATE of the time the job ended to calculate next_date. So, if a job
    was set to run every hour, and a run lasted 1.5 hours, the next run
    would begin one hour after the last one finished. If it ran for less
    than one hour, the next run would begin one hour from the start of the
    previous run.


    That is not the behavior we saw with a long running job recently, so I
    tested this again. On both a 9i and 10g database, if a DBMS_JOB job
    runs past the time the INTERVAL is set for, Oracle seems to place the
    SYSDATE at job completion into next_date. So, if a job runs longer than
    the INTERVAL is set for, it will start back up again as soon as it
    finishes.



    I have looked at Oracle documentation, and it says that the INTERVAL is
    evaluated before the job is executed. It says it must be a time in the
    future. This date becomes the new NEXT_DATE after the job executes
    successfully. I can't find anything that says what Oracle does if the
    calculated date is not in the future at that point in time.



    Is there a parameter setting that could affect how NEXT_DATE is
    calculated? I know scheduler has more flexibility, but we have many
    DBMS_JOB jobs, and I want to be able to say with certainty what will
    happen if a job runs long.



    Thanks,



    John Reimer








    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJan 4, '12 at 7:17p
activeJan 6, '12 at 6:49p
posts2
users2
websiteoracle.com

2 users in discussion

Reimer, John J.: 1 post Joel Patterson: 1 post

People

Translate

site design / logo © 2022 Grokbase