Parallelizing calls in PL/SQL

Question

I have a package with a proc that will execute a number of other procedures, like so:

CREATE PACKAGE BODY pkg IS
    CREATE PROCEDURE do
    IS
    BEGIN
        other_pkg.other_proc;
        other_pkg2.other_proc2;
        other_pkg3.other_proc3;
    END;
END;

Is there any way to have the procedures execute in parallel rather than serially?

EDIT:

Is this the proper way to use DBMS_SCHEDULER in this instance:

CREATE PACKAGE BODY pkg IS
    CREATE PROCEDURE do
    IS
    BEGIN
        DBMS_SCHEDULER.CREATE_JOB('job_other_pkg.other_proc', 'STORED_PROCEDURE', 'other_pkg.other_proc;');
        DBMS_SCHEDULER.RUN_JOB('job_other_pkg.other_proc', FALSE);
        -- ...
    END;
END;

Answer 1

You can use the dbms_job (or dbms_scheduler) package to submit jobs that will run in parallel. If you are using dbms_job, submitting the jobs will be part of the transaction so the jobs will start once the transaction completes.

CREATE PACKAGE BODY pkg IS
    CREATE PROCEDURE do
    IS
      l_jobno pls_integer;
    BEGIN
        dbms_job.submit(l_jobno, 'begin other_pkg.other_proc; end;' );
        dbms_job.submit(l_jobno, 'begin other_pkg2.other_proc2; end;' );
        dbms_job.submit(l_jobno, 'begin other_pkg3.other_proc3; end;' );
    END;
END;

If you are using dbms_scheduler, creating a new job is not transactional (i.e. there would be implicit commits each time you created a new job) which may cause problems with transactional integrity if there is other work being done in the transaction where this procedure is called. On the other hand, if you are using dbms_scheduler, it may be easier to create the jobs in advance and simply run them from the procedure (or to use dbms_scheduler to create a chain that runs the job in response to some other action or event such as putting a message on a queue).

Of course, with either solution, you'd need to then build the infrastructure to monitor the progress of these three jobs assuming that you care when and whether they succeed (and whether they generate errors).

If you are going to use DBMS_SCHEDULER

• There is no need to use dynamic SQL. You can ditch the EXECUTE IMMEDIATE and just call the DBMS_SCHEDULER package's procedures directly just like you would any other procedure.
• When you call RUN_JOB, you need to pass in a second parameter. The use_current_session parameter controls whether the job runs in the current session (and blocks) or whether it runs in a separate session (in which case the current session can continue on and do other things). Since you want to run multiple jobs in parallel, you would need to pass in a value of false.
• Although it is not required, it would be more conventional to create the jobs once (with auto_drop set to false) and then just run them from your procedure.

So you would probably want to create the jobs outside the package and then your procedure would just become

CREATE PACKAGE BODY pkg IS
    CREATE PROCEDURE do
    IS
    BEGIN
        DBMS_SCHEDULER.RUN_JOB('job_other_pkg.other_proc', false);
        DBMS_SCHEDULER.RUN_JOB('job_other_pkg2.other_proc2', false);
        DBMS_SCHEDULER.RUN_JOB('job_other_pkg3.other_proc3', false);
    END;
END;

Answer 2

Another solution is to hack Oracle's SQL parallelism mechanism. See answer to How to execute a stored procedure in a different session in same time in pl/sql .

It uses William Robertson's great solution Parallel PL/SQL launcher.

(tested with Oracle 10g)