Solved

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

Posted on 2008-06-17
5
2,245 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
5 Comments
 
LVL 4

Expert Comment

by:Fekrat
Comment Utility
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
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now