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. |
|
