Difference between revisions of "Run multiple tasks in parallel"
		
		
		
		
		
		Jump to navigation
		Jump to search
		
				
		
		
	
| (2 intermediate revisions by the same user not shown) | |||
| 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.  | ||
<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 12: | Line 14: | ||
         into v_nr  |          into v_nr  | ||
         from dba_scheduler_jobs  |          from dba_scheduler_jobs  | ||
| − |         where job_name like v_job_prefix || '\_%' escape '\';  | + |         where job_name like upper(v_job_prefix) || '\_%' escape '\';  | 
       if v_nr < 5 then  |        if v_nr < 5 then  | ||
         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 => upper(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);  | ||
| Line 33: | Line 35: | ||
         into v_nr  |          into v_nr  | ||
         from dba_scheduler_jobs  |          from dba_scheduler_jobs  | ||
| − |         where job_name like v_job_prefix || '\_%' escape '\';  | + |         where job_name like upper(v_job_prefix) || '\_%' escape '\';  | 
	  exit when v_nr = 0;  | 	  exit when v_nr = 0;  | ||
       dbms_lock.sleep(10);  |        dbms_lock.sleep(10);  | ||
Latest revision as of 16:25, 29 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
  v_queuenr number := 0;
  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 upper(v_job_prefix) || '\_%' escape '\';
      if v_nr < 5 then
        v_queuenr := v_queuenr + 1;
        dbms_scheduler.create_job(job_name => upper(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 upper(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.