Oracle Parallel Execution of Procedures

You can call procedures or packages in parallel. Oracle lets us maintain by using sys.dbms_parallel_execute package. At first glance, understanding the package is not that easy. But I prepared an example, and you can easily add or subtract procedures from my template code.

Let’s see my example. I created 5 independent procedures. Name of our procedures : proc_B, proc_A, proc_R, proc_T, proc_U

I created a package pck_execute_parallel and it has 2 procedures. The first one is prc_job_list and it stores list of procedures. The other one is prc_run_parallel and it maintains parallelism.

Creation of the procedures :

CREATE OR REPLACE PROCEDURE proc_B AS
BEGIN
    --Do something
    sys.dbms_session.sleep(10);
END;

CREATE OR REPLACE PROCEDURE proc_A AS
BEGIN
    --Do something
    sys.dbms_session.sleep(7);
END;

CREATE OR REPLACE PROCEDURE proc_R(param1 NUMBER) AS
BEGIN
    --Do something
    sys.dbms_session.sleep(5);
END;

CREATE OR REPLACE PROCEDURE proc_T(param1 NUMBER) AS
BEGIN
    --Do something
    sys.dbms_session.sleep(8);
END;

CREATE OR REPLACE PROCEDURE proc_U(param1 NUMBER) AS
BEGIN
    --Do something
    sys.dbms_session.sleep(6);
END;

Creation of the package spec :

CREATE OR REPLACE PACKAGE pck_execute_parallel AS
    procedure prc_job_list (start_id number, end_id number);
    procedure prc_run_parallel;
END;

Creation of the the package body :

CREATE OR REPLACE PACKAGE BODY pck_execute_parallel AS
    PROCEDURE prc_job_list (start_id number, end_id number) AS
    BEGIN
        IF    (start_id = 1) THEN
            proc_B;
        ELSIF (start_id = 2) THEN
            proc_A;
        ELSIF (start_id = 3) THEN
            proc_R(2024);
        ELSIF (start_id = 4) THEN
            proc_T(99);
        ELSIF (start_id = 5) THEN
            proc_U(99);
        END IF;
        --If you add more or remove ELSEIF, you MUST CHANGE value of V_PROC_CALL_COUNT.
    END prc_job_list;
    
    PROCEDURE prc_run_parallel AS
        v_task_name          sys.dbms_parallel_execute_task$.task_name%TYPE := 'TASK_PARALEL_RUN';
        v_task_exists        NUMBER(1);
        --How many procedure will be run in parallel? (chunk count):
        v_proc_call_count    NUMBER(3):= 5;  
        v_chunk_sql          CLOB;
        v_run_sql            CLOB;
        v_retry_count_chunk  NUMBER(3):= 2;
        v_retry_loop         NUMBER(3):= 0;
        v_task_status        NUMBER(2);
        exp_raise_task_error EXCEPTION;
    BEGIN
        SELECT COUNT(*) INTO v_task_exists
          FROM sys.dba_parallel_execute_tasks 
         WHERE 1=1
               --and task_owner = ''  --package schema
               and task_name = v_task_name; 
        IF (v_task_exists=1) THEN
            sys.dbms_parallel_execute.drop_task (task_name => v_task_name);
        END IF;
        
        sys.dbms_parallel_execute.create_task (task_name => v_task_name);

        v_chunk_sql := 'SELECT 0 + LEVEL as start_id, 
                               0 + LEVEL as end_id 
                          FROM sys.dual
              CONNECT BY LEVEL <= '||v_proc_call_count;
        
        v_run_sql := 'BEGIN 
                        pck_execute_parallel.prc_job_list(:start_id, :end_id);
                      END; ';
        
        sys.dbms_parallel_execute.create_chunks_by_sql(task_name => v_task_name,
                                                       sql_stmt  => v_chunk_sql,
                                                       by_rowid  => FALSE);
        sys.dbms_parallel_execute.run_task(task_name => v_task_name,
                                           sql_stmt => v_run_sql,
                                           language_flag => DBMS_SQL.NATIVE,
                                           parallel_level => v_proc_call_count
                                          );
        --even if all chunks get errors, the task will finish. So we need error handling
        v_task_status := sys.dbms_parallel_execute.task_status(v_task_name);
        
        WHILE ( v_retry_loop < v_retry_count_chunk 
            AND v_task_status != sys.dbms_parallel_execute.finished) 
        LOOP
            v_retry_loop := v_retry_loop + 1;
            --"resume_task" runs chunks that failed. it does not run chunks that finished successfully 
            sys.dbms_parallel_execute.resume_task(v_task_name);
            v_task_status := sys.dbms_parallel_execute.task_status(v_task_name);
        END LOOP;

        IF(v_task_status != sys.dbms_parallel_execute.finished) THEN
            RAISE exp_raise_task_error;
        END IF;
    
    EXCEPTION
    WHEN exp_raise_task_error THEN 
        RAISE_APPLICATION_ERROR(-20100, 'Chunk(s) is failed. ' || sqlerrm);
    WHEN OTHERS THEN 
        RAISE_APPLICATION_ERROR(-20100, 'Error :  ' || sqlerrm);
    END prc_run_parallel;
END pck_execute_parallel;

Execution of procedures in parallel :

exec pck_execute_parallel.prc_run_parallel;

If you get grant error at execution, try this codes :

grant manage scheduler to YourUser;
grant create job to YourUser;

Check the status of execution :

SELECT * FROM  user_parallel_execute_tasks where task_name = 'TASK_PARALEL_RUN';
--Expecting value : STATUS column:  FINISHED
SELECT * FROM  user_parallel_execute_chunks where task_name = 'TASK_PARALEL_RUN';
--Expecting value for ALL rows : STATUS column:  PROCESSED

If you see the FINISHED at task, and PROCESSED at chunks. It means everything is worked well. You can check also start, finish time of procedures :

If you want to understand what code do in bold outline, you can read it here. Under prc_run_parallel, I created a task. I define how many rows will be at v_chunk_sql variable. v_run_sql is a variable for run the chunks. create_chunks_by_sql generates chunks ( parallel steps ). “(:start_id, :end_id)” is obligatory. run_task runs v_run_sql with different start_id’s. The rest of the code is for error management. If one or more of step is fails, resume_task runs only failed steps.

Full codes as txt :