Difference between revisions of "Run multiple tasks in parallel"
Jump to navigation
Jump to search
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 16: | Line 16: | ||
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 => 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); |
Revision as of 16:31, 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 (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
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.