Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Scheduled job time incrementally increasing - why?

604042Feb 14 2008 — edited Feb 28 2008
I have 5 stored procedures that keep our client's data sync'd with ours. I am running 10g XE and using heteogenous services (our application database is postgresql, but we have an "in between" database that is the oracle system I'm working with). I have created 5 separate jobs using the scheduler. 4 of the jobs run fine - they are all smaller tables and recordsets. 1 job does not run very well. It works with roughly 5k records and has to compare each one to determine if there were changes. When I run it by itself (via calling the procedure), it takes 11 minutes to complete. This is acceptible to us.

When I set up the job, I was having it run every 30 minutes. After a few days of running I checked to see if everything was OK. Well I noticed that the RUN_DURATION time increases 10-20 seconds after EVERY run. So my 11 minute procedure turned into 40 minutes after a few weeks. This is not acceptible to us.

Why would this happen? The other jobs are not taking much more time then they do running them via a direct procedure call. As for data, it has only increased 40 records and there are only 3 fields in the table. Below is the code I have for setting up the job:

--CREATE A JOB TO SYNC THE SCHEDULE DATA
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'sync_schedule',
job_type => 'STORED_PROCEDURE',
job_action => 'JASON.STEP5SYNCSCHEDULE',
start_date => sysdate,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=30', /* every 30 minutes */
enabled => TRUE);
END;

Any input is greatly appreciated. As of right now I have bumped the interval to 60 minutes and it is still increasing the run_duration.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 27 2008
Added on Feb 14 2008
14 comments
4,355 views