Difference between revisions of "Run multiple tasks in parallel"

From Tom's notes
Jump to navigation Jump to search
 
(3 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 (4 at most in this case).
+
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 6: Line 6:
 
declare
 
declare
 
   v_queuenr number := 0;
 
   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 13: 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 34: 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 '\';
      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 55:
 
/
 
/
 
</source>
 
</source>
 +
 +
Make sure all variables are before the functions in the declare block.

Latest revision as of 17: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.