Scheduled job time incrementally increasing - why?
604042Feb 14 2008 — edited Feb 28 2008I 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.