Run procedures in parallel - Oracle PL/SQL

Question

I am trying to run stored procedures in parallel - Oracle PL/SQL using dbms_scheduler but I am getting an error like an unknown job, I have also tried dbms_job, here I am getting an error- identifier dbms_jobs must be declared. Could someone please help me out? Below are two approaches I have tried:

CREATE PACKAGE BODY pkg IS
CREATE PROCEDURE do_parallel_execution
IS
BEGIN
   DBMS_SCHEDULER.RUN_JOB('pkg1.proc1', false);
   DBMS_SCHEDULER.RUN_JOB('pkg1.proc2', false);
   DBMS_SCHEDULER.RUN_JOB('pkg1.proc3', false);
END;

CREATE PACKAGE BODY pkg IS
CREATE PROCEDURE run_in_parallel
IS
   l_jobno pls_integer;
BEGIN
   dbms_job.submit(l_jobno, 'pkg1.proc1; end;' );
   dbms_job.submit(l_jobno, 'pkg1.proc2; end;' );
  -- dbms_job.submit(l_jobno, 'pkg1.proc3; end;' );
END;

where pkg1 has all 3 procedures defined in it. Thank you!

`dbms_job` is deprecated but prefer using `dbms_scheduler`. [this](https://stackoverflow.com/a/46618695/5841306) might help you I think. — Barbaros Özhan, Jun 13 '20 at 16:06
you can't execute programms parallel in plsql. but you can create scheduler jobs that will be executed to the same time by oracle. how to create scheduler jobs you already can find on SO if you use a search bar. e.g. [here](https://stackoverflow.com/questions/37019719/create-oracle-scheduler-job) — hotfix, Jun 13 '20 at 16:11
Look at the `DBMS_PARALLEL_EXECUTE` package, and [see this AskTom question](https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4248554900346593542). For further info, try Googling "parallel execution in pl/sql". [The 19c documentation can be found here](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_PARALLEL_EXECUTE.html#GUID-D13B6975-09B5-4711-AD43-45F68228C1CC) but it's supported back to (I believe) 11g. — Bob Jarvis - Слава Україні, Jun 13 '20 at 16:43
@Reinstate Monica, I think this concept can be used if you want parallelism in the program, I am trying to run multiple procedures at same time — ZZ3111, Jun 13 '20 at 16:50
You can use Scheduler job chains to achieve parallel execution. — pmdba, Jun 14 '20 at 03:16
@PriyadarshniSagar I think you're pretty close, you just need to use the procedure `CREATE_JOB` instead of simply calling `RUN_JOB`. Look at [this article](https://oracle-base.com/articles/10g/scheduler-10g#simple) for a simple example. — Jon Heller, Jun 15 '20 at 00:48

pmdba · Accepted Answer · 2020-06-14T11:53:06.917

To execute otherwise unrelated procedures in parallel, use a Scheduler Job Chain:

Create procedures:

create or replace package test as
    procedure test1;
    procedure test2;
    procedure test3;
end test;
/

create or replace package body test as
    procedure test1 is
    begin
        sys.dbms_session.sleep(5);
    end test1;

    procedure test2 is
    begin
        sys.dbms_session.sleep(5);
    end test2;

    procedure test3 is
    begin
        sys.dbms_session.sleep(5);
    end test3;
end test;
/

Create Scheduler Programs for each procedure:

BEGIN
    DBMS_SCHEDULER.create_program(
        program_name => 'TEST1_PROGRAM',
        program_action => 'TEST.TEST1',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    DBMS_SCHEDULER.ENABLE(name=>'TEST1_PROGRAM');    

    DBMS_SCHEDULER.create_program(
        program_name => 'TEST2_PROGRAM',
        program_action => 'TEST.TEST2',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    DBMS_SCHEDULER.ENABLE(name=>'TEST2_PROGRAM');    

    DBMS_SCHEDULER.create_program(
        program_name => 'TEST3_PROGRAM',
        program_action => 'TEST.TEST3',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    DBMS_SCHEDULER.ENABLE(name=>'TEST3_PROGRAM');    
END;
/

Create the Scheduler Chain:

BEGIN
  -- one step for each program
  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
     chain_name          => 'TEST_CHAIN'
    ,step_name           => 'CHAIN_STEP1'
    ,program_name        => 'TEST1_PROGRAM');

  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
     chain_name          => 'TEST_CHAIN'
    ,step_name           => 'CHAIN_STEP2'
    ,program_name        => 'TEST2_PROGRAM');

  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
     chain_name          => 'TEST_CHAIN'
    ,step_name           => 'CHAIN_STEP3'
    ,program_name        => 'TEST3_PROGRAM');

  -- one rule with condition "true" to start each step immediately
  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
      CHAIN_NAME  => 'TEST_CHAIN',
      rule_name  => 'TEST_RULE1',
      condition => 'TRUE',
      action => 'START "CHAIN_STEP1"');   

  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
      CHAIN_NAME  => 'TEST_CHAIN',
      rule_name  => 'TEST_RULE2',
      condition => 'TRUE',
      action => 'START "CHAIN_STEP2"');   

  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
      CHAIN_NAME  => 'TEST_CHAIN',
      rule_name  => 'TEST_RULE3',
      condition => 'TRUE',
      action => 'START "CHAIN_STEP3"');   

  -- one rule to close out the chain after all steps are completed    
  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
     chain_name          => 'TEST_CHAIN',
     rule_name           => 'TEST_RULE4',
     condition           => 'CHAIN_STEP1 Completed AND CHAIN_STEP2 Completed AND CHAIN_STEP3 Completed',
     action              => 'END 0');

END;
/

The chain flow now looks like this (as depicted by SQL Developer):

Now create a Scheduler Job to run the chain:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => 'TEST_JOB',
            job_type => 'CHAIN',
            job_action => 'TEST_CHAIN',
            number_of_arguments => 0,
            start_date => NULL,
            repeat_interval => NULL,
            end_date => NULL,
            enabled => FALSE,
            auto_drop => FALSE,
            comments => '');

    DBMS_SCHEDULER.SET_ATTRIBUTE( 
             name => 'TEST_JOB', 
             attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_RUNS);

END;
/

And run the job:

BEGIN
    DBMS_SCHEDULER.RUN_JOB(job_name => 'TEST_JOB', USE_CURRENT_SESSION => FALSE);
END;
/

Now look at the job run details for the job:

"LOG_ID" "LOG_DATE"                               "JOB_NAME" "JOB_SUBNAME" "STATUS"    "ERROR#" "ACTUAL_START_DATE"                                "RUN_DURATION"
"1548"   "14-JUN-20 12.15.46.744612000 AM -04:00" "TEST_JOB" "CHAIN_STEP3" "SUCCEEDED" "0"      "14-JUN-20 12.15.41.708043000 AM AMERICA/NEW_YORK" "+00 00:00:05.000000"
"1544"   "14-JUN-20 12.15.46.746544000 AM -04:00" "TEST_JOB" "CHAIN_STEP2" "SUCCEEDED" "0"      "14-JUN-20 12.15.41.690404000 AM AMERICA/NEW_YORK" "+00 00:00:05.000000"
"1546"   "14-JUN-20 12.15.46.748830000 AM -04:00" "TEST_JOB" "CHAIN_STEP1" "SUCCEEDED" "0"      "14-JUN-20 12.15.41.690891000 AM AMERICA/NEW_YORK" "+00 00:00:05.000000"
"1550"   "14-JUN-20 12.15.46.968592000 AM -04:00" "TEST_JOB" ""            "SUCCEEDED" "0"      "14-JUN-20 12.15.41.574115000 AM AMERICA/NEW_YORK" "+00 00:00:05.000000"

Note that:

The job starts at "12.15.41.574115000" (ACTUAL_START_DATE, Line 1550).
Each job step starts within a fraction of a second of the overall job start (as recorded in ACTUAL_START_DATE for each step in lines 1544, 1546, and 1548), and completes in the expected 5 seconds.
The overall job completes at "14-JUN-20 12.15.46.968592000" (LOG_DATE, Line 1550) with a total duration of 5 seconds to complete all three steps.
Note that rule processing may add a tiny bit of overhead to the total execution time for the chain.

Thank you! can we generate an alert after all these procedures finishes execution? — ZZ3111, Jun 16 '20 at 23:48
You can have the Scheduler Job raise an alert and send an email when it completes, or you could have plsql procedures generate email at key points in the execution. — pmdba, Jun 16 '20 at 23:56
I have 12 procedures and my first procedure is for initialization after initialization only I can run the remaining 10 procedures in parallel using chaining concept, finally, I have scoring proc which needs the output of all previous procedures, here using email alert would be useful by production point of view? — ZZ3111, Jun 17 '20 at 00:00
If it were me I'd just have the job raise a completion event and send the email when it completes all of the procedures, but that's just me. Do what works best for you. — pmdba, Jun 17 '20 at 00:03
@pmdba How can I keep the parallel programs running indefinitely without having to re-trigger the chain? We want to do this so that if some parallel jobs finish early, then we want to make sure we don't wait until all others complete for their next invocation.. Thoughts? — Rookie, Jul 05 '22 at 20:54
You would have to retrigger the chain in some way, using a set schedule or a triggering event. — pmdba, Jul 06 '22 at 01:41
I came across this answer in a Google search. I was going to upvote it, because it is helpful, except that I had already upvoted it. Must have been the from the last time I was doing something with Job Chains :) — APC, Aug 09 '22 at 12:53

score 0 · Answer 2 · edited Jan 09 '22 at 14:52

0

The parallel processing can be achieved using the schedule job chain.

create schedule programs with the procedure function you want to run,
create a scheduler chain and define steps and rules,
create a job/scheduler job to call the scheduler chain

edited Jan 09 '22 at 14:52

user3666197

1
6
50
92

answered Jan 02 '22 at 22:58

mubasheer

11
2

Run procedures in parallel - Oracle PL/SQL

2 Answers2

Linked