Difference between revisions of "Run multiple tasks in parallel"
Jump to navigation
Jump to search
Line 5: | Line 5: | ||
<source lang="plsql"> | <source lang="plsql"> | ||
declare | declare | ||
− | |||
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 '\'; | ||
− | + | exit when v_nr = 0; | |
− | |||
− | |||
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.