Cron vs. DBMS_JOB 2

[stevecal]

I have an interesting argument currently going with the production end of our database deployment team (my bit is the development side), and would be interested in opinions.

I have a current process which runs once a day on a cron job. Fair enough, no real need for any DBMS_JOB here. But now I want a few behind the scenes processes to clean up temporary messaging tables, etc, etc. on a number of bases:
one job will run every five minutes, one every twenty seconds, and one will occur occasionally dependant on database state.

The production guys reasonably enough are saying that this should be in a package and it should be called by a cron job. But it seems a bit absurd to me to make a call from external to Oracle to do something we could quite happily do internally using DBMS_JOB.

The question is, should I warm up my virtual baseball bat or let the poor saps have their way?

 

[Kenrae]

Personally, I don't like crons very much because they can be a dead weight on your DB. When you restart Oracle, it kills all crons and waits for all of them to die. This can be really exasperating (Maybe up to one hour).
But jobs have some problems, too. A job reschedules itself before executing. Then, if Oracle falls down and restarts after the next execution time, you'll have a job whose execution time is out of date. There's a possible solution, though. You can use an on startup trigger in order to reschedule all these jobs. You'll need to know, for every job, the expression used for calculating the next date.

As you can see, both options have some problems. You'll know, depending on your system properties, which option suits you.

Hope this helps

 

[MikeJones]

I'd give DBMS_JOB a big thumbs up, the abilty for it to restart jobs that go down during a crash etc. take responsibilty away from the individual programmer and to Oracle corp itself. You've paid for the software, you might as well get everything you can from it.

Futhermore you can easily maintain job schedules within the DB and change them programatically (this includes breaking jobs). Which leads me on to my final point.

It might be worth pointing out to your production people that using DBMS_JOB is in THERE interests.

1. It nolonger makes them responsible for making sure the
Cron jobs are set up correctly.
2. They no longer have to write pages of shell to cope
with jobs failing and what needs to be done as a clean
up exercise

I've had a similar problem before, they were reluctant however as they thought that we were encoaching on there area. but then that's a different problem.

Mike.

 

[stevecal]

Thanks guys, that gives me some useful background. To my mind DBMS_JOB looks the way to go, particularly in the light of being able to audit internally whether a job has occurred or not (I have in mind a table to manage this).