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;

Totemism

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;

Angkor

Thank you @Justin; would you mind checking my edit to see if I did the dbms_scheduler correctly? – Totemism

@MatthewMoisen - Updated my answer with a few suggestions on the dbms_scheduler side. – Angkor

Great; thanks for the response. I was just doing Dynamic SQL to save me from writing two lines of code. – Totemism

@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. – Angkor

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? – Totemism

And is `dbms_job.submit(l_jobno, 'job_other_pkg.other_proc;'); fine or does it require begin and end ? – Totemism

@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. – Angkor

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)

Shererd