Framework for Oracle Parallelism

Architecture

Execute Parallel Jobs in Oracle.



   PL/SQL:
begin
    MJB_PARALLEL.exec('proc_1', 'group_parallel');
    MJB_PARALLEL.exec('proc_2', 'group_parallel');

    IF MJB_PARALLEL.wait('group_parallel', 100) = MJB_PARALLEL.STATUS_RUNNING
    THEN
       -- Drop running jobs after the timeout
       MJB_PARALLEL.clear_job('group_parallel');
    END IF; 
end;   
  
 

Create producer consumer (thread pool).

Tutorial parallel execution

Create a dummy procedure for test purpose.

CREATE OR REPLACE PROCEDURE proc_work( vcAnimal varchar2 )
is
begin
  MJB_TRACE.info  ('................start proc_work: '||vcAnimal, 'proc_work');
  IF vcAnimal = 'Monkey'
  then
    DBMS_LOCK.sleep(50);
  elsif vcAnimal = 'Donkey'
  then
    DBMS_LOCK.sleep(60);
  else
    DBMS_LOCK.sleep(70);
  end if;
  MJB_TRACE.info  ('................end proc_work: '||vcAnimal, 'proc_work');
end;

One can create anonymous block we can call it controller with some logic and during its execution to launch some processes that to be executed asynchronous.

declare
  vcAnimal varchar2(50);
  CURSOR animals_cur IS
    select 'Monkey' animal from dual union all
    select 'Donkey' animal from dual union all
    select 'Chiken' animal from dual;
Begin
    OPEN animals_cur;
     LOOP
        FETCH animals_cur INTO vcAnimal;
        EXIT WHEN animals_cur%NOTFOUND;
       
        -- Launch the procedure with arguments based on the cursor as the loop will create as many sessions as iterations we have into the loop instantly and will exits.
        MJB_PARALLEL.exec('proc_work('''||vcAnimal||''')', 'group_parallel');
    END LOOP;
    -- Make a trace when procedure is started.
    MJB_TRACE.info('All procedures were launched asynchronously');
    
    -- Wait all 3 parallel task to finish    
    IF MJB_PARALLEL.wait('group_parallel', 100) = MJB_PARALLEL.STATUS_RUNNING
    THEN
       -- Drop running jobs after the timeout
       MJB_PARALLEL.clear_job('group_parallel');
    END IF;
    MJB_TRACE.info('All parallel tasks finished');
End test_log;
/

Check result.

ID	SUB_RES_NAME	MESSAGE	CREATED	TRACE_ID	LOG_LEVEL
112053		All procedures were launched asynchronously	11/01/2016 2:07:56 PM	112051	0
112055	proc_work	...............start proc_work: Monkey	11/01/2016 2:07:56 PM		0
112057	proc_work	................start proc_work: Donkey	11/01/2016 2:07:56 PM		0
112058	proc_work	................start proc_work: Chiken	11/01/2016 2:07:56 PM		0
112063	proc_work	................end proc_work: Monkey	11/01/2016 2:08:46 PM		0
112065	proc_work	................end proc_work: Donkey	11/01/2016 2:08:56 PM		0
112067	proc_work	................end proc_work: Chiken	11/01/2016 2:09:06 PM		0
112069		All parallel tasks finished	11/01/2016 2:09:16 PM	112051	0

Tutorial thread pool execution

PL/SQL example code for thread pool execution.

Begin
MJB_PRODUCER_CONSUMER.start_tread_pool(2);

MJB_PRODUCER_CONSUMER.add_task_to_queue(‘proc1’);
MJB_PRODUCER_CONSUMER.add_task_to_queue(‘proc2’);
MJB_PRODUCER_CONSUMER.add_task_to_queue(‘proc3’);
MJB_PRODUCER_CONSUMER.add_task_to_queue(‘proc4’);

MJB_PRODUCER_CONSUMER.wait(300);

MJB_PRODUCER_CONSUMER.stop_tread_pool;
MJB_PARALLEL.wait('group_parallel',60);
End;
/

Thread pool execution diagram.

Documentation

Package MJB_PARALLEL
Procedure / Function	Parameters	Description
exec	vcProgramExecute varchar2, vcParallelGroupName varchar2	Launch a pl/sql program in parallel vcProgramExecute: the exact command vcParallelGroupName: this will be the first part from the job name
wait	vcParallelGroupName IN VARCHAR2, nTimeoutSeconds IN NUMBER	Wait for all jobs with name starting with vcParallelGroupName
clear_job	vcJobName IN VARCHAR2	Drop all jobs with name starting with vcJobName

Package MJB_PRODUCER_CONSUMER
Procedure / Function	Parameters	Description
add_task_to_queue	vcProgramExecute varchar2, vcQueueName varchar2	Launch a pl/sql program in parallel vcProgramExecute: the exact command vcQueueName: this will be the first part from the job name
start_tread_pool	nSize IN NUMBER default 3	How many parallel jobs will be running.
stop_tread_pool		Drop all jobs and remove all tasks in queue
task_dispacher

Licenses

Open Source License: Apache 2.

Commercial Licensing:

• SOS grants customers who purchased commercial licenses access to a wide range of Support Options and professional Services.
• Customers of commercial licenses help us to finance on-going development from license revenues.
• Customers benefit from on-going Product Maintenance Service, Support Options and Services, e.g. Training Service, Implementation Service and Migration Service.
• Customers receive Long Term Support by maintenance releases for longer periods whereas publicly available maintenance releases are provided for one year.
• Customers report issues to our SOS Ticket System, are guaranteed response times and receive immediate support in case of production standstill.

Cras mattis consectetur purus sit amet fermentum. Sed posuere consectetur est at lobortis.

Installation

Automatic installation.

@install.sql

Installation details.

--Grants 
GRANT SELECT ON V_$SESSION TO ;
grant execute on sys.dbms_lock to ;
GRANT CREATE JOB TO ;
grant create external job to ;