This discussion is archived
5 Replies Latest reply: Feb 8, 2013 8:09 PM by onedbguru RSS

Run Procedures in Parallel

spalato76 Newbie
Currently Being Moderated
Hi All,

I have a procedure, seen below, that essentially checks every day whether a source table has a maximum date greater than my target table. If the Maximum Date in the Source Table is greater, that signifies that I need to update my source tables with new data. The procedure runs perfectly, I've set it up to run as a job every 24 hours. Here's the problem. Currently, the way the procedure is written all the procedures are executed sequentially which makes the whole process much longer when in fact there are a few procedures that can run in parallel and I unfortunately don't know how to set it up to run in parallel. Here's the order of events --->

If the max source date is greater than the target max date (if condition), then the procedures start executing (procedure list below). This is how I would like it set up:

1 PROC_CPIRUDBA_IP_DIAG_PROC_3NF
2 INSERT_PROV_ACSC_IND_REF
3 CREATE_PROV_ACSC_INDICATOR_RPT (runs 7 times but with different input parameters)
4 MERGE_CMG_DASHBOARDS
5 INSERT_ELOS_ALOS_DASHBOARD
6 INSERT_FACILITY_DASHBOARD

Currently, they all run in sequence. Would like it set up so that:

Procedures 1,4,5,6 run at the same time
Procedure 2 has to wait for Procedure 1 to be done before running
Procedure 3 has to wait for Procedure 2 to be done before running

Here's my code below:
CREATE OR REPLACE procedure dad_refresh
AUTHID CURRENT_USER IS

begin

