Solved

How can I execute stored procedures in parallel from a stored procedure?

Posted on 2008-06-17
5
2,264 Views
Last Modified: 2013-12-07
I need to run in parellel multiple stored procedures with in a stored procedure to attain better performance. Can someone please provide sample code?
0
Comment
Question by:psaju
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 4

Expert Comment

by:Fekrat
ID: 21810566
Use one of Oracle's prepackaged APIs, the DBMS_JOB package, to submit multiple jobs to a job queue and then run them in parallel.
0
 
LVL 4

Expert Comment

by:Fekrat
ID: 21810597
This is an example code i found on the web for a procedure that is not done in parallel
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;
/

Open in new window

0
 
LVL 4

Accepted Solution

by:
Fekrat earned 500 total points
ID: 21810599
The following is the version of the code after using the dbms_job package. Hope this helps
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;
/

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

731 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question