How to call multiple procedures in parallel in Oracle 12c?

Question

I have a procedure as follows,

CREATE OR REPLACE PROCEDURE engineering_all ( idx IN NUMBER )
IS
tempstmt VARCHAR2(2000);
BEGIN
create_table_like( 'results_temp', 'results', 1);

tempstmt :=  'ALTER TABLE results_temp CACHE';
EXECUTE IMMEDIATE tempstmt;

engineering('CONSERVATIVE', idx);
engineering('INTERMEDIATE', idx);
engineering('AGGRESSIVE',   idx);
END;
/

Three calls to procedure engineering are independent of each other, so I want to parallelize this. I came across multiple ways like DBMS_PARALLEL_EXECUTE, DBMS_JOB, DBMS_SCHEDULER but not able to figure out which one is the most effective for my time-optimization objective.

Please help me out to figure out which one to choose and how can I implement that?

Answer 1

I suggest to use DBMS_PARALLEL_EXECUTE. The main session waits till child parallel sessions are finished. There are convenient views with the statistics and results - user_parallel_execute_chunks and user_parallel_execute_tasks. We use it in our project, find it quite convenient.

One point is that this package requires chunking that can be done only by rowid or by numbers. So first of all you have to create the procedure wich accepts numbers. Here's the one for your case:

create or replace procedure engineering_parallel(
  iLaunchType number, 
  idx         number
) 
is
begin
  if iLaunchType = 1 then
    engineering('CONSERVATIVE',idx);
  elsif iLaunchType = 2 then
    engineering('INTERMEDIATE',idx);
  elsif iLaunchType = 3 then
    engineering('AGGRESSIVE',idx);
  end if;
end;

And here you will find an example of launching your case in anonymous pl/sql block, you can easily convert it into engineering_all procedure:

declare
  -- idx parameter
  idx number := 0;
  -- unique parallel task name
  sTaskName varchar2(32767) := 'ENGINEERING-'||to_char(sysdate,'yyyy-mm-dd-hh24-mi-ss');
  -- this is where you store the query to split into chunks
  sChunkSQL varchar2(32767) := 'select level start_id, '||idx||' end_id'||chr(10)||
                               'from   dual connect by level <= 3';
  -- this is the procedure call
  sParallelSQL varchar2(32767) := 'begin engineering_parallel(:start_id,:end_id); end;';
  -- parallel degree
  iParalleDegree number := 3;
begin
  -- create a task
  DBMS_PARALLEL_EXECUTE.create_task(task_name => sTaskName);

  -- chunking
  DBMS_PARALLEL_EXECUTE.create_chunks_by_sql(
    task_name => sTaskName,
    sql_stmt  => sChunkSQL,
    by_rowid  => FALSE
  );

  -- launch. current session waits till all child parallel sessions are finished
  DBMS_PARALLEL_EXECUTE.run_task(
    task_name      => sTaskName,
    sql_stmt       => sParallelSQL,
    language_flag  => DBMS_SQL.NATIVE,
    parallel_level => iParalleDegree
  );

  dbms_output.put_line(
    'Job is finished.'||
    'Check user_parallel_execute_chunks, user_parallel_execute_tasks for the task '||
    sTaskName
  );

end;  

Last point to consider - check if your version includes the fix of Bug 18966843: DBMS_PARALLEL_EXECUTE PERFORMANCE DELAY AFTER UPGRADE TO 11.2.0.4 We faced it in 12.1, but there are patches to fix it. If it's not fixed then you have a chance that the parallel degree at the end will be less than requested (down to 1).

Answer 2

I never used the first option you mentioned, but - choosing between DBMS_JOB and DBMS_SCHEDULER, DBMS_JOB is simpler so I'd choose that. In its simplest way, procedure might look like this:

CREATE OR REPLACE PROCEDURE engineering_all (idx IN NUMBER)
IS
   l_job      NUMBER;
   tempstmt   VARCHAR2 (2000);
BEGIN
   create_table_like ('results_temp', 'results', 1);
   tempstmt := 'ALTER TABLE results_temp CACHE';

   EXECUTE IMMEDIATE tempstmt;

   DBMS_JOB.submit (
      job         => l_job,
      what        => 'begin engineering(''CONSERVATIVE'', ' || idx || '); end;',
      next_date   => SYSDATE,
      interval    => NULL);

   DBMS_JOB.submit (
      job         => l_job,
      what        => 'begin engineering(''INTERMEDIATE'', ' || idx || '); end;',
      next_date   => SYSDATE,
      interval    => NULL);

   DBMS_JOB.submit (
      job         => l_job,
      what        => 'begin engineering(''AGGRESSIVE'', ' || idx || '); end;',
      next_date   => SYSDATE,
      interval    => NULL);

   COMMIT;
END;
/