declare
  max_src_dt date; -- Source Table
  max_tgt_dt date; -- Target Table
  max_tgt_dt_fyr  VARCHAR2(30);
  v_str_temp      varchar2(3000);
  START_dt  VARCHAR2(30);
  END_DT VARCHAR2(30);
  v_date_start    date    := SYSDATE;
  elos_max_dt date;
  facility_max_dt date;
  
     
  begin
        dbms_output.disable;
        dbms_output.enable(1000000);
        dbms_output.put_line('============================================================================');
        dbms_output.put_line('Start DAD Refresh: '|| TO_CHAR(SYSDATE,'yyyy-Mon-dd hh24:mi:ss'));
        
   select  MAX( is_date(disdate,'yyyymmdd'))
   into max_src_dt
   from        ahsdata.ahs_ip_doctor_dx;
   
   select  MAX(disch_dt)
   into max_tgt_dt
   from        prov_acsc_indicator_ref;
   
   select  MAX(trunc(discharge_dt))
   into elos_max_dt
   from  elos_vs_alos_dashboard;
   
  select  MAX(trunc(discharge_dt))
   into facility_max_dt
   from  facility_profile_dashboard;
   
   select TO_CHAR( MAX(disch_dt)+1,'DD-MON-YYYY')
   into START_dt
   from        prov_acsc_indicator_ref;
   
   select TO_CHAR(fiscal_year_start(MAX(disch_dt)),'DD-MON-YYYY')
   into max_tgt_dt_fyr
   from        prov_acsc_indicator_ref;
   
   SELECT '01-APR-2020'
    INTO END_DT
    FROM DUAL;
    
         dbms_output.put_line('============================================================================');
        dbms_output.put_line('Source and Target Table Date Checks');
        dbms_output.put_line('============================================================================');
        dbms_output.put_line('Maximum Source Date : '||max_src_dt);
        dbms_output.put_line('Maximum Target Date : '||max_tgt_dt);  
        dbms_output.put_line('Target Fiscal Year Start : '||max_tgt_dt_fyr); 
        dbms_output.put_line('============================================================================');
    if max_src_dt = max_tgt_dt then 
                        dbms_output.put_line('DAD Refresh Required: No'); 
                        dbms_output.put_line('============================================================================');
                        dbms_output.put_line('Table Counts');
                        dbms_output.put_line('============================================================================');
                        SELECT COUNT(*) INTO v_str_temp FROM cpirudba_ip_diagnosis_3nf;
                        dbms_output.put_line('CPIRUDBA IP DIAGNOSIS 3NF: '|| v_str_temp);
                        SELECT COUNT(*) INTO v_str_temp FROM cpirudba_ip_procedure_3nf;
                        dbms_output.put_line('CPIRUDBA IP PROCEDURE 3NF: '|| v_str_temp);
                         SELECT COUNT(*) INTO v_str_temp FROM prov_acsc_indicator_ref;
                        dbms_output.put_line('PROV ACSC INDICATOR REF: '|| v_str_temp);
                        SELECT COUNT(*) INTO v_str_temp FROM prov_acsc_indicator_rpt;
                        dbms_output.put_line('PROV ACSC INDICATOR RPT: '|| v_str_temp);
                        SELECT COUNT(*) INTO v_str_temp FROM ELOS_VS_ALOS_DASHBOARD;
                        dbms_output.put_line('ELOS VS ALOS DASHBOARD: '|| v_str_temp);
                         SELECT COUNT(*) INTO v_str_temp FROM facility_profile_dashboard;
                        dbms_output.put_line('FACILITY PROFILE DASHBOARD: '|| v_str_temp);
                        SELECT COUNT(*) INTO v_str_temp FROM cmg_local_geo_0211;
                        dbms_output.put_line('CMG LOCAL GEO 0211: '|| v_str_temp);
                         dbms_output.put_line('============================================================================');
                         dbms_output.put_line('Table Maximum Dates');
                         dbms_output.put_line('============================================================================');
                         dbms_output.put_line('AHS_IP_DOCTOR_DX: '||max_src_dt);
                         dbms_output.put_line('PROV_ACSC_INDICATOR_REF: '||max_tgt_dt);  
                         dbms_output.put_line('ELOS_VS_ALOS_DASHBOARD: '||ELOS_MAX_DT); 
                         dbms_output.put_line('FACILITY_PROFILE_DASHBOARD: '||FACILITY_MAX_DT); 
                         dbms_output.put_line('============================================================================');
                        dbms_output.put_line('End DAD Refresh: '|| TO_CHAR(SYSDATE,'yyyy-Mon-dd hh24:mi:ss'));
                        dbms_output.put_line('============================================================================');
                         v_str_temp := ROUND((SYSDATE - v_date_start)*24*60,1)||' minutes';    
                         dbms_output.put_line('Total Time: '|| v_str_temp);
                       send_mail('myname@companyname.ca','myname@companyname.ca','DAD Refresh Statistics', get_dbms_output);
                                          elsif max_src_dt > max_tgt_dt then 
                                                            dbms_output.put_line('DAD Refresh Required: Yes'); 
                                                            dbms_output.put_line('============================================================================');
                                                            dbms_output.put_line('Procedure Execution Start/End Times');
                                                            dbms_output.put_line('============================================================================');
                                                            dbms_output.put_line('Start PROC_CPIRUDBA_IP_DIAG_PROC_3NF Refresh: '|| TO_CHAR(SYSDATE,'yyyy-Mon-dd hh24:mi:ss'));     
                                                            begin  PROC_CPIRUDBA_IP_DIAG_PROC_3NF('01-apr-2002','01-apr-2020');end;
                                                            dbms_output.put_line('End PROC_CPIRUDBA_IP_DIAG_PROC_3NF Refresh: '|| TO_CHAR(SYSDATE,'yyyy-Mon-dd hh24:mi:ss'));   
                                                            dbms_output.put_line('Start INSERT_PROV_ACSC_IND_REF Refresh: '|| TO_CHAR(SYSDATE,'yyyy-Mon-dd hh24:mi:ss'));   
                                                            begin INSERT_PROV_ACSC_IND_REF(start_dt,end_dt); end;
                                                            dbms_output.put_line('End INSERT_PROV_ACSC_IND_REF Refresh: '|| TO_CHAR(SYSDATE,'yyyy-Mon-dd hh24:mi:ss'));   
                                                            dbms_output.put_line('Start CREATE_PROV_ACSC_INDICATOR_RPT Refresh: '|| TO_CHAR(SYSDATE,'yyyy-Mon-dd hh24:mi:ss'));   
                                                            begin 
                                                            CREATE_PROV_ACSC_INDICATOR_RPT(max_tgt_dt_fyr,end_dt,'ZONE_CODE','ZONE_NAME'); 
                                                            CREATE_PROV_ACSC_INDICATOR_RPT(max_tgt_dt_fyr,end_dt,'PEER_CODE','PEER_GROUP'); 
                                                            CREATE_PROV_ACSC_INDICATOR_RPT(max_tgt_dt_fyr,end_dt,'LOCAL_CODE','LOCAL_NAME'); 
                                                            CREATE_PROV_ACSC_INDICATOR_RPT(max_tgt_dt_fyr,end_dt,'SZONE_CODE','SZONE_NAME'); 
                                                            CREATE_PROV_ACSC_INDICATOR_RPT(max_tgt_dt_fyr,end_dt,'AGG_CODE','AGG_NAME'); 
                                                            CREATE_PROV_ACSC_INDICATOR_RPT(max_tgt_dt_fyr,end_dt,'RHA_CODE','RHA_NAME'); 
                                                            CREATE_PROV_ACSC_INDICATOR_RPT(max_tgt_dt_fyr,end_dt,'HSA_CODE','HSA_NAME'); 
                                                            dbms_output.put_line('End CREATE_PROV_ACSC_INDICATOR_RPT Refresh: '|| TO_CHAR(SYSDATE,'yyyy-Mon-dd hh24:mi:ss')); 
                                                            END;
                                                            dbms_output.put_line('Start CMG Dashboard Refresh: '|| TO_CHAR(SYSDATE,'yyyy-Mon-dd hh24:mi:ss'));
                                                            begin MERGE_CMG_DASHBOARDS('01-APR-2002','01-APR-2020' );end;
                                                            dbms_output.put_line('End CMG Dashboard Refresh: '|| TO_CHAR(SYSDATE,'yyyy-Mon-dd hh24:mi:ss'));
                                                            dbms_output.put_line('Start ELOS/ALOS Dashboard Refresh: '|| TO_CHAR(SYSDATE,'yyyy-Mon-dd hh24:mi:ss'));
                                                            begin INSERT_ELOS_ALOS_DASHBOARD('01-APR-2002','01-APR-2020');end;
                                                            dbms_output.put_line('End ELOS/ALOS Dashboard Refresh: '|| TO_CHAR(SYSDATE,'yyyy-Mon-dd hh24:mi:ss'));
                                                            dbms_output.put_line('Start Facility Profile Dashboard Refresh: '|| TO_CHAR(SYSDATE,'yyyy-Mon-dd hh24:mi:ss'));
                                                            begin INSERT_FACILITY_DASHBOARD('01-APR-2002','01-APR-2020');end;
                                                            dbms_output.put_line('End Facility Profile Dashboard Refresh: '|| TO_CHAR(SYSDATE,'yyyy-Mon-dd hh24:mi:ss'));
                                                            dbms_output.put_line('============================================================================');
                                                            dbms_output.put_line('Table Record Counts');
                                                            dbms_output.put_line('============================================================================');
                                                            SELECT COUNT(*) INTO v_str_temp FROM cpirudba_ip_diagnosis_3nf;
                                                            dbms_output.put_line('CPIRUDBA IP DIAGNOSIS 3NF: '|| v_str_temp);
                                                            SELECT COUNT(*) INTO v_str_temp FROM cpirudba_ip_procedure_3nf;
                                                            dbms_output.put_line('CPIRUDBA IP PROCEDURE 3NF: '|| v_str_temp);
                                                             SELECT COUNT(*) INTO v_str_temp FROM prov_acsc_indicator_ref;
                                                            dbms_output.put_line('PROV ACSC INDICATOR REF: '|| v_str_temp);
                                                            SELECT COUNT(*) INTO v_str_temp FROM prov_acsc_indicator_rpt;
                                                            dbms_output.put_line('PROV ACSC INDICATOR RPT: '|| v_str_temp);
                                                            SELECT COUNT(*) INTO v_str_temp FROM ELOS_VS_ALOS_DASHBOARD;
                                                            dbms_output.put_line('ELOS VS ALOS DASHBOARD: '|| v_str_temp);
                                                             SELECT COUNT(*) INTO v_str_temp FROM facility_profile_dashboard;
                                                            dbms_output.put_line('FACILITY PROFILE DASHBOARD: '|| v_str_temp);
                                                            SELECT COUNT(*) INTO v_str_temp FROM cmg_local_geo_0211;
                                                            dbms_output.put_line('CMG LOCAL GEO 0211: '|| v_str_temp);
                                                             dbms_output.put_line('============================================================================');
                                                            dbms_output.put_line('Table Maximum Dates');
                                                            dbms_output.put_line('============================================================================');
                                                             dbms_output.put_line('AHS_IP_DOCTOR_DX: '||max_src_dt);
                                                             dbms_output.put_line('PROV_ACSC_INDICATOR_REF: '||max_tgt_dt);  
                                                             dbms_output.put_line('ELOS_VS_ALOS_DASHBOARD: '||ELOS_MAX_DT); 
                                                             dbms_output.put_line('FACILITY_PROFILE_DASHBOARD: '||FACILITY_MAX_DT); 
                                                            dbms_output.put_line('============================================================================');
                                                           dbms_output.put_line('End DAD Refresh: '|| TO_CHAR(SYSDATE,'yyyy-Mon-dd hh24:mi:ss'));
                                                           dbms_output.put_line('============================================================================');
                                                           v_str_temp := ROUND((SYSDATE - v_date_start)*24*60,1)||' minutes';    
                                                           dbms_output.put_line('Total Time: '|| v_str_temp);
                                                           send_mail('myname@companyname.ca','myname@companyname.ca','DAD Refresh Statistics', get_dbms_output);

    end if;
        
       end;
       
