Difference between revisions of "Run multiple tasks in parallel"
Jump to navigation
Jump to search
(Created page with "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 cas...") |
|||
Line 13: | Line 13: | ||
into v_nr | into v_nr | ||
from dba_scheduler_jobs | from dba_scheduler_jobs | ||
− | where job_name like | + | where job_name like v_job_prefix || '\_%' escape '\'; |
if v_nr < 5 then | if v_nr < 5 then | ||
v_queuenr := v_queuenr + 1; | v_queuenr := v_queuenr + 1; | ||
Line 34: | Line 34: | ||
into v_nr | into v_nr | ||
from dba_scheduler_jobs | from dba_scheduler_jobs | ||
− | where job_name like | + | where job_name like v_job_prefix || '\_%' escape '\'; |
if v_nr = 0 then | if v_nr = 0 then | ||
exit when true; | exit when true; |
Revision as of 16:19, 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
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 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 '\';
if v_nr = 0 then
exit when true;
end if;
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;
/