Difference between revisions of "Run multiple tasks in parallel"
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 ( | + | 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 '\'; |
− | + | exit when v_nr = 0; | |
− | |||
− | |||
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.