Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to call multiple instance of the same procedure in PL/SQL

HawkerHunterFeb 18 2021

Hello Friends,
I have a stored procedure which is meant to process huge number of data.
Is it possible to call multiple instances of the same procedure from a scheduled procedure to run in parallel so that the overall processing time reduces.
Regards
Hawker
This post has been answered by GregV on Feb 22 2021
Jump to Answer

Comments

BEDE

Generally starting in parallel several things fron one database session is achieved via jobs. Read the documentation for dbms_scheduler.
Look also into the documentation for dbms_parallel_execute.
That's as much as I can tell so far. If you provide more info as to what you actually have to do, someone may come with a concrete solution.

1 person found this helpful
Solomon Yakobson

Not sure what do you mean "from a scheduled procedure"? Anyway, take a look at dbms_parallel_execute package.
SY.

Billy Verreynne

Look at my posting https://community.oracle.com/tech/developers/discussion/3626286/parallelize-procedure-call#latest where I touch on the differences between parallel processing and threading.
The former can be done using Parallel Query (PQ), using DBMS_PARALLEL_EXECUTE, and with parallel (PQ driven) PL/SQL pipelines.
The latter I gave a very basic example of in that posting, and skipped issues such as IPC, semaphores, and so on.
What exactly does your PL/SQL do? Is it thread/parallel safe? Is it well instrumented? How are failure and restarts dealt with?

HawkerHunter

Hi friends,
thanks for your replies. Yes I need something like parallel processing. I have not done this earlier so I thought before proceeding, it would be good to take some expert guidance. I shall definitely go through the documentation for dbms_parallel_execute and also Billy Verreynne's post.
The following is what I am trying to achieve.
I have a table ITEMS_TO_BE_PROCESSED which has following fields:
ITEM_CODE NUMBER,
START_DATE DATE,
END_DATE DATE,
STATUS CHAR(1),
CREATION_DATE DATE,
CREATED_BY VARCHAR2(50)
The user shall add the items to be processed. The table captures the Item Id and Creation date and created by.
I have created a Procedure "PROCESS_ITEMS" which loops through the "ITEMS_TO_BE_PROCESSED" table and processes each item serially.
The moment it starts processing an item, it puts the date and time in the START_DATE field and commits immediately. Processing of each item takes about 15 to 20 minutes. There may be around 50 or more such items added to the ITEMS_TO_BE_PROCESSED table.
So, it may take more than 10 hours to complete the processing of all the items. We can schedule the procedure "PROCESS_ITEMS" to start execution during non-business hours.
But we want to call more than one instance of this procedure to process the items.
When one instance of the procedure starts processing, it shall check the START_TIME field. If it has any value then that record shall be skipped. In this way we shall avoid same item to be processed by the different instances of "PROCESS_ITEMS" procedure.
We would also like to have a control of the number of instances of the "PROCESS_ITEMS" procedure to run on different days through a parameter set by an admin user on different days. E.g.: On day 1, the admin user updates the parameter to 2. So, two instances of the "PROCESS_ITEMS" procedure shall run.
If the user sets the parameter to 4 on another day, then during non business hours it shall run 4 instances of the "PROCESS_ITEMS" procedure and so on.
Thanks and Regards
Hawker

GregV

Hi,
So theI TEMS_TO_BE_PROCESSED table contains the list of items to be processed in an asyncrhonous fashion. If I understood properly there can be several rows with the same item code in this queue table. If I were you I'd add a PK called something like ITEM_QUEUE_ID, populated by a sequence (or identity). This way it will be easier to update the END_DATE and STATUS columns using this PK.
Instead of checking the START_TIME for processing a row, I guess it is more proper to check the STATUS, isn't it?
If the underlying procedure that processes the item is the same for all items (only input parameters change), then you could use DBMS_SCHEDULER jobs based on the same Scheduler program. A Scheduler program serves as a "template" for a job, that is, it refers to a stored procedure with defined arguments. When you create the job, you use the program as a base, and define the argument values for the job.

HawkerHunter

Hi GregV,
Thanks for your reply. The ITEMS_TO_BE_PROCESSED table shall be populated by users who would want to schedule their items to be processed during non-business hours and yes you are right I have a schedule id as the primary key in the table being populated by a sequence.
Yes the status can also be used as a flag to mark that the record is either under process or completed.
Yes the list of items shall be processed in an asynchronous fashion. I have the procedure which I can schedule to start processing.
But I want to call more than one instance of the process to handle the job so that it is processed parallel and saves time.
Regards
Hawker

GregV
Answer

Yes the idea is to run jobs so that you can process your items in parallel.
As I said, you can create a Scheduler program (you do this just once):

begin
dbms_scheduler.create_program(program_name => 'PROCESS_ITEMS_PRG', program_type => 'stored_procedure', program_action => 'your_pkg.your_processing_proc', number_of_arguments => 1);
dbms_scheduler.define_program_argument(program_name => 'PROCESS_ITEMS_PRG', argument_position => 1, argument_type => 'NUMBER');
dbms_scheduler.enable('PROCESS_ITEMS_PRG');
end;
/

Then, in your procedure that checks the items to be processed, you can have something like :

cursor c_items
is select schedule_id
from items_to_be_processed
where status = <value for pending>
for update of status;

rec_items c_items;

v_job_name varchar2(30 char);

begin
  for rec_items in c_items loop
    update  items_to_be_processed
    set status = <value_for_processing>
    where current of c_items;

    v_job_name := 'PROCESS_ITEMS_JOB' || rec_items.schedule_id;
    dbms_scheduler.create_job(job_name    => v_job_name,
                              program_name => 'PROCESS_ITEMS_PRG',
                              job_style   => 'LIGHTWEIGHT'
                             ); 
    dbms_scheduler.set_job_argument_value(job_name => v_job_name, argument_position => 1, argument_value => rec_items.schedule_id);
    dbms_scheduler.enable(name => v_job_name); 
  end loop;

This is just some sample code not tested. This will run as many pending jobs. Of course you can a clause to limit the number of jobs running (for example "and rownum <= 4").
The dbms_scheduler.create_job procedure uses the program to create a job. Then you setup the argument value to the schedule_id and use the dbms_scheduler.enable to enable the job so that it runs immediately.
dbms_scheduler.create_job does an implicit commit so the status should be updated to signal the item is being processed.

Marked as Answer by HawkerHunter · Mar 18 2021
1 person found this helpful
HawkerHunter

Hi Greg,
thanks for showing me some sample code. It has really helped me.
Thanks and Regards
Hawker

1 - 8

Post Details

Added on Feb 18 2021
#procedure
8 comments
3,778 views