end dad_refresh;
/
Any help would be appreciated.

Thanks
  • 1. Re: Run Procedures in Parallel
    sb92075 Guru
    Currently Being Moderated
    spalato76 wrote:
    1 PROC_CPIRUDBA_IP_DIAG_PROC_3NF
    2 INSERT_PROV_ACSC_IND_REF
    3 CREATE_PROV_ACSC_INDICATOR_RPT (runs 7 times but with different input parameters)
    4 MERGE_CMG_DASHBOARDS
    5 INSERT_ELOS_ALOS_DASHBOARD
    6 INSERT_FACILITY_DASHBOARD

    Currently, they all run in sequence. Would like it set up so that:

    Procedures 1,4,5,6 run at the same time
    Procedure 2 has to wait for Procedure 1 to be done before running
    Procedure 3 has to wait for Procedure 2 to be done before running
    Would procedures 1, 4, 5, & 6 compete to access the same objects?
    If all 4 ran at the same time which system resource, RAM, CPU or DISK, would be the major bottleneck?
  • 2. Re: Run Procedures in Parallel
    spalato76 Newbie
    Currently Being Moderated
    Yes, procedures 1,4,5 and 6 would be selecting from the same source table and doing inserts into their corresponding target tables. Don't think there would be any bottlenecks....it would be like running four select statements on the source table all at once.
  • 3. Re: Run Procedures in Parallel
    sb92075 Guru
    Currently Being Moderated
    spalato76 wrote:
    Yes, procedures 1,4,5 and 6 would be selecting from the same source table and doing inserts into their corresponding target tables. Don't think there would be any bottlenecks....it would be like running four select statements on the source table all at once.
    You are entitled to your opinion; regardless of whether they are based upon reality or not.

    I am assuming that these procedures ARE resource intensive & take many minutes to complete.
    Having all 4 to compete at the same time might not reduce total elapsed time significantly.

    So fire all 4 of them off at the same time & see what happens.
  • 4. Re: Run Procedures in Parallel
    rp0428 Guru
    Currently Being Moderated
    You're developing a bad habit of not providing your 4 digit Oracle version when you post (result of SELECT * FROM V$VERSION). And you have recently post multiple threads that appear to be related. If threads are related it helps if you post links to the other threads so volunteers can follow the entire chain and get a better view of the context.

    Without knowing the version there isn't any way to provide relevant advice so we just assume 11.2 is used.
    >
    I have a procedure, seen below, that essentially checks every day whether a source table has a maximum date greater than my target table. If the Maximum Date in the Source Table is greater, that signifies that I need to update my source tables with new data. The procedure runs perfectly, I've set it up to run as a job every 24 hours. Here's the problem. Currently, the way the procedure is written all the procedures are executed sequentially which makes the whole process much longer when in fact there are a few procedures that can run in parallel and I unfortunately don't know how to set it up to run in parallel. Here's the order of events --->

    If the max source date is greater than the target max date (if condition), then the procedures start executing (procedure list below). This is how I would like it set up:

    1 PROC_CPIRUDBA_IP_DIAG_PROC_3NF
    2 INSERT_PROV_ACSC_IND_REF
    3 CREATE_PROV_ACSC_INDICATOR_RPT (runs 7 times but with different input parameters)
    4 MERGE_CMG_DASHBOARDS
    5 INSERT_ELOS_ALOS_DASHBOARD
    6 INSERT_FACILITY_DASHBOARD

    Currently, they all run in sequence. Would like it set up so that:

    Procedures 1,4,5,6 run at the same time
    Procedure 2 has to wait for Procedure 1 to be done before running
    Procedure 3 has to wait for Procedure 2 to be done before running
    >
    That is exactly the sort of thing that DBMS_SCHEDULER chains are designed to help you with.

    See Examples of Creating Chains in the DBA Guide
    the http://docs.oracle.com/cd/B28359_01/server.111/b28310/schedadmin006.htm#BAJHFHCD
    >
    This section contains examples of creating chains. To create chains, you use the CREATE_CHAIN procedure. After creating a chain, you add steps to the chain with the DEFINE_CHAIN_STEP or DEFINE_CHAIN_EVENT_STEP procedures and define the rules with the DEFINE_CHAIN_RULE procedure.

    Example 28-16 Creating a Chain

    The following example creates a chain where my_program1 runs before my_program2 and my_program3. my_program2 and my_program3 run in parallel after my_program1 has completed.

    The user for this example must have the CREATE EVALUATION CONTEXT, CREATE RULE, and CREATE RULE SET privileges. See "Setting Chain Privileges" for more information.
    >
    Review the reply I provided in your 'Automating Procedures to run when Source Table is Updated' thread.
    Re: Automating Procedures to run when Source Table is Updated

    You will find that a very similar process can be used. Often a CONTROL table is used that each procedure (chain step) updates. So the scheduler can control the individual steps and each step can still check a control table to make sure things are done in the proper order and are not done multiple times.
  • 5. Re: Run Procedures in Parallel
    onedbguru Pro
    Currently Being Moderated
    With 11gR2 there is DBMS_PARALLEL_EXECUTE - this is GREAT for doing mass updates or whatever. I have used it to to parallel MERGE statements. Almost anything you can do in a single statement, you can do in a PARALLEL_EXECUTE procedure.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points