Re: oracle job

"Anton Buijs" <remove_aammbuijs_at_xs4all.nl> wrote in message news:3f329a0e$0$49116$e4fe514c_at_news.xs4all.nl...
>
> Anurag Varma <avdbi_at_hotmail.com> schreef in berichtnieuws
> BMjYa.27986$tt1.13970_at_news02.roc.ny...
> |
> | "Anton Buijs" <remove_aammbuijs_at_xs4all.nl> wrote in message
> news:3f31425f$0$49100$e4fe514c_at_news.xs4all.nl...
> | >
> | > Solaris77 <giofai_at_inwind.it> schreef in berichtnieuws
> | > bgr054$r953n$1_at_ID-203060.news.uni-berlin.de...
> | > | Hi,
> | > | i have 1 oracle job starting every 2 hours.
> | > | My question is:
> | > |
> | > | if the oracle job starts 12:00 and it's running at 14:00 what's
> happening?
> | > |
> | > | The new job start or wait the finish of the first job??
> | > |
> | > | PS Sorry for my english!!
> | > |
> | > | Thank
> | > | Giovanni
> | > |
> | >
> | > Oracle calculates the next job execution time when the job finishes. It
> uses
> | > the function you specified as the "interval" parameter. Example:
> | > - suppose interval is "sysdate +2/24" (means: every 2 hours)
> | > - job started to run at 12:00 (while running you can see that in
> dba_jobs
> | > columns this_date and this_sec or the view dba_jobs_running)
> | > - job finishes at 15:05 (see view dba_jobs columns last_date and
> last_sec)
> | > - Oracle calculates a new scheduled time (dba_jobs columns next_date and
> | > next_sec) using interval, resulting in 17:05 in this example.
> | >
> | >
> | >
> |
> | Anton,
> |
> | I do not think that you are correct.
> |
> | A job which runs for 3 hours, if scheduled to run every 2 hours .... will
> run every (approx.) 3 hours.
> |
> | So
> | - job starts at 12:00 and finishes at 15:05
> | - the next job will start pretty soon after 15:05
> |
> | .. thus if a job takes longer than its interval ... it will behave as a
> continuous job.
> |
> | And since job puts a "job lock", only one of them will be running at one
> time.
> |
> | Try out a simple example using dbms_lock.sleep. Pretty easy to test this.
> |
> | .. so the answer to OP's question is: Only one job will be running at one
> time. However, in his case .. as soon
> | as the job finishes, another will start. So "new job will wait for the one
> currently running to finish".
> |
> |
> | Anurag
> |
>
> I tested it and I must admit: you are totally right. I was so convinced
> about what I described as the behaviour, don't know how that has settled in
> my mind. But now that is updated and commited.
> It is in the doc too. From the Oracle Administrators Guide, Chapter Managing
> Job Queue:
>
> Job Execution Interval:
> The INTERVAL date function is evaluated immediately before a job is
> executed. If
> the job completes successfully, the date calculated from INTERVAL becomes
> the
> new NEXT_DATE. If the INTERVAL date function evaluates to NULL and the job
> completes successfully, the job is deleted from the queue.
>
>

To add to it .. try tracing the job queue process. You'll find that the following happens (in brief):

• Before a job runs, it calculates the next date by evaluating the "interval" that was given.
• When the job finishes, it updates the next_date using the following clause "GREATEST(:next_date, SYSDATE)".
  • Thus if the next date is less than sysdate (meaning that if the job ran longer than its interval) then the next_date is updated to the sysdate. And hence it runs again, immediately after it finishes.
• You will also find that other job processes wake up in the meantime (j001, j002 ...) and wait on a job queue lock, for some time and finish/go back to sleep. (Assuming these other jobs do not have to run other jobs).

hth

Anurag Received on Thu Aug 07 2003 - 16:16:57 CDT