In this article I will discuss how stored procedures can run in parallel inside an oracle database. We can use one of Oracle's pre-packaged APIs, the DBMS_JOB package, to submit multiple jobs to a job queue and then run them in parallel.

Oracle Pre-packaged APIs:

Oracle databases provide a rich set of pre-packaged APIs in the form of Supplied PL/SQL Packages. Oracle 9.2 documentation contains references to 101 packages, although issuing the following query returns 320: Access requires an Oracle.com login.
select count(*) from dba_objects where object_type = 'PACKAGE BODY' and owner = 'SYS'

PL/SQL packages:

Most of supplied PL/SQL Packages provide a useful API for this task which is already developed and tested. The DBMS_JOB package is oracle's native scheduler that provides a set of APIs for submitting and managing jobs in a queue.

Submitting multiple jobs:

If multiple jobs are submitted to a job queue and scheduled them all to start immediately, then they will start and run in parallel. The useful API in this case is DBMS_JOB.SUBMIT. It submits jobs to a job queue for execution. The next_date parameter of DBMS_JOB.SUBMIT determines the next date when the job will be run. It has a default value of SYSDATE, so if you don't specify a date, the job will start immediately. However, the job will be submitted to the job queue only after commit, which makes this process transaction-based.

Load Balancing:

A big advantage of the DBMS_JOB-based solution is load balancing which determines the maximum number of concurrent jobs the solution can execute. It controls how many jobs can be run simultaneously.

Control Concurrency:

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 9 i ) and that would be the degree of concurrency.

Rebuilding Indexes:

Below code rebuilds indexes with NOLOGGING PARALLEL options serially using the RebuildIndex procedure:

declare
vStart number;
begin
vStart := dbms_utility.get_time;
Maintenance.RebuildIndex('ORDERS_PK', 'NOLOGGING PARALLEL');
Maintenance.RebuildIndex('SALES_PK', 'NOLOGGING PARALLEL');
Maintenance.RebuildIndex('ORDERS_AK1','NOLOGGING PARALLEL');
Maintenance.RebuildIndex('SALES_AK1', 'NOLOGGING PARALLEL');
dbms_output.put_line('Elapsed time: ' ||
round((dbms_utility.get_time - vStart)/100, 2) || ' hsecs' );
end;
/

Rebuilding indexes in parallel:

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

declare
vJob number;
begin
dbms_job.submit(job => vJob,
what => 'Maintenance.RebuildIndex(''ORDERS_PK'',
''NOLOGGING PARALLEL'');');
dbms_output.put_line('Index ORDERS_PK: Job ' || to_char(vJob));
dbms_job.submit(job => vJob,
what => 'Maintenance.RebuildIndex(''SALES_PK'',
''NOLOGGING PARALLEL'');');
dbms_output.put_line('Index SALES_PK: Job ' || to_char(vJob));
dbms_job.submit(job => vJob,
what => 'Maintenance.RebuildIndex(''ORDERS_AK1'',
''NOLOGGING PARALLEL'');');
dbms_output.put_line('Index ORDERS_AK1: Job ' || to_char(vJob));
dbms_job.submit(job => vJob,
what => 'Maintenance.RebuildIndex(''SALES_AK1'',
''NOLOGGING PARALLEL'');');
dbms_output.put_line('Index SALES_AK1: Job ' || to_char(vJob));
commit;
end;

It executes almost immediately but it doesn't mean the indexes got rebuilt that fast. Indexes get built in the background. If you keep querying for a while, some jobs will finish and disappear from the query. When the query returns no rows, the index rebuilding jobs are complete. The second block of code, which rebuilds indexes in parallel, execute 30–35 percent faster than the first block. That's a significant performance gain.

Determining execution completion:

Once the execution is completed, it needs a procedure that automates the process for determining execution completion. The way it currently works is not elegant at all: opening another SQL*Plus session and issuing the same query every 10 seconds from that session.

Polling user_jobs View:

A possible solution would be a procedure that polls the user_jobs view in a loop until it returns no rows.

Oracle Packages for database communication:

Oracle provides two packages to communicate between different database sessions: DBMS_ALERT and DBMS_PIPE. I've chosen DBMS_ALERT, because when a job completes, it sends an "I'm done" signal to the main block of code that initially submitted that job to a job queue.

Alerts:

The DBMS_ALERT package supports asynchronous notification of database events (alerts). It provides APIs to send alerts, register for alerts, and wait to receive them. The procedures this Solution uses are SIGNAL, REGISTER, and WAITFORANY:

DBMS_ALERT.SIGNAL:

The DBMS_ALERT.SIGNAL procedure signals an alert. The SIGNAL call goes into effect only when the transaction in which it is made commits. If the transaction rolls back, SIGNAL has no effect. All sessions that have registered interest in this alert are notified. (If the application does not require transaction-based alerts, the DBMS_PIPE package may provide a useful alternative.)

DBMS_ALERT.REGISTER:

The DBMS_ALERT.REGISTER procedure registers interest in an alert.

DBMS_ALERT.WAITFORANY:

The DBMS_ALERT.WAITFORANY procedure waits for any of the alerts for which the current session is registered to occur.

DBMS_ALERT Package:

The DBMS_ALERT package helps in carrying out the following procedure:

1. Runs N jobs in parallel
2. Registers for N alerts (one per job)
3. Submits the jobs to a job queue
4. Waits to be notified by all of the alerts

DemoPkg package:

Consider a DemoPkg package with one simple procedure, Sleep. This procedure "sleeps" for the number of seconds passed as a parameter. The following code shows the DemoPkg package specification and body:

create or replace package DemoPkg is

procedure Sleep
(
pTime integer
);
end DemoPkg;
/
create or replace package body DemoPkg is
procedure Sleep
(
pTime integer
)
is
begin
dbms_lock.sleep(pTime);
JobsPkg.SignalCompletion('DemoPkg.Sleep(' ||
to_char(pTime) || ')');
end Sleep;
end DemoPkg;
/

You can use this package to execute code similar to the parallel execution block in the Control Concurrency section and run three Sleep procedures in parallel. The following code executes these procedures:

set timing on
declare
vJobs JobsPkg.tArrayJob;
begin
vJobs(1) := 'DemoPkg.Sleep(10)';
vJobs(2) := 'DemoPkg.Sleep(15)';
vJobs(3) := 'DemoPkg.Sleep(20)';
JobsPkg.Execute(vJobs);
JobsPkg.WaitForCompletion(vJobs);
end;
/

Unlike the parallel execution block code from the Control Concurrency section, the above code does not return control immediately. It waits for completion of all the jobs from the vJobs array.

Conclusion:

You can easily execute stored procedures in parallel and get in significant performance benefits. You can add more functionality based on your own business rules and requirements.

 More Database Articles
   Database Security: Step by step guideline
   The Secrets of Handling Tricky Duplicate Data!!
   The power of Oracle Diagnostic Tools!
   Improve performance by Optimizing SQL!!
   Exciting Oracle 11g features you should not miss to know!!