The Oracle (tm) Users' Co-Operative FAQ

How can I schedule a particular piece of code run at a regularly scheduled time ?


Author's name: Connor McDonald

Author's Email: connor_mcdonald@yahoo.com

Date written: 15th June 2001
Updated: 18th June 2001

Oracle version(s): 7.3 - 8.1.7

Historically, everyone ran jobs against the Oracle database in the same way. You wrote a small wrapper unix shell script and dropped it into cron. This is all well and good if you are on a Unix box, but as Oracle began their quest for supporting every computing platform known to man, it became evident that it would be nice if some sort of task scheduling facility could be housed within the database.

Back to index of questions


Since Oracle 7.3 (and possibly even backported into earlier versions of 7), Oracle has provided a package DBMS_JOB to permit task scheduling. It is worth noting that many, many sites still use cron exclusively for their scheduling, and others have exploited the multi-platform nature of perl to achieve the same under environments such as Windows NT. So the existence of DBMS_JOB does not mean you should race out and rewrite all of your existing code. However, DBMS_JOB does have the fundamental advantage that it only runs when the database is up and available - something that external tools (such as shell scripts etc) will always need to test for.

DBMS_JOB only supports the execution of PL/SQL blocks, which in the past has been a significant restriction. However, with the current versions of Oracle, where PL/SQL can quite easily call external routines or Java code, there is very little that cannot not be done using PL/SQL under DBMS_JOB. The big advantage with a DBMS_JOB-based task scheduling implementation is that it will run under any Oracle platform, most probably with little or no modifications required. (This is not to say that using other facilities such as cron should be entirely discounted).

DBMS_JOB is created via catproc.sql, so it should already exist in the database. If not, you can run (as SYS)

under $ORACLE_HOME/rdbms/admin to create the appropriate objects.

To create a job, you add it to the job queue using DBMS_JOB.SUBMIT (described shortly). The job queue is viewed by querying DBA_JOBS (or ALL_JOBS, USER_JOBS etc).

Submitting a job

I'm not going to go through the complete set of options to create jobs, since this is thoroughly covered in the Oracle documentation. The standard documentation can be found here on technet and there is also a good note here on Metalink, but in a nutshell, the basics of creating a job are:

declare
  v_job  number;
begin
  dbms_job.submit(v_job,                   -- the resultant job number
         'PLSQL_program_I_want_to_run;',   -- WHAT:      What PL/SQL do I want to run 
         sysdate,                          -- NEXT_DATE: When do I want to run the PL/SQL
         'sysdate+2/24');                  -- INTERVAL:  How OFTEN do I want to run the PL/SQL
end;                                                     In this case, its every 2 hours (2/24)

 

Sounds simple enough, but there are some frequently encountered problems:

  1. "I tried to submit my job and got 'PLS-00103: Encountered the symbol "END" when expecting one of the following:'"

    Notice the trailing semi-colon for 'PLSQL_program_I_want_to_run;'. This is because the job is actually built by Oracle as an anonymous block, so your PL/SQL routine will be encompassed by BEGIN and END, so you MUST have the semi-colon to ensure that block will compile successfully. This also raises the point that your PL/SQL routine does not have to be a stored procedure or package - it can be any PL/SQL block. For the same reason, any character literal information you pass to the job during submission will need to be double-quoted, for example

    declare
      v_job  number;
    begin
      dbms_job.submit(v_job,
             'add_surname(''JOHNSON('');',
             sysdate,
             'sysdate+1/12');
    end;
    
  2. "OK, my job submitted successfully but it never starts"

    There are two likely reasons for a job not starting. Firstly, a job does not become active until you commit. Because so many people get burnt by this, it is often criticised, but this is a feature that you can use to your advantage, as we'll see later.

    Secondly, Oracle needs to have some processes running in the background to check the job queue, which you enable via init.ora for the database. In older versions of Oracle, these processes were used for refreshing snapshots, so the parameters were specified as (with example values):

    snapshot_refresh_processes = 3   (How many 'job checkers' to have, maximum of 10)
    snapshot_refresh_interval  = 60  (How often each should check for jobs to run)
    

    but these were obsoleted in 7.3 with the new parameters for generic job processing

    job_queue_processes = 3          (Up to 36 'job checkers')
    job_queue_interval = 60
    

    Because the job queue is only checked with the frequency nominated in job_queue_interval, jobs are not guaranteed to start exactly at their nominated time. They always run shortly afterward, where "shortly" could be anything from 0 to job_queue_interval seconds.

    Current versions of Oracle allow from 1-36 background job processes and they show up in a Unix process listing as "ora_<sid>_snpX", where X is 0-9,A-Z. The number of processes can also be altered on the fly with ALTER SYSTEM.

    The job queue interval can be between 1 and 3600 seconds. Interestingly in Oracle 9i, the limit of processes has been increased to a maximum of 1000 but the JOB_QUEUE_INTERVAL parameter is deprecated. I haven't found any information yet on how (and how often) Oracle will internally handle the checking of the job queue. Update: See addenda at the bottom of the page

