Tag: dbms_scheduler

When Overlapping CRON Jobs Attack…

We recently had an issue, which I suspect was caused by overlapping CRON jobs. By that I mean a CRON job had not completed its run by the time it was scheduled to run again.

CRON

If you’ve used UNIX/Linux you’ve probably scheduled a task using CRON. We’ve got loads of CRON jobs on some of our systems. The problem with CRON is it doesn’t care about overlapping jobs. If you schedule something to run every 10 minutes, but the task takes 30 minutes to complete, you will get overlapping runs. In some situations this can degrade performance to the point where each run gets progressively longer, meaning there are more and more overlaps. Eventually things can go bang!

Fortunately there is a really easy solution to this. Just use “flock”.

Let’s say we have a job that runs every 10 minutes.

*/10 * * * * /u01/scripts/my_job.sh > /dev/null 2>&1

We can use flock protect it by providing a lock file. The job can only run if it can lock the file.

*/10 * * * * /usr/bin/flock -n /tmp/my_job.lockfile /u01/scripts/my_job.sh > /dev/null 2>&1

In one simple move we have prevented overlapping jobs.

Remember, each job will need a separate lock file. In the following example we have three separate scripts, so we need three separate lock files.

*/10 * * * * /usr/bin/flock -n /tmp/my_job1.lockfile /u01/scripts/my_job1.sh > /dev/null 2>&1
*/10 * * * * /usr/bin/flock -n /tmp/my_job2.lockfile /u01/scripts/my_job2.sh > /dev/null 2>&1
*/10 * * * * /usr/bin/flock -n /tmp/my_job3.lockfile /u01/scripts/my_job3.sh > /dev/null 2>&1

Oracle Scheduler (DBMS_SCHEDULER)

The Oracle Scheduler (DBMS_SCHEDULER) doesn’t suffer from overlapping jobs. The previous run must be complete before the next run can happen. If we have a really slow bit of code that takes 30 minutes to run, it is safe to schedule it to run every 10 minutes, even though it may seem a little stupid.

begin
  dbms_scheduler.create_job (
    job_name        => 'slow_job',
    job_type        => 'plsql_block',
    job_action      => 'begin my_30_min_procedure; end;',
    start_date      => systimestamp,
    repeat_interval => 'freq=minutely; interval=10; bysecond=0;',
    enabled         => true);
end;
/

The Oracle Scheduler also has a bunch of other features that CRON doesn’t have. See here.

Conclusion

I’m not a massive fan of CRON. For many database tasks I think the Oracle Scheduler is far superior. If you are going to use CRON, please use it safely. 🙂

Cheers

Tim…

Video : DBMS_JOB Jobs Converted to DBMS_SCHEDULER Jobs in Oracle Database 19c Onward

In today’s video we discuss a change to the old DBMS_JOB scheduler from 19c onward.

This video is based on the following article.

You may find these useful as well.

The star of today’s video is Liron Amitzi, who took a break from shopping to film this. You might notice some familiar faces in the background and on the video screen to the side… 🙂

Cheers

Tim…

Q: Is the CREATE JOB privilege required in 19c? A: No!

A couple of people have already written about a new feature in oracle 19c, which converts jobs created using DBMS_JOB into DBMS_SCHEDULER jobs.

I finally got round to writing up my notes about it here.

The conversion comes with rather interesting consequences.

The scheduler side of things is tighter. As Connor pointed out you now need the CREATE JOB privilege to use the DBMS_JOB package. That’s nice, but isn’t that the opposite of the title of this post? Yes, but…

The problem is the re-implementation of materialized refresh groups using the DBMS_REFRESH package didn’t seem to follow the same approach. You can create a refresh group, which creates a DBMS_SCHEDULER job, without needing the CREATE_JOB privilege. Once you own a job, you can amend it, which means there is now a method to create DBMS_SCHEDULER jobs without needing the CREATE JOB privilege. Doh! You can see an example of it in 19.3 here.

Thoughts:

  • It’s clearly a bug, and I’m sure it will be picked up in a future release.
  • Although it seems pretty bad, remember the DBMS_REFRESH and DBMS_JOB packages are available by default and in previous releases you didn’t need the CREATE JOB privilege to them.
  • If this is a problem you can revoke execute on DBMS_REFRESH from PUBLIC, like you may have been doing for DBMS_JOB already.

Cheers

Tim…

PS. SR raised.

SR 3-20860955641 : Jobs can be created without the CREATE JOB privilege.

PPS. This is now Bug 30357828 and is being worked on.

SQL Developer 3.1 : Scheduler Support…

I had a play around with the scheduler support in SQL Developer 3.1 today. I’m late to the party because most of it has been there since 3.0, but what the hell.

It’s more of an opinion piece, so it started as a blog post, but it got too big. Not really what I usually put on the website, but I figured if I put it on the blog it would get in the way of the movie and book reviews and that simply wouldn’t do… 🙂

I am still worried about feature creep turning SQL Developer into the new TOAD, but so far so good. I’m liking it more and more with each release.

Cheers

Tim…