Run Oracle Stored Procedure Parallel

DBMS_JOB is a nice way to execute jobs in parallel because you control the degree of concurrency. You could set up to 36 job queue processes (in 8i) or 1,000 (in 9i) and that would be the degree of concurrency.

The performance benefits could be substantial. For example, I did a performance test on two 1-million-row tables with four indexes to rebuild. The following snippet is the first block of code I used, which rebuilds indexes with NOLOGGING PARALLEL options serially using the RebuildIndex procedure of my Maintenance package:

declare vStart number;
begin
vStart := dbms_utility.get_time;

NCR_NZ_REPLAY_PUB_PKG_1.replay_all(23859,23985);
'NCR_NZ_REPLAY_PUB_PKG_1.replay_all(23994,24120);

dbms_output.put_line('Elapsed time: ' || round((dbms_utility.get_time - vStart)/100, 2) || ' hsecs' );
end;
/

The second block of code rebuilds indexes in parallel using DBMS_JOB:

declare

vJob number;
x_return_status VARCHAR2(100);

begin

dbms_job.submit(job => vJob, what => 'NCR_NZ_REPLAY_PUB_PKG_1.replay_all(23859,23985);');
dbms_output.put_line('First Job ' || to_char(vJob));

dbms_job.submit(job => vJob, what => 'NCR_NZ_REPLAY_PUB_PKG_1.replay_all(23994,24120);');
dbms_output.put_line('Second Job ' || to_char(vJob));

commit;
end;
/