DBMS schedulers

Question

I have two procedures p1 which prints 1 to 10,and p2 which prints 10 to 1, I have used schedulers to run these procedures in parallel. my code is

BEGIN
dbms_scheduler.create_job (
job_name           =>  'j1a',
job_type           =>  'STORED_PROCEDURE',
job_action         =>  'p1',
start_date        =>  SYSTIMESTAMP,
repeat_interval    =>  'FREQ=DAILY', /* every  day */
enabled         => TRUE
);
DBMS_SCHEDULER.CREATE_JOB (
job_name           =>  'j2a',
job_type           =>  'STORED_PROCEDURE',
job_action         =>  'p2',
start_date        =>  SYSTIMESTAMP,
repeat_interval    =>  'FREQ=DAILY' ,/* every other day */
enabled             => TRUE
);
end;
BEGIN
DBMS_SCHEDULER.RUN_JOB(
JOB_NAME            => 'j1a,j2a'
--USE_CURRENT_SESSION => FALSE
);
end;

I am getting output as

1
2
3
4
5
6
7
8
9
10
10
9
8
7

but not as

1
2
10
9
8
3
..

Please help me in this regard.

Answer 1

You are running the jobs like this:

DBMS_SCHEDULER.RUN_JOB(
JOB_NAME            => 'j1a,j2a'
--USE_CURRENT_SESSION => FALSE
);

The default value for USE_CURRENT_SESSION is true. This means both jobs run in the same session which means they run serially. There is no way to change this behaviour: a session can only run one job at a time.

You could run the jobs in parallel by uncommenting the USE_CURRENT_SESSION parameter setting but then you wouldn't see the output in your session. However, there is an option: use DBMS_PIPE. DBMS_PIPE is an Oracle built-in library which allows sessions to communicate with each other. In this case you could use it to send messages from the background jobs to another session which will receive the messages and write them to the DBMS_OUTPUT buffer.

The following may seem a trifle over-engineered for your requirement, but it's been over a decade since I last used DBMS_PIPE, and rusty pipes are bad for the health, so here we go!

This is a toy messaging pipe package. It has two procedures - one for sending messages and one for reading them:

create or replace package messaging is

subtype t_message is varchar2(128);

c_pipe_name constant varchar2(30) := 'MSG_PIPE';
c_stop      constant t_message    := 'STOP';

procedure listen (p_timeout in number default null);

procedure send (p_message in t_message);

end messaging;
/

Simplistically the listening procedure remains open until any sender tells it to stop. Here is the package implementation:

create or replace package body messaging is

procedure listen (p_timeout in number default null) is

  l_timeout pls_integer;
  l_message t_message := 'waiting';
  l_status  pls_integer;

begin

  dbms_output.put_line(c_pipe_name || ' listening');

  l_status := DBMS_PIPE.create_pipe(pipename  => c_pipe_name
                                    , private => false);

  l_timeout := coalesce(p_timeout, DBMS_PIPE.maxwait);

  while l_message != c_stop loop

    l_status  := DBMS_PIPE.receive_message(pipename  => c_pipe_name
                                           , timeout => l_timeout);

    if l_status = 0 then

       DBMS_PIPE.unpack_message(l_message);
       dbms_output.put_line('Message received: ' || l_message);

    end if;

  end loop;

  l_status := DBMS_PIPE.remove_pipe(pipename => c_pipe_name);

  dbms_output.put_line(c_pipe_name || ' stopped');

end listen;

procedure send (p_message in t_message) is

  l_status    number;

begin

  DBMS_PIPE.pack_message(p_message);
  l_status := DBMS_PIPE.send_message(c_pipe_name);

end send;

end messaging;
/

I have rewritten your stored procedures to call messaging.send():

create or replace procedure p1 is
  l_message   varchar2(80) := 'P#1:';
begin

  for idx in 1..10 loop
      messaging.send(l_message || to_char(idx));      
      dbms_session.sleep(1);
  end loop;

  messaging.send(messaging.c_stop);

end;
/
create or replace procedure p2 is
  l_message   varchar2(80) := 'P#2:';
begin

  for idx in reverse 1..10 loop
      messaging.send(l_message || to_char(idx));      
      dbms_session.sleep(1);  
  end loop;

  messaging.send(messaging.c_stop);

end;
/

These routines use sleep() because I ran them manually to test and had to make sure the first didn't finish before I kicked off the second: you may not need them when they're running as jobs. (Incidentally I used the long-awaited Robertson enhancement DBMS_SESSION.SLEEP() because I'm running on Oracle 18c; if you're on an earlier version you may need to ask your DBA to grant you EXECUTE ON DBMS_LOCK, or do without sleep.)

For completeness your job submission code:

BEGIN
  dbms_scheduler.create_job (
    job_name           =>  'j1a',
    job_type           =>  'STORED_PROCEDURE',
    job_action         =>  'p1',
    start_date        =>  SYSTIMESTAMP,
    repeat_interval    =>  'FREQ=DAILY', /* every  day */
    enabled         => TRUE
  );
  DBMS_SCHEDULER.CREATE_JOB (
    job_name           =>  'j2a',
    job_type           =>  'STORED_PROCEDURE',
    job_action         =>  'p2',
    start_date        =>  SYSTIMESTAMP,
    repeat_interval    =>  'FREQ=DAILY' ,/* every other day */
    enabled             => TRUE
  );
end;
/

Now, I set up a session to listening for messages ...

begin
  messaging.listen();
end;
/

... and in another session I kick off your jobs to run in the background:

BEGIN
    DBMS_SCHEDULER.RUN_JOB(
      JOB_NAME            => 'j1a,j2a'
      , USE_CURRENT_SESSION => FALSE
);
end;
/

And lo! here is the dbms_output:

MSG_PIPE listening
Message received: P#1:1
Message received: P#2:10
Message received: P#2:9
Message received: P#1:2
Message received: P#2:8
Message received: P#1:3
Message received: P#2:7
Message received: P#1:4
Message received: P#1:5
Message received: P#2:6
Message received: P#2:5
Message received: P#1:6
Message received: P#1:7
Message received: P#2:4
Message received: P#2:3
Message received: P#1:8
Message received: P#1:9
Message received: P#2:2
Message received: P#2:1
Message received: P#1:10
Message received: STOP
MSG_PIPE stopped

So clearly the procedures are running in parallel but the messages aren't neatly interleaved. This is inevitable when we run autonomous parallel jobs, without any interplay between the sessions.