Solved

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

Posted on 2008-06-17
5
2,250 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
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Finace 3 67
oracle query help 29 77
export Oracle diagram from Oracle DB including VIEWS 8 105
Oracle Listener Not Starting 11 27
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

867 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