DBMS_JOB: Next Date changes
Richard de Fonseka
execute
dbms_job.isubmit(300,'apl.update_msf600_comparison();',trunc(sysdate)
+ 23/24,'sysdate+1');
When I check ALL_JOBS after the job executes, the Last Date changes
(e.g. 11:02pm) and the Next Date changes as well.
I have tried to change the Next Date using DBMS_JOB.CHANGE at the end
of the procedure being executed.
How do I keep the start date/time to exactly 11:00pm.
Thanks,
Richard
Connor McDonald
Use trunc in your next definition.
HTH
--
===========================================
Connor McDonald
"These views mine, no-one elses etc etc"
connor_...@yahoo.com
"Some days you're the pigeon, and some days you're the statue."
Doug Cowles
daily basis,
it doesn't execute when it's suppossed to (10:00pm). The job takes less
than a
second to run, yet it always exeutes at 10:07pm.
Have a look at these values for the job:
LAST_DATE = 31-JAN-00
LAST_SEC= 22:07:53
NEXT_DATE = 01-FEB-00
NEXT_SEC=22:00:00
Yesterday, the values reported were the same (although one day back).
Why is it always 7 minutes late?
- Dc.
Andy Hardy
<dco...@us.ibm.com> writes
>I do have a trunc in my next_date, and although the job doesn't slip on a
>daily basis,
>it doesn't execute when it's suppossed to (10:00pm). The job takes less
>than a
>second to run, yet it always exeutes at 10:07pm.
>Have a look at these values for the job:
>LAST_DATE = 31-JAN-00
>LAST_SEC= 22:07:53
>NEXT_DATE = 01-FEB-00
>NEXT_SEC=22:00:00
>
>Yesterday, the values reported were the same (although one day back).
>Why is it always 7 minutes late?
What is your JOB_QUEUE_INTERVAL set to and how many JOB_QUEUE_PROCESSES
do you have compared to the number of jobs?
Andy
--
Andy Hardy. PGP key available on request
===============================================================
Thomas Kyte
(if that email address didn't require changing)
On Tue, 01 Feb 2000 11:46:03 -0500, you wrote:
>I do have a trunc in my next_date, and although the job doesn't slip on a
>daily basis,
>it doesn't execute when it's suppossed to (10:00pm). The job takes less
>than a
>second to run, yet it always exeutes at 10:07pm.
>Have a look at these values for the job:
>LAST_DATE = 31-JAN-00
>LAST_SEC= 22:07:53
>NEXT_DATE = 01-FEB-00
>NEXT_SEC=22:00:00
>
>Yesterday, the values reported were the same (although one day back).
>Why is it always 7 minutes late?
>- Dc.
>
what is your job_queue_interval?
what is your job_queue_processes?
if you interval is too large -- jobs won't run near their scheduled time. for
example, say the interval was 10 minutes. If you started the database at
11:57am, then jobs would be processed every 10 minutes from there (10:07, 10:17,
10:27 and so on).
if you have too few processes and a couple of jobs scheduled for the same time -
you'll get a backlog as well. only as many jobs as you have queue processes can
be running simultaneously. If you have 1 process and 2 jobs scheduled for 10 --
and the first job takes 7 minutes, this can happen as well.
>
>Connor McDonald wrote:
>
>> Richard de Fonseka wrote:
>> >
>> > I can submit a job to run at exactly 11:00pm:
>> >
>> > execute
>> > dbms_job.isubmit(300,'apl.update_msf600_comparison();',trunc(sysdate)
>> > + 23/24,'sysdate+1');
>> >
>> > When I check ALL_JOBS after the job executes, the Last Date changes
>> > (e.g. 11:02pm) and the Next Date changes as well.
>> >
>> > I have tried to change the Next Date using DBMS_JOB.CHANGE at the end
>> > of the procedure being executed.
>> >
>> > How do I keep the start date/time to exactly 11:00pm.
>> >
>> > Thanks,
>> > Richard
>>
>> Use trunc in your next definition.
>>
>> HTH
>> --
>> ===========================================
>> Connor McDonald
>> "These views mine, no-one elses etc etc"
>> connor_...@yahoo.com
>>
>> "Some days you're the pigeon, and some days you're the statue."
>
>
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tk...@us.oracle.com
Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Frederic Bidon
DBMS_JOB uses jobs queues handled by snp process (started according to the
job_queue_processes parameter).
Each snp session can handle one job at at time.
When a snp is idle it scans jobs to be submited every job_queue_interval
(typically 60 seconds).
If no snp is idle, then the next job has to wait that one snp queue is free
to handle it.
The problem is that the next occurence of the job will be calculated
according to the actual date of execution, hence this "drift" of job
execution time.
If someone smarter has found a solution, I would be also interested.
The only way I have found so far is to reschedule on a regular basis my jobs
(from a script) to minimize the drift and harness the problem of automatic
rescheduling when a job fails.
To minimize the pb, you should start several snp processes and set the
job_queue_interval to a smaller value.
Reagrds,
Frederic.
-----Original Message-----
From: Richard de Fonseka [mailto:rdefo...@scnet.com.au]
Sent: 25. januar 2000 18:35
To: comp.databases...@list.deja.com
Subject: DBMS_JOB: Next Date changes
Message from the Deja.com forum:
comp.databases.oracle.server
Your subscription is set to individual email delivery
I can submit a job to run at exactly 11:00pm:
execute
dbms_job.isubmit(300,'apl.update_msf600_comparison();',trunc(sysdate)
+ 23/24,'sysdate+1');
When I check ALL_JOBS after the job executes, the Last Date changes
(e.g. 11:02pm) and the Next Date changes as well.
I have tried to change the Next Date using DBMS_JOB.CHANGE at the end
of the procedure being executed.
How do I keep the start date/time to exactly 11:00pm.
Thanks,
Richard
_____________________________________________________________
Deja.com: Before you buy.
http://www.deja.com/
* To modify or remove your subscription, go to
http://www.deja.com/edit_sub.xp?group=comp.databases.oracle.server
* Read this thread at
http://www.deja.com/thread/%3C388ddd2a.3096492%40news.iinet.net.au%3E
Sent via Deja.com http://www.deja.com/
Before you buy.
Doug Cowles
what units that is), I moved it to 10 and am awaiting results tonight.
processes is 10 as well. Only 2 jobs, spaced 3 hours apart.
- Dc.
Thomas Kyte wrote:
> >> > I can submit a job to run at exactly 11:00pm:
> >> >
> >> > execute
> >> > dbms_job.isubmit(300,'apl.update_msf600_comparison();',trunc(sysdate)
> >> > + 23/24,'sysdate+1');
> >> >
> >> > When I check ALL_JOBS after the job executes, the Last Date changes
> >> > (e.g. 11:02pm) and the Next Date changes as well.
> >> >
> >> > I have tried to change the Next Date using DBMS_JOB.CHANGE at the end
> >> > of the procedure being executed.
> >> >
> >> > How do I keep the start date/time to exactly 11:00pm.
> >> >
> >> > Thanks,
> >> > Richard
> >>
Andy Hardy
<dco...@us.ibm.com> writes
>Thanks everyone.. job_queue_interval was set to 600 (although I'm not sure
>about
>what units that is),
It's in seconds.
Kevin A Lewis
the snp process picks up and launches the activity. What you can do is
ensure the problem gets no worse by setting the DBMS_JOB interval to for
example which ensures a daily job to run every half past ten in the evening.
/*1:Days*/ TO_DATE(TO_CHAR(TRUNC(SYSDATE)+1,'YY-MON-DD')
||':20:30:00','YY-MON-DD:HH24:MI:SS')
Okay so you cannot remove the gap between this time and when the system
actually gets it going. You can ensure that a series does not get later and
later, day by day.
As others have suggested you can reduce the SNP job process interval or add
more processes to speed up the processes of job launch identification and
action.
Regards
--
Kevin A Lewis (BOCM PAULS LTD) - Animal Feed Manufacturer - Ipswich United
Kingdom)
<Kevin_...@Hotmail.com>
The views expressed herein by the author of this document
are not necessarily those of BOCM PAULS Ltd.
Frederic Bidon <f...@mobilix.dk> wrote in message
news:2D3005375CAED31199D...@ms02.mobilix.dk...
> I can submit a job to run at exactly 11:00pm:
>
> execute
> dbms_job.isubmit(300,'apl.update_msf600_comparison();',trunc(sysdate)
> + 23/24,'sysdate+1');
>
> When I check ALL_JOBS after the job executes, the Last Date changes
> (e.g. 11:02pm) and the Next Date changes as well.
>
> I have tried to change the Next Date using DBMS_JOB.CHANGE at the end
> of the procedure being executed.
>
> How do I keep the start date/time to exactly 11:00pm.
>
> Thanks,
> Richard
>
>
>
avinna...@gmail.com
A. Mehoela
> I also want to know if there is any query that can exactly show me the time frame in which the job was broken and had stop executing. PS: The job seems to be running from 8th Jan.
>