Within Reason

Periodic jobs in the Oracle RDBMS

Posted by Ceri Davies Mon, 03 Oct 2005 19:47:00 GMT

My foray into Oracle continues; today I got to see the horrors of the DBMS_JOB package, which is used for scheduling periodic tasks (think cron(8)).

This particular database had a couple of jobs scheduled, but they were not being run. I knew how to sort that out; in 9i there is a database parameter called job_queue_processes which governs how many job runners are allowed concurrently, and this was set to zero:

SQL> show parameters job

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     0

There were, therefore, no job runners:

SQL> !ps -ef|grep j
  oracle  6183  6182  0 14:13:15 pts/1    0:00 grep j
  oracle  6182  6166  0 14:13:15 pts/1    0:00 /bin/sh -c ps -ef|grep j

This is a dynamic parameter, so I changed it:

SQL> alter system set job_queue_processes = 2;

System altered.

SQL> !ps -ef|grep j
  oracle  6226  6166  0 14:17:35 pts/1    0:00 /bin/sh -c ps -ef|grep j
  oracle  6219     1  0 14:17:30 ?        0:00 ora_cjq0_WXYZ
  oracle  6220     1  0 14:17:30 ?        0:00 ora_j000_WXYZ
  oracle  6222     1  0 14:17:30 ?        0:00 ora_j001_WXYZ

Since there were jobs that needed running, the ora_cjq controller instantly fired off a pair of ora_jnnn processes to run them. Unfortunately, one of them was an analyze of the SYS schema, which immediately caused deadlocks since this was the middle of the day. And this despite the job not being scheduled until what I thought was 3am:

SQL> select job,last_date,next_date,
 substr(interval,1,24) interval,
 substr(what,1,48) what
 from dba_jobs
 where job = '2';

JOB LAST_DATE NEXT_DATE INTERVAL                         WHAT
--- --------- --------- ------------------------ ------------------------------------------------
  2 03-OCT-05 17-OCT-05 trunc(sysdate+14)+27/24  dbms_utility.analyze_schema('SYS','COMPUTE');

However, Oracle insisted on playing catchup there and then, which made me doubt whether trunc(sysdate+14)+27/24 really did mean “14 days from now at 3am”.

Unfortunately the docs suck big time; the definition of INTERVAL doesn’t exist in the Oracle9i Database Administrator’s Guide, and the excerpt below tells you everything you need to know in order to understand my pain (my emphasis):

If you always want to automatically execute a job at a specific time, regardless of the last execution (for example, every Monday), […]

It just strikes me that “Monday” isn’t all that specific…