Wednesday, June 25, 2014

Generating load in parallel

create or replace procedure do_insert
as
    l_stop  date default sysdate+5/24/60;
    l_date  date default sysdate;
begin
    while (l_date < l_stop)
    loop
        insert into test (x)
        values (sysdate)
        returning x into l_date;
        commit;
    end loop;
end;
/
exec perfstat.statspack.snap;
declare
    l_job number;
begin
    for i in 1 .. 5
    loop
        dbms_job.submit( l_job, 'do_insert;' );
    end loop;
    commit;
end;
/


Wait until all jobs has been finished using the following statement. If you get no more rows as a result, the jobs has been completed.

select substr(job,1,4) "job",
       substr(schema_user,1,10) "user",
       substr(to_char(last_date,'dd.mm.yyyy hh24:mi'),1,16) "last date",
       substr(to_char(next_date,'dd.mm.yyyy hh24:mi'),1,16) "next date",
       substr(broken,1,2) "b",
       substr(failures,1,6) "failed",
       substr(what,1,32) "command"
  from dba_jobs;

No comments: