Find programs whose schedule is lost/skipped/missed

Question

I have around 350 concurrent programs which run with different schedules in Oracle varying from once in a week to once every minute. In the 350 programs, few programs have more than one schedule with different parameters.

There is a weekly maintenance, during which the concurrent manager is brought down without notice. Can't help with that. But the programs that were running during that time errs out and the subsequent schedule is also lost. Also there many other ways after which a schedule of a program is lost. Mostly these are not noticed for a long time (sometime even months) and this causes issues. Is there anyway to query for the programs whose schedule is lost/skipped/missed?

score 0 · Answer 1 · answered Jun 13 '13 at 05:29

Assuming you have logging history the details of the jobs run will be in USER_SCHEDULER_JOB_RUN_DETAILS

So you need to work out what data should be in there, but isn't.

You might be able to find jobs where the difference between SYSDATE and the last run date is greater than the average difference between historical job runs. We can use the LAG analytical function to determine the time difference between two rows of the same job, and then average this, and then add the difference to the last time the job ran to see if the time interval has been exceeded.

I don't use the scheduler, therefore don't have any data to test this, but it works in principle with a similar table that I do have:

SELECT job_name, last_run, avg_interval, last_run + avg_interval as expected_runtime
FROM (
  SELECT job_name, max(actual_start_date) as last_run, avg(difference) as avg_interval
  FROM (
    SELECT job_name, actual_start_date, 
        to_date(actual_start_date) - lag(to_date(actual_start_date)) 
               over (partition by job_name order by log_date) as difference
      FROM user_scheduler_job_run_details
    ) 
   GROUP BY job_name
 )
 WHERE last_run + avg_interval < sysdate
 ORDER BY expected_runtime desc

Any historical jobs that have at least two runs should be picked up if they haven't run again. You may need to tweak it a little to allow for tolerances in job times.

Find programs whose schedule is lost/skipped/missed

1 Answers1