Run Multiple Functions and/or Procedures in Parallel
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
Generic Metrics Collection Instrumentation Library
The SQL language is designed to run statements in serial fashion: The second statement does not begin to run until the first one completes. The Oracle procedural language, PL/SQL, similarly has no native capacity to run multiple statements in parallel.

This demonstration shows a very simple way, utilizing jobs that do not run in the parent session, to run in parallel. To do this we will write two stored procedures: Both procedures perform 3 inserts with a one second delay before each insert.

The first step is to create a logging table as since the parallel run will utilize jobs running in three  separate sessions.
CREATE TABLE t (
proc_name  VARCHAR2(10),
stamp_time DATE);
Here are the two stored procedures. The first, run_serial, uses a FOR loop to sleep for a total of three seconds and perform three inserts.

The second procedure, run_parallel, also contains three one second sleeps and performs three inserts. The difference is that it does so by creating three autonomous scheduler jobs.
CREATE OR REPLACE PROCEDURE run_serial AUTHID DEFINER IS
 rType t.proc_name%TYPE := 'SERIAL';
BEGIN
  FOR i IN 1..3 LOOP
    dbms_lock.sleep(1);
    INSERT INTO T VALUES (rType, SYSDATE);
  END LOOP;
  COMMIT;
END run_serial;
/


CREATE OR REPLACE PROCEDURE run_parallel AUTHID DEFINER IS
 jName VARCHAR2(2);
BEGIN
  FOR i IN 1..3 LOOP
    jName := 'J' || TO_CHAR(i);
    dbms_scheduler.create_job(jName, 'PLSQL_BLOCK',
                              'DECLARE
                                rType VARCHAR2(10) := ''PARALLEL'';
                               BEGIN
                                 dbms_lock.sleep(1);
                                 INSERT INTO t VALUES (rType, SYSDATE);
                               END; ',
                              start_date=>SYSDATE);
    dbms_scheduler.run_job(jName, FALSE);
  END LOOP;
  COMMIT;
END run_parallel;
/
The following runs the two procs and examines the resulting output.
exec run_serial;

exec run_parallel;

SQL> SELECT * FROM t;

PROC_NAME STAMP_TIME
---------- --------------------
SERIAL     30-DEC-2011 20:34:42
SERIAL     30-DEC-2011 20:34:43
SERIAL     30-DEC-2011 20:34:44

PARALLEL   30-DEC-2011 20:34:49
PARALLEL   30-DEC-2011 20:34:49
PARALLEL   30-DEC-2011 20:34:49
The first three records, created by the serial procedure clearly show the one second separation between the inserts. The second appears to show all three running at the exact same point-in-time but what is hidden from sight is that each took its one second sleep in parallel so the total run-time for all three is just a fraction of a second more than the one second sleep.

The secret to how this works is that it uses DBMS_SCHEDULER.RUN_JOB's second parameter, use_current_session, which normally defaults to TRUE to run each job in a separate session. As each of the three sleeps and inserts occur in a separate session they run in parallel and do not depend on the completion of the first for the second to begin execution.
 
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved