Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2008-06-17
5
Medium Priority
?
2,292 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
  • 3
3 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 2000 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

Independent Software Vendors: 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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month13 days, 7 hours left to enroll

581 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