Difference between revisions of "Run multiple tasks in parallel"
		
		
		
		
		
		Jump to navigation
		Jump to search
		
				
		
		
	
| Line 1: | Line 1: | ||
If you want to run multiple statements simultaneously, it can be done using following code.  | If you want to run multiple statements simultaneously, it can be done using following code.  | ||
| − | The v_nr < 5 defines how many jobs will run at the same time (  | + | The v_nr < 5 defines how many jobs will run at the same time (5 at most in this case).  | 
Use the same prefix for all jobs if you want 1 queue. If you want multiple queues, different names can be found.  | Use the same prefix for all jobs if you want 1 queue. If you want multiple queues, different names can be found.  | ||
Keep in mind that calls to queue are blocking if the queue is already full.  | Keep in mind that calls to queue are blocking if the queue is already full.  | ||
| Line 16: | Line 16: | ||
         v_queuenr := v_queuenr + 1;  |          v_queuenr := v_queuenr + 1;  | ||
         dbms_scheduler.create_job(job_name => v_job_prefix || '_' || v_queuenr  |          dbms_scheduler.create_job(job_name => v_job_prefix || '_' || v_queuenr  | ||
| − |                                  , job_type=>'PLSQL_BLOCK'  | + |                                  , job_type => 'PLSQL_BLOCK'  | 
| − |                                  , job_action=> v_action  | + |                                  , job_action => v_action  | 
                                 , enabled => true  |                                  , enabled => true  | ||
                                 , auto_drop => true);  |                                  , auto_drop => true);  | ||
Revision as of 15:31, 1 June 2016
If you want to run multiple statements simultaneously, it can be done using following code. The v_nr < 5 defines how many jobs will run at the same time (5 at most in this case). Use the same prefix for all jobs if you want 1 queue. If you want multiple queues, different names can be found. Keep in mind that calls to queue are blocking if the queue is already full.
declare
  procedure queue (v_job_prefix in varchar2, v_action in varchar2, v_queuenr in out number) is
    v_nr number;
  begin
    loop
      select count(1)
        into v_nr
        from dba_scheduler_jobs
       where job_name like v_job_prefix || '\_%' escape '\';
      if v_nr < 5 then
        v_queuenr := v_queuenr + 1;
        dbms_scheduler.create_job(job_name => v_job_prefix || '_' || v_queuenr
                                , job_type => 'PLSQL_BLOCK'
                                , job_action => v_action
                                , enabled => true
                                , auto_drop => true);
        exit when true;
      end if;
      dbms_lock.sleep(10);
    end loop;
  end;
  
  procedure queue_wait_to_finish(v_job_prefix in varchar2) is
    v_nr number;
  begin
    loop
      select count(1)
        into v_nr
        from dba_scheduler_jobs
       where job_name like v_job_prefix || '\_%' escape '\';
	  exit when v_nr = 0;
      dbms_lock.sleep(10);
    end loop;
  end;
begin
  queue('REDEF20160601', 'execute immediate ''alter index idx1 rebuild online'';', v_queuenr);
  queue('REDEF20160601', 'execute immediate ''alter index idx2 rebuild online'';', v_queuenr);
  queue('REDEF20160601', 'execute immediate ''alter index idx3 rebuild online'';', v_queuenr);
  queue('REDEF20160601', 'execute immediate ''alter index idx4 rebuild online'';', v_queuenr);
  queue('REDEF20160601', 'execute immediate ''alter index idx5 rebuild online'';', v_queuenr);
  queue('REDEF20160601', 'execute immediate ''alter index idx6 rebuild online'';', v_queuenr);
  queue('REDEF20160601', 'execute immediate ''alter index idx7 rebuild online'';', v_queuenr);
  queue('REDEF20160601', 'execute immediate ''alter index idx8 rebuild online'';', v_queuenr);
  queue('REDEF20160601', 'execute immediate ''alter index idx9 rebuild online'';', v_queuenr);
  queue('REDEF20160601', 'execute immediate ''alter index idx10 rebuild online'';', v_queuenr);
  queue_wait_to_finish('REDEF20160601');
end;
/
Make sure all variables are before the functions in the declare block.