Worthy of Note

  1. Its more than just a scheduler

    People often only associate DBMS_JOB with scheduling tasks, but it can also be used as a background engine to allow resource intensive work to be initiated by the user, without the user needing to wait for its completion. If a job is created WITHOUT specifying when and how often it is to be run, then it will be run only once (as soon as the user issues a commit). For example, an implementation I saw recently used the creation of records on a warehouse summary table to trigger the appropriate partition maintenance (drop the oldest partition, add a new partition) on the underlying fact table. Similarly, a job can be created to to run only once at some future point in time by omitting just the repeat interval. Placing calls to DBMS_JOB in a database trigger is safe because if the transaction rolls back, then the DBMS_JOB call has in effect never been made.

  2. "Execution drift"

    A common criticism of DBMS_JOB is that if you submit a job to run every two hours on the hour, you'll often see that executions occur not as you anticipated, (say) at 08:00, 10:00, 12:00, but at 08:00, 10:03, 12:07 and so on. This "drifting" of job start times is easily explained. You'll notice in the DBMS_JOB.SUBMIT example above, that the interval parameter is not passed as a date but as a string. This is an expression not a value, that will evaluated at the completion of the job execution. Using the 'sysdate+2/24' example above, the following execution sequence would occur:

    1. 08:00 the job runs and completes at 08:03
    2. Then (and only then), the expression 'sysdate+2/24' is evaluated which is "10:03"
    3. At 10:03, the job runs and completes at 10:07
    4. Then (and only then), the expression 'sysdate+2/24' is evaluated which is "12:07"

    and so on

    To avoid this, our interval simply needs to be 'trunc(sysdate+2/24,''HH'')', to truncate the calculated expression to the nearest hour.

  3. Jobs sequencing is not guaranteed

    The SNP processes run the following query to to determine the next job:

    select job 
    from sys.job$  
    where next_date < sysdate 
    and (field1=":1" or (field1="0" and 'Y'=":2))" 
    order by next_date,job 
    
    

    So jobs are not necessarily processed in the order that they were submitted. If sequencing is required, a solution (as suggested by Tom Kyte) is to write all the parameters for job execution to a (sequence-prefixed) table and then have regular DBMS_JOB runs to read (in sequence order) from the table rather than receive their parameters directly.

  4. Performance considerations

    Its difficult to accurately determine the overhead of the SNP processes (when they aren't actually running jobs). The session cannot be traced since the serial# changes for each job_queue_interval cycle. Similarly if you turn on sql_trace for the database, you get no trace information for the SNP processes.

    However, the SNP processes appear to relatively efficient. Even testing 36 concurent job processes with a 1 second interval, yielded only 4 CPU secs in a 15 minute period, although there is some noticeable latch contention so its not worth overdoing the number of processes or shortening the interval unless you really need to. Even so, such an overhead would be considerably smaller than an equivalent external routine that repeatedly fires up SQL*Plus to connect to the database.

    DBA_JOBS is a simple view based on the SYS.JOB$ table. If you are planning on having massive numbers of jobs, then it may be prudent to move this table out of the SYSTEM tablespace or change its storage definition (neither of which is probably officially supported by Oracle). This table is not indexed and is scanned by each SNP process , so if the table grows abnormally then it should probably be truncated to reset its high water mark down when an opportunity arises.

    Similarly, population of the JOBNO column is performed via the JOBSEQ sequence, which has a default cache size of 20. This could be increased but the default value is unlikely to cause significant performance limitations

  5. Can the submitted job know what its own job number is?

    Enabling a trace around a call to DBMS_JOB, shows that the code Oracle wraps around your PL/SQL code is:

    DECLARE 
      job BINARY_INTEGER := 1; 
    BEGIN 
      ... your stuff 
    END;
    

    so your PL/SQL procedure can reference a variable called "job" to reference the job number. For example, your procedure code could be:

    procedure do_some_work(p_job number) is
    begin
      ...
    end;
    

    and then the job would be submitted with

    declare
      v_job  number;
    begin
      dbms_job.submit(v_job,
             'do_some_work(job)',
             sysdate,
             'sysdate+2/24');
    end;
    
  6. Killing / Removing jobs

    DBA_JOBS_RUNNING identifies the session id (SID) of an active job, which can then be killed in the conventional manner. This view is just a layer on top of V$LOCK for locktype JQ, where ID2 contains the job number.

    Jobs can be removed using DBMS_JOB.REMOVE. You can remove an active job, but it will complete its execution anyway. One important restriction is that you can only remove jobs that you own. Even the SYS account will get an "Job does not exist" error when trying to issue a DBMS_JOB.REMOVE against someone else's job. The "official" (that is, supported) way to remove a job that you do not own is to either:

    However, you can call an undocumented package DBMS_IJOB.REMOVE which does allow you to remove any job - you need SYS or EXECUTE ANY PROCEDURE privileges to do this.

  7. Errors / Broken jobs

    If a job fails, an entry is written to the alert log and a user trace file is created. Since we are all monitoring our alert log anyway (aren't we?!), monitoring job failure is relatively straightforward. The job is retried in 1 minute (if your execution interval was originally set to being greater than 1 minute), and then Oracle uses an binary exponential backoff algorithm until such stage as the backoff interval exceeds the original job submission interval.

    If a job fails 16 times in succession, then it gets marked as BROKEN and it will no longer execution automatically. Whilst 16 times may sound fine, if your schedule is every 10 seconds, then all you need is 160 seconds worth of problems and your job is dead. Some options that can be used to avoid jobs getting into this state are:

  8. Miscellaneous

    Tucked away in the Advanced Queueing manual is a little snippet "The scheduling algorithm places the restriction that at least 2 job queue processes be available for propagation". Whilst there are no Oracle errors in the Error Messages guide to that effect, a scan of the Oracle executable reveals the string "WARNING: At least 2 JOB_QUEUE_PROCESSES must be available for AQ to correctly propagate from ALL schedules." so if you use AQ, it would most probably be prudent to have job_queue_processes set to at least 2 to avoid any possible issues.

    DBMS_IJOB and some other "I"-prefixed routines in DBMS_JOB can be used to achieve (unsupported) subtle variations to the standard job facilities (like removing someone else's job as noted above), but their usage is obviously not supported by Oracle.

    As of 8.1, jobs can be associated with a particular instance in Parallel Server environments.

Conclusion

DBMS_JOB is a very powerful tool but it also has its limitations. Whilst it would be possible to build a complete scheduling solution (dependency tracking etc) with DBMS_JOB, if this is your requirement, then it would be more sensible to invest in a packaged tool such as AutoSys, CA or JobTrack (none of which I'm trying to either sway you toward or away from)


Further reading: OTN documenation and Metalink note (will require metalink id/password)

Addenda:

The job process handling has changed in Oracle 9i. Some additional information from documentation is pasted below.

  "You can schedule routines (jobs) to be run periodically using the job queue. To schedule a job you submit it to the job queue, using the Oracle supplied DBMS_JOBS package, and specify the frequency at which the job is to be run. Additional functionality enables you to alter, disable, or delete a job that you previously submitted.

Job queue (Jnnn) processes execute jobs in the job queue. For each instance, these job queue processes are dynamically spawned by a coordinator job queue (CJQ0) background process. The coordinator periodically selects jobs that are ready to run from the jobs shown in the DBA_JOBS view. It orders them by time, and then spawns Jnnn processes to run the selected jobs. Each Jnnn process executes one of the selected jobs.

The JOB_QUEUE_PROCESSES initialization parameter controls whether a coordinator job queue process is started by an instance. If this parameter is set to 0, no coordinator job queue process is started at database startup, and consequently no job queue jobs are executed. The JOB_QUEUE_PROCESSES initialization parameter also specifies the maximum number of Jnnn processes that can concurrently run on an instance. The maximum number of processes that can be specified is 1000.

The following initialization parameter setting causes the coordinator job queue process to start at database startup, and allows the spawning of a maximum of 60 concurrent Jnnn processes.

JOB_QUEUE_PROCESSES = 60

In any given period that the coordinator job queue process scans the jobs shown in the DBA_JOBS view, it spawns at most only the number of Jnnn processes required to execute the jobs it has selected. While the above example allows for 60 concurrent Jnnn processes, if only 20 jobs are selected for execution, then the coordinator spawns, or reuses, only the number of Jnnn processes necessary to execute the 20 jobs (at least, 20). Any idle existing Jnnn processes are considered available for reuse.

When a Jnnn process finishes execution of a job, it polls for another job to execute. If there are no jobs selected for execution, it enters an idle state, but wakes up periodically to poll again. If, after a predetermined number of tries, it still finds no jobs to execute, it terminates.

The JOB_QUEUE_PROCESSES initialization parameter is dynamic and it can be modified by an ALTER SYSTEM statement. For example, the following statement sets the maximum number of concurrent Jnnn processes allowed to 20.

ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20;

If the new value is lower than the previous setting and less than the number of currently executing Jnnn processes, the excess processes are allowed to complete before they are terminated.

Jnnn processes will not execute jobs if the instance is running in restricted mode."


Back to top

Back to index of questions