Use DBMS_JOB or DBMS_SCHEDULER if the procedures are not depending on other procedures beingf finished first.
Each job will run in it's own session.
Make sure to pick the correct value for parameter job_queue_processes, alert your DBA if necessary
Examples can be found on http://asktom.oracle.com
It still would work, you can execute p1 in 300 times at the same time by submitting 300 jobs.
But my guess is that your DBA will no longer like you.
Since I know nothing about the data and volumes, not what your procedures are doing, it's kind of hard to provide more clues.
Perhaps a redesign is needed instead of applying fixes/patches...
Pseudo code wise:
while Work-To-Do loop
storeID := GetNextStore;
..create a chunk of 30
..use dbms_parallel_execute to execute: begin ProcessStore( storeId, :start_id, :end_id ); end;
.. wait for parallel processes to complete
The chunking requires the numbers 1 to 30 - where each number represents the procedure to execute. E.g. 5 chunks, with chunk 1 containing range 1 to 6. Etc.
The ProcessStore proc will look something like the following:
procedure ProcessStore( storeID number, processIdStart number, processIdEnd number ) is
for i in processIdStart..processIDEnd loop
when i = 1 then p1(storeID);
when i = 30 then p30(storeID);
Thus this proc does a single or range of procedures for a store ID. And DBMS_PARALLEL_EXECUTE is used to set up the number of chunks (process ranges per thread/job) to execute.
Did not see that. But the basic remains the same. Manually "chunk" and then manually spawn jobs (via DBMS_JOB for example) and use the job id to check user_jobs for when it completes.
DBMS_PARALLEL_EXECUTE makes it simpler - but the basic moving parts needed for parallel PL/SQL processing this fashion exists with Oracle 8, if not earlier.
thanks for the reply.
But i missed my point , p1 should be completed before p2.
And what if p1 failed ?
Well, in addition of what already said by Billy and Hoek, I would also investigate, if you can afford it, the OEM job scheduling/ordering capabilities, it's quite interesting. Especially OEM 12c (certified with target 22.214.171.124 and above).