Question:
Are the multiple queries in a stored
procedure fired in parallel or sequentially? I have a
PL/SQL stored procedure with four insert statements (into
fdifferent tables), and I want the insert statements to be
submitted and executed in parallel. How can I execute
DML statement in parallel in Oracle?
Answer:
Oracle has a wealth of parallel
features including parallel query, parallel DML, but it is
also possible to perform parallel processing on Oracle using
PL/SQL.
You could place each on in a separate stored procedure
and then use the UNIX/Linux "nohup" command to submit them
in parallel.
If using a
shell script environment (ksh, bash), you can invoke
parallel PL/SQL programs with the nohup command:
nohup
sqlplus system/manager exec xxx &
nohup sqlplus
system/manager exec yyy &
nohup sqlplus system/manager
exec zzz &
The
ampersand invokes the sqlplus in the background, such that
the nohup commands ate invoked serially, but execute in
parallel. For return processing, you can write a simple
daemon process to verify positive return codes ("process
executed successfully"),
Within
PL/SQL you can invoke parallel threads using parallel
pipelined functions. You can also submit jobs in
parallel with dbms_scheduler.run_job or
dbms_scheduler.submit:
CREATE PACKAGE BODY pkg IS
CREATE PROCEDURE do_parallel_execution
IS
BEGIN
DBMS_SCHEDULER.RUN_JOB('job_other_pkg.other_proc', false);
DBMS_SCHEDULER.RUN_JOB('job_other_pkg2.other_proc2', false);
DBMS_SCHEDULER.RUN_JOB('job_other_pkg3.other_proc3', false);
END;
Or
you cam use the later dbms_scheduler package to submit
parallel PL/SQL jobs:
CREATE PACKAGE BODY pkg IS
CREATE PROCEDURE run_in_parallel
IS
l_jobno pls_integer;
BEGIN
dbms_job.submit(l_jobno, 'begin other_pkg.other_proc; end;'
);
dbms_job.submit(l_jobno, 'begin
other_pkg2.other_proc2; end;' );
dbms_job.submit(l_jobno, 'begin other_pkg3.other_proc3;
end;' );
END;
END;
For parallelizing PL/SQL using parallel pipelined
functions, see my notes
here on using parallel pipelined functions.
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|