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;

Justin Cave · Accepted Answer · 2013-01-25T22:38:19.480

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;

Thank you @Justin; would you mind checking my edit to see if I did the dbms_scheduler correctly? — Matthew Moisen, Jan 25 '13 at 22:26
@MatthewMoisen - Updated my answer with a few suggestions on the `dbms_scheduler` side. — Justin Cave, Jan 25 '13 at 22:38
Great; thanks for the response. I was just doing Dynamic SQL to save me from writing two lines of code. — Matthew Moisen, Jan 25 '13 at 22:48
@MatthewMoisen - Be aware that the second parameter to `run_job` is a boolean not a string. In your most recent update, you're passing in a string rather than a boolean. — Justin Cave, Jan 25 '13 at 22:51
Another question: why does `dbms_job.submit(null, '...');` cause an error while a `dbms_job.submit(l_jobno, '...');` , where l_jobno is an uninitialized variable, not cause an error? — Matthew Moisen, Jan 25 '13 at 23:15
And is `dbms_job.submit(l_jobno, 'job_other_pkg.other_proc;'); fine or does it require begin and end ? — Matthew Moisen, Jan 25 '13 at 23:18
@MatthewMoisen - The first parameter to `dbms_job.submit` is an `OUT` parameter. That lets `dbms_job` tell you the job number that was just created (which you would generally want to record for monitoring purposes). You can't pass in a literal as the `OUT` parameter for any procedure. I'm pretty sure the `what` parameter is required to be a valid PL/SQL block so I'm pretty sure the `BEGIN` and `END` are required but I wouldn't guarantee that. — Justin Cave, Jan 25 '13 at 23:21

score 1 · Answer 2 · edited May 23 '17 at 12:32

1

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)

edited May 23 '17 at 12:32

Community

1
1

answered May 12 '16 at 08:27

J. Chomel

8,193
15
41
69

Parallelizing calls in PL/SQL

2 Answers2

Linked