Forum Stats

  • 3,781,417 Users
  • 2,254,514 Discussions
  • 7,879,682 Comments

Discussions

Categories

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

HawkerHunter
HawkerHunter Member Posts: 498 Bronze Badge
in SQL & PL/SQL

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

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • GregV
    GregV Member Posts: 3,075 Gold Crown
    Accepted 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.

    HawkerHunter
    · Share on TwitterShare on Facebook

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,327 Gold Trophy

    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.

    HawkerHunter
    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,009 Red Diamond

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

    SY.

    · Share on TwitterShare on Facebook
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,648 Red Diamond

    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?

    · Share on TwitterShare on Facebook
  • HawkerHunter
    HawkerHunter Member Posts: 498 Bronze Badge

    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

    · Share on TwitterShare on Facebook
  • GregV
    GregV Member Posts: 3,075 Gold Crown

    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.

    · Share on TwitterShare on Facebook
  • HawkerHunter
    HawkerHunter Member Posts: 498 Bronze Badge

    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

    · Share on TwitterShare on Facebook
  • GregV
    GregV Member Posts: 3,075 Gold Crown
    Accepted 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.

    HawkerHunter
    · Share on TwitterShare on Facebook
  • HawkerHunter
    HawkerHunter Member Posts: 498 Bronze Badge

    Hi Greg,

    thanks for showing me some sample code. It has really helped me.

    Thanks and Regards

    Hawker

    · Share on TwitterShare on Facebook