Run Oracle Stored Procedures in Parallel—Inside the Database-4 : Page 4

JobsPkg Demonstration
To demonstrate the JobPkg code work, I created a DemoPkg package with one simple procedure, Sleep. This procedure "sleeps" for the number of seconds passed as a parameter (click hereto download the package).

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 Sleepprocedures in parallel:

1. DemoPkg.Sleep(10) will sleep for 10 seconds and send an alert.
2. DemoPkg.Sleep.Sleep(15) will sleep for 15 seconds.
3. DemoPkg.Sleep.Sleep(20) will sleep for 20 seconds.

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. Its output shows that the execution time is a little more than 20 seconds, as expected:

Received alert: DEMOPKG.SLEEP(10) with message: DemoPkg.Sleep(10) completed.
Received alert: DEMOPKG.SLEEP(15) with message: DemoPkg.Sleep(15) completed.
Received alert: DEMOPKG.SLEEP(20) with message: DemoPkg.Sleep(20) completed.

PL/SQL procedure successfully completed.

Elapsed: 00:00:22.05

When Would I Use This Technique?
During a recent project, I had to speed up legacy code that was so slow running it overnight didn't allow enough time for it to complete. It was an intensive data-manipulation batch process that consisted of 14 major steps. All the steps were executed serially. Looking at the dependencies, I found the following:

• Steps 1–5 were independent.
• Steps 6–9 were also independent, but they relied on the results from Steps 1–5.
• Steps 10–11 had to be run after Steps 6–9 and one after another.
• Steps 12–14 were independent, but they relied on results from Step 11.

Using the JobsPkg APIs, I modified the code by creating the following four code blocks and executing them in sequence:

• Block 1: execute five steps (1–5) in parallel
• Block 2: execute three steps (6–9) in parallel
• Block 3: execute steps 10 and 11 serially
• Block 4: execute the last three steps (12–14) in parallel

Simply by restructuring the code, I was able to cut execution time by 60 percent! And I hadn't even touched the code itself yet, just an order of execution.

You can easily execute stored procedures in parallel—and reap significant performance benefits—by using code like that in the JobsPkg package. You can use the package as a base and add more functionality based on your own business rules and requirements.

Previous Page

• Download The Code for this Article