Difference between revisions of "Run multiple tasks in parallel"

From Tom's notes
Jump to navigation Jump to search
Line 5: Line 5:
 
<source lang="plsql">
 
<source lang="plsql">
 
declare
 
declare
  v_queuenr number := 0;
 
 
   procedure queue (v_job_prefix in varchar2, v_action in varchar2, v_queuenr in out number) is
 
   procedure queue (v_job_prefix in varchar2, v_action in varchar2, v_queuenr in out number) is
 
     v_nr number;
 
     v_nr number;
Line 35: Line 34:
 
         from dba_scheduler_jobs
 
         from dba_scheduler_jobs
 
       where job_name like v_job_prefix || '\_%' escape '\';
 
       where job_name like v_job_prefix || '\_%' escape '\';
      if v_nr = 0 then
+
  exit when v_nr = 0;
        exit when true;
 
      end if;
 
 
       dbms_lock.sleep(10);
 
       dbms_lock.sleep(10);
 
     end loop;
 
     end loop;
Line 56: Line 53:
 
/
 
/
 
</source>
 
</source>
 +
 +
Make sure all variables are before the functions in the declare block.

Revision as of 16:23, 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 (4 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.