DBMS_JOB is an asynchronous mechanism

One of the very cool things about DBMS_JOB is that a job does not “exist” as such until the session that submitted the job commits the transaction. (This in my opinion is a critical feature that is missing from the DBMS_SCHEDULER package which, other than this omission, is superior to DBMS_JOB in every way).

Because DBMS_JOB is transactional, we can use it to make “non-transactional” things appear transactional. For example, if part of the workflow for hiring someone is to send an email to the Human Resources department, we can do the email via job submission so that an email is not sent if the employee record is not created successfully or is rolled back manually, eg:


begin
  insert into EMP (empno, ename) values (123, 'CONNOR');
  dbms_job.submit( :j,'notify_hr(empno=>123);');
end;

This post is not about that – that cool usage scenario is well known. But as a consequence, people tend to think that as soon as I commit, the job will spring into life. This is typically the case, but there are no guarantees that your job will run immediately, even if job queue processes are available.

Here’s a couple of examples of that in action


--
-- This one is almost instant
--
SQL> create table t
  2   ( submitted timestamp,
  3     started   timestamp );

Table created.

SQL>
SQL> create or replace
  2  procedure prc is
  3  begin
  4    update t set started = systimestamp;
  5    commit;
  6  end;
  7  /

Procedure created.

SQL>
SQL> declare
  2    j int;
  3  begin
  4    dbms_job.submit( j,'prc;');
  5    insert into t(submitted) values (systimestamp);
  6    commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> col submitted format a32
SQL> col started format a32
SQL> select * from t;

SUBMITTED                        STARTED
-------------------------------- --------------------------------
13-FEB-19 09.31.10.956989 PM     13-FEB-19 09.31.10.994153 PM

--
-- This one not so much :-)
--

SQL> declare
  2    j int;
  3  begin
  4    dbms_job.submit( j,'prc;');
  5    insert into t(submitted) values (systimestamp);
  6    commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> col submitted format a32
SQL> col started format a32
SQL> select * from t;

SUBMITTED                        STARTED
-------------------------------- --------------------------------
14-FEB-19 01.22.45.928000 PM

SQL> /

SUBMITTED                        STARTED
-------------------------------- --------------------------------
14-FEB-19 01.22.45.928000 PM

SQL> /

SUBMITTED                        STARTED
-------------------------------- --------------------------------
14-FEB-19 01.22.45.928000 PM     14-FEB-19 01.22.59.775000 PM

In particular, I tend to notice this more on Windows platforms than others. MOS Note 2109840.1 is also a good starting point if you are seeing huge delays – you may have hit upon a bug.

So just be aware that DBMS_JOB will run your job soon after committing, but “soon” is a flexible term Smile

3 Comments on “DBMS_JOB is an asynchronous mechanism

  1. We switched from DBMS_JOBS to DBMS_SCHEDULER recently just for the reason jobs were getting delayed.!

  2. Hello Connor,

    As an “old times bear”, I completely agree with you that having job creation as transactional is a critical feature 🙂 surely after reading the many examples in Tom Kyte’s books about exactly what you have specified, namely, “making non-transactional actions look as transactional”.

    I wonder how come that nobody objected on this behavior, so that at least an additional argument
    would be added to the DBMS_SCHEDULER job creation procedure(s), allowing them to become
    transactional, just like DBMS_JOB.

    I am not sure that the experienced delay of a job execution depends on whether you are using
    DBMS_JOB or DBMS_SCHEDULER for submitting the job.
    As far as I know, the same mechanism (aka having a number of slaves defined by JOB_QUEUE_PROCESSES) is used for execution the jobs.

    The only difference is that up to Oracle8i there existed an initialization parameter named JOB_QUEUE_INTERVAL for the “wake-up” interval of the job execution slave processes,
    with a default of 60 seconds.
    This parameter is not documented any more in the later versions, so that it is possible that
    now some other value (maybe lower than 60 seconds) is used transparently.

    But maybe this parameter is still available as an invisible parameter, that can still be modified
    if anybody needs it.

    We have used DBMS_JOB a lot, up to and including Oracle11g (and maybe even 12c),
    both on Windows and on Linux, and we have not experienced any significant execution delays.

    Cheers & Best Regards,
    Iudith

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: