Running Procedures Asynchronously with Oracle Job Scheduler

Consider a PL/SQL stored procedure that handles a heavy transaction. The procedure is extremely slow – when executed from a UI, the application hangs for minutes. On analysis it is found that the procedure is performing a complex series of steps, a portion of which are non-critical and need not hold up the entire transaction. In other words, it would be acceptable if:

• some of the steps are run asynchronously while a slimmer main transaction completes
• failures (if any) in the asynchronous steps do not cause a failure in the main transaction

Oracle PL/SQL helps us achieve these objectives with asynchronous processing using Oracle job scheduler DBMS_SCHEDULER. Here’s a demo to show you how.

Use Case for Demo: Movie Ticketing Application

Let’s say this heavy and slow procedure is one that creates a booking in a movie ticketing application. In its current form, it does all of the following:

create_booking (booking_id)
{
  allocate_seats;
  capture_customer_details;
  receive_payment;
  notify_customer;
  upsell_food;
  update_central_crm;
}

The problem: The last three “non-critical” steps are slowing down the main flow. While it is desirable to have these steps work close to real-time, some lag with these steps is acceptable if it makes the main flow of create_booking faster.

-- BEFORE Slow create_booking 
-- All processing synchronous
create_booking (booking_id)
{

  -- Critical parts of booking: main flow, any failure 
  -- here must fail the entire booking
  allocate_seats;
  capture_customer_details;
  receive_payment;

  -- Non-critical parts of booking: desirable to have this 
  -- as close as possible to real-time but not at the cost 
  -- of slowing down the main flow
  notify_customer;
  upsell_food;
  update_central_crm;
 }

The solution: Move the last three “non-critical” steps to another procedure and invoke that procedure asynchronously. The final state should look like this algorithmically:

-- AFTER Fast create_booking 
-- Non-critical processing asynchronous
create_booking (booking_id)
{

  -- Critical parts of booking: main flow, any failure 
  -- here must fail the entire booking
  allocate_seats;
  capture_customer_details;
  receive_payment;

  -- Non-critical parts of booking: wrapped in 
  -- a separate procedure called asynchronously
  [async] post_booking_flow(booking_id);
 }

-- Async steps factored out into separate procedure
post_booking_flow (booking_id) 
{
  notify_customer;
  upsell_food;
  update_central_crm;
}

Oracle Job Scheduler to Run Procedure Asynchronously: Summary

To run a piece of code asynchronously via Oracle Job Scheduler, a summary of the steps needed:

1. Create procedure to be run asynchronously
2. Call the procedure via DBMS_SCHEDULER.CREATE_JOB in the main flow for a single immediate run (i.e. AUTO_DROP = TRUE, the default)

Oracle Job Scheduler to Run Job Asynchronously: Stepwise Scripts

1. Create procedure to be run asynchronously

This procedure is a wrapper to the portion of code in the main flow which is to be run asynchronously.

-- Procedure to be run asynchronously
create or replace procedure 
post_booking_flow (booking_id in varchar2) 
as
begin
  dbms_output.put_line('START post_booking_flow');
  notify_customer;
  upsell_food;
  update_central_crm;
  dbms_output.put_line('END post_booking_flow');
end;
/

2. Call the procedure via DBMS_SCHEDULER.CREATE_JOB for a single immediate run

The parameter values for CREATE_JOB to support single immediate run:

• job_name: A unique name, say ‘post_booking_flow_job’ concatenated with booking_id to prevent concurrency conflict
• job_type: PLSQL_BLOCK
• job_action: PL/SQL block invoking procedure created in step 1
• enabled: TRUE (default is FALSE)
• auto_drop: TRUE (default is TRUE)

-- Non-critical processing asynchronous
-- with DBMS_SCHEDULER.CREATE_JOB
create or replace procedure create_booking
(booking_id in varchar2)
as 
begin
  dbms_output.put_line('START create_booking');    
  -- Critical parts of booking: main flow, any failure 
  -- here must fail the entire booking
  allocate_seats;
  capture_customer_details;
  receive_payment;

  -- Non-critical parts of booking: wrapped in 
  -- a separate procedure called asynchronously
  dbms_output.put_line('Before post_booking_flow_job');
  dbms_scheduler.create_job (
  job_name   =>  'post_booking_flow_job'||booking_id,
  job_type   => 'PLSQL_BLOCK',
  job_action => 
    'BEGIN 
       post_booking_flow('''||booking_id||''');
     END;',
  enabled   =>  TRUE,  
  auto_drop =>  TRUE, 
  comments  =>  'Non-critical post-booking steps');
  
  dbms_output.put_line('After post_booking_flow_job');  
  dbms_output.put_line('END create_booking');  
end;
/

Oracle Job Scheduler to Run Job Asynchronously: Scripts When Run

SQL> -- Procedure to be run asynchronously
SQL> create or replace procedure
  2  post_booking_flow (booking_id in varchar2)
  3  as
  4  begin
  5    dbms_output.put_line('START post_booking_flow');
  6    notify_customer;
  7    upsell_food;
  8    update_central_crm;
  9    dbms_output.put_line('END post_booking_flow');
 10  end;
 11  /

Procedure created.

SQL> -- Non-critical processing asynchronous
SQL> -- with DBMS_SCHEDULER.CREATE_JOB
SQL> create or replace procedure create_booking
  2  (booking_id in varchar2)
  3  as
  4  begin
  5    dbms_output.put_line('START create_booking');
  6    -- Critical parts of booking: main flow, any failure
  7    -- here must fail the entire booking
  8    allocate_seats;
  9    capture_customer_details;
 10    receive_payment;
 11
 12    -- Non-critical parts of booking: wrapped in
 13    -- a separate procedure called asynchronously
 14    dbms_output.put_line('Before post_booking_flow_job');
 15    dbms_scheduler.create_job (
 16    job_name   =>  'post_booking_flow_job'||booking_id,
 17    job_type   => 'PLSQL_BLOCK',
 18    job_action =>
 19      'BEGIN
 20         post_booking_flow('''||booking_id||''');
 21       END;',
 22    enabled   =>  TRUE,
 23    auto_drop =>  TRUE,
 24    comments  =>  'Non-critical post-booking steps');
 25
 26    dbms_output.put_line('After post_booking_flow_job');
 27    dbms_output.put_line('END create_booking');
 28  end;
 29  /

Procedure created.

Test the asynchronous PL/SQL job

Pass the argument booking_id and test create_booking, now branching off into an asynchronous call to post_booking_flow_job||booking_id.

SQL>-- Running the procedure with async call
SQL> exec create_booking('A001');
START create_booking
Before post_booking_flow_job
After post_booking_flow_job
END create_booking

PL/SQL procedure successfully completed.

SQL> -- Running the procedure with async call
SQL> exec create_booking('A002');
START create_booking
Before post_booking_flow_job
After post_booking_flow_job
END create_booking

PL/SQL procedure successfully completed.

Good news: the asynchronous portion of create_booking code no longer causes a lag in the booking process.

Post Async Job Run: Verify Scheduler Logs

ALL_SCHEDULER_JOB_LOG displays log for the Scheduler jobs accessible to the current user.

ALL_SCHEDULER_JOB_RUN_DETAILS displays run details for the Scheduler jobs accessible to the current user.

SQL> -- Job logs generated in the last hour
SQL> select job_name, status
  2  from all_scheduler_job_log
  3  where job_name like 'POST_BOOKING_FLOW_JOB%'
  4  and log_date > sysdate - 1/24
  5  order by log_date desc;

JOB_NAME                  STATUS
------------------------- ----------
POST_BOOKING_FLOW_JOBA002 SUCCEEDED
POST_BOOKING_FLOW_JOBA001 SUCCEEDED

SQL> -- Job run details generated in the last hour
SQL> select job_name, status
  2  from all_scheduler_job_run_details
  3  where job_name like 'POST_BOOKING_FLOW_JOB%'
  4  and log_date > sysdate - 1/24
  5  order by log_date desc;

JOB_NAME                  STATUS
------------------------- ----------
POST_BOOKING_FLOW_JOBA002 SUCCEEDED
POST_BOOKING_FLOW_JOBA001 SUCCEEDED

Scheduler Privileges to Create Job Asynchronously

In addition to EXECUTE privilege on the package DBMS_SCHEDULER, one must have:

• CREATE JOB privilege to create a job in one’s own schema, and
• CREATE ANY JOB privilege to create a job in any schema except SYS

To invoke a PL/SQL program when creating a job, the owner of the job must be the owner of the program or have EXECUTE privilege on the program.

For Further Reading

Running procedures asynchronously is just one use case for DBMS_SCHEDULER – look up Oracle 12c documentation for the range of its features and utilities.

Also see: Get job name inside DBMS_SCHEDULER executed procedure