Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_JOB Scheduler Issue (Oracle Database 10.2.0.3.0. AIX version 5.3)
DBMS_JOB Scheduler Issue [message #328979] Mon, 23 June 2008 11:05 Go to next message
wtolentino
Messages: 196
Registered: March 2005
Senior Member
recently our dbms_job in APEX is behaving erratically. it appears to be that the schedule job does not start at the specified time. e.g. job start to run daily at 6:30pm but somehow it started at 7:05pm. what could be possible cause of the issue?

thanks,
warren
Re: DBMS_JOB Scheduler Issue [message #328982 is a reply to message #328979] Mon, 23 June 2008 11:32 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
It is obviously an Oracle bug.
Re: DBMS_JOB Scheduler Issue [message #328987 is a reply to message #328979] Mon, 23 June 2008 12:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since you provided no information at all, all we can do is guess.
My guess is that you are using an interval, which causes the starttime to shift. See this page for an example.
Re: DBMS_JOB Scheduler Issue [message #328990 is a reply to message #328979] Mon, 23 June 2008 12:11 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
As I recall, DBMS_JOB does not say when a job will start. It says that a job will start on or after the next start time. There is no guarentee that a job will starte exactly at the time stated. Thus the behavior you are seeing may be 100% correct and normal. I have seen jobs wait up to 40 minutes after their start time for various reasons.

There are several things that can control when a job will start. I have not looked closely at DBMS_JOB in recent releases so I am sure things have changed, but I recall that at least with older versions of the RDBMS, one thing which affected starting of jobs, was the number of job_queue_processes that was made available. Assuming the job_scheduler still uses these, then the RDBMS can only have as many simultaneous jobs running as there are available processess. If you have say 1 available process thread for running dbms_jobs jobs, and two jobs are scheduled to run @5am, then one would start @5am, and the second would have to wait till the first was done before it could run because there is no process thread for it to use. Complicating things is that many new features use these threads themselves so you need to account for use of these new features in the overal total number if you are using any of them.

So, in the past I have seen what you describe as simply a result of the job having to wait for a free processing thread to open up before it could start, because there were not enough to go around.

In the past there was also a setting for "polling" of job readiness (eg. how often does the system look to see if there is a job to run). My memory fails me somewhat, but I think this polling worked in such a way as to delay job starts, up to 2x the polling time (which I think defaulted to 10 minutes). So your delay might be do to some parameter setting for job processes. This behaviour as I recall varied by O/S. Additionally, I think new releases of Oracle no longer work this way and this parameter may have been obsoleted so you will have to look into it.

But the point is DBMS_JOB scheduling follows various rules. What you are seeing may be perfectly fine for the rules.

Maybe you could read about how DBMS_JOB works for your version of Oracle and then determine if there is a resource lacking or a parameter setting you need to tweek. You might wish to ask you O/S experts, and your RDBMS experts what changed recently if this is a recent behaviour.

Good luck, Kevin
Re: DBMS_JOB Scheduler Issue [message #329025 is a reply to message #328987] Mon, 23 June 2008 14:10 Go to previous messageGo to next message
wtolentino
Messages: 196
Registered: March 2005
Senior Member
sorry i'm not sure what other details that needed to be posted. basically the job was been running correctly at the specified time on a daily basis for more that a year it started at "26-MAY-08 06.30.00". there has been no changes to the job since it was initially submitted. however for the past two weeks we noticed it occured 7 times that the job did not start in the time it was specified to run.

e.g.
  JOB_NAME             REQ_START_DATE                      ACTUAL_START_DATE
  -------------------- ----------------------------------- -----------------------------------
  BULKLOAD_PRE_PACK    20-JUN-08 06.30.00.000000 PM -04:00 20-JUN-08 06.58.50.177346 PM -04:00
  BULKLOAD_PRE_PACK    17-JUN-08 06.30.00.000000 PM -04:00 17-JUN-08 06.53.26.442425 PM -04:00
  BULKLOAD_PRE_PACK    11-JUN-08 06.30.00.000000 PM -04:00 11-JUN-08 07.05.53.251258 PM -04:00


thanks,
warren
Re: DBMS_JOB Scheduler Issue [message #329027 is a reply to message #328990] Mon, 23 June 2008 14:19 Go to previous message
wtolentino
Messages: 196
Registered: March 2005
Senior Member
thank you kevin for your explanation. that was a very good advise. actually the problem we has is timing issue because the DBMS_JOB when run the job will create a file and at the same time it is read by another process (perl scripting) that runs on a later time. my guess is that to solve the issue we have to increase the time gap between the two process.
Previous Topic: SORTING WITH ALPHA-NUMERIC
Next Topic: CAN I use two cursors like show below
Goto Forum:
  


Current Time: Sat Dec 10 07:05:37 CST 2016

Total time taken to generate the page: 0.14019 seconds