比较dbms_job几个模拟多线程方法在调用延迟上的差异

目的：比较几个多线程方法在调用延迟上的差异。

1.dbms_job.submit;

2.dbms_job.isubmit;

3.dbms_job.run;

4.dbms_job.broken;

这几种都能调用新JOB，模拟多线程，比较这几种方式的差异。

实验内容：连续调用起100个线程，每10个后sleep(1)，记录每个线程的调起时间，且从kect_testa表随机取一条记录插入到kect_testb表。每个线程完成kect_testb.pre_start_time的更新。通过比较kect_testb.pre_start_time与ins_time的时间差可以得到延迟时长(select pre_start_time-ins_time,t.ins_time,t.pre_start_time from kect_testb t;)。

实验结论：dbms_job.run从job起调到新线程的开始运行的延迟最短。

1.dbms_job.submit; 1.386~5.916秒

2.dbms_job.isubmit; 1.214~6.020秒

3.dbms_job.run; 0.013~0.017秒

4.dbms_job.broken; 0.428~5.812秒

create table kect_testa(

id number,

name varchar2(30),

type varchar2(30)

);

/

insert into kect_testa select rownum,object_name,object_type from user_objects;

/

create table kect_testb(

thread_id number,

ins_time timestamp(3),

id number,

name varchar2(30),

type varchar2(30),

pre_start_time timestamp(3),

pre_stop_time timestamp(3)

);

/

1.从以下代码运行结果看，从JOB起调到线程开始运行延迟1.386~5.916秒。（看kect_testb.pre_start_time与ins_time的时间差）

create or replace package KECT_TEST is

procedure main;
procedure pre_run(p_thread_id number);

end KECT_TEST;

/

create or replace package body KECT_TEST is

procedure main as
l_rec_a kect_testa%rowtype;
l_rec_b kect_testb%rowtype;
l_sql varchar2(1000);
begin
for x in 1..100 loop
l_rec_a:=null;
l_rec_b:=null;
if mod(x,10)=0 then
dbms_lock.sleep(1);
end if;
l_rec_b.id:=round(dbms_random.value(1,10000));
select * into l_rec_a from kect_testa
where id=l_rec_b.id;
l_rec_b.id :=l_rec_a.id;
l_rec_b.name:=l_rec_a.name;
l_rec_b.type:=l_rec_a.type;
l_rec_b.thread_id:=x;
l_rec_b.ins_time :=systimestamp;
insert into kect_testb values l_rec_b;
commit;
l_sql:='declare ';
l_sql:=l_sql||'l_job number; ';
l_sql:=l_sql||'begin ';
l_sql:=l_sql||'dbms_job.submit(l_job,''kect_test.pre_run('||x||');'');';
l_sql:=l_sql||'end;';
execute immediate l_sql;

commit;
end loop;
end main;

procedure pre_run(p_thread_id number) as
l_rec_b kect_testb%rowtype;
begin
l_rec_b.pre_start_time:=systimestamp;
update kect_testb set pre_start_time=l_rec_b.pre_start_time
where thread_id=p_thread_id;
commit;
end pre_run;

end KECT_TEST;

/

2.从以下代码运行结果看，从JOB起调到线程开始运行延迟1.214~6.020秒。（看kect_testb.pre_start_time与ins_time的时间差）

create or replace package KECT_TEST is

procedure main;
procedure pre_run(p_thread_id number);

end KECT_TEST;

/

create or replace package body KECT_TEST is

procedure main as
l_rec_a kect_testa%rowtype;
l_rec_b kect_testb%rowtype;
l_sql varchar2(1000);
l_jobid number;
l_exists number;
begin
for x in 1..100 loop
l_rec_a:=null;
l_rec_b:=null;
if mod(x,10)=0 then
dbms_lock.sleep(1);
end if;
loop
l_jobid:=round(dbms_random.value(1000,2000));
select count(*) into l_exists from user_jobs
where job=l_jobid;
exit when l_exists=0;
end loop;
l_rec_b.id:=round(dbms_random.value(1,10000));
select * into l_rec_a from kect_testa
where id=l_rec_b.id;
l_rec_b.id :=l_rec_a.id;
l_rec_b.name:=l_rec_a.name;
l_rec_b.type:=l_rec_a.type;
l_rec_b.thread_id:=x;
l_rec_b.ins_time :=systimestamp;
insert into kect_testb values l_rec_b;
commit;
l_sql:='begin ';
l_sql:=l_sql||'dbms_job.isubmit(:1,''kect_test.pre_run('||x||');'',:2);';
l_sql:=l_sql||'end;';
execute immediate l_sql using l_jobid,sysdate;

commit;
end loop;
end main;

procedure pre_run(p_thread_id number) as
l_rec_b kect_testb%rowtype;
begin
l_rec_b.pre_start_time:=systimestamp;
update kect_testb set pre_start_time=l_rec_b.pre_start_time
where thread_id=p_thread_id;
commit;
end pre_run;

end KECT_TEST;

/

3.从以下代码运行结果看，从JOB起调到线程开始运行延迟0.013~0.017秒。（看kect_testb.pre_start_time与ins_time的时间差）

create or replace package KECT_TEST is

procedure main;
procedure pre_run(p_thread_id number);

end KECT_TEST;

/

create or replace package body KECT_TEST is
procedure main as
l_rec_a kect_testa%rowtype;
l_rec_b kect_testb%rowtype;
l_sql varchar2(1000);
l_jobid number;
begin
for x in 1..100 loop
l_jobid:=x+1000;
--用于创建JOB

--l_sql:='begin ';
--l_sql:=l_sql||'dbms_job.isubmit('||l_jobid||',''kect_test.pre_run('||x||');'',:2,''sysdate+1000'');';
--l_sql:=l_sql||'end;';
--execute immediate l_sql using sysdate+1000;
--commit;

l_rec_a:=null;
l_rec_b:=null;
if mod(x,10)=0 then
dbms_lock.sleep(1);
end if;
l_rec_b.id:=round(dbms_random.value(1,10000));
select * into l_rec_a from kect_testa
where id=l_rec_b.id;
l_rec_b.id :=l_rec_a.id;
l_rec_b.name:=l_rec_a.name;
l_rec_b.type:=l_rec_a.type;
l_rec_b.thread_id:=x;
l_rec_b.ins_time :=systimestamp;
insert into kect_testb values l_rec_b;
commit;
l_sql:='begin ';
l_sql:=l_sql||'dbms_job.run('||l_jobid||');';
l_sql:=l_sql||'end;';
execute immediate l_sql;
commit;
end loop;
end main;

procedure pre_run(p_thread_id number) as
l_rec_b kect_testb%rowtype;
begin
l_rec_b.pre_start_time:=systimestamp;
update kect_testb set pre_start_time=l_rec_b.pre_start_time
where thread_id=p_thread_id;
commit;
end pre_run;

end KECT_TEST;

/

4.从以下代码运行结果看，从JOB起调到线程开始运行延迟0.428~5.812秒。（看kect_testb.pre_start_time与ins_time的时间差）

create or replace package KECT_TEST is

procedure main;
procedure pre_run(p_thread_id number);

end KECT_TEST;

/

create or replace package body KECT_TEST is
procedure main as
l_rec_a kect_testa%rowtype;
l_rec_b kect_testb%rowtype;
l_sql varchar2(1000);
l_jobid number;
begin
for x in 1..100 loop
l_jobid:=x+1000;
--用于创建JOB
--l_sql:='begin ';
--l_sql:=l_sql||'dbms_job.isubmit('||l_jobid||',''kect_test.pre_run('||x||');'',:2,''sysdate+1000'');';
--l_sql:=l_sql||'end;';
--execute immediate l_sql using sysdate+1000;
--commit;

l_rec_a:=null;
l_rec_b:=null;
if mod(x,10)=0 then
dbms_lock.sleep(1);
end if;
l_rec_b.id:=round(dbms_random.value(1,10000));
select * into l_rec_a from kect_testa
where id=l_rec_b.id;
l_rec_b.id :=l_rec_a.id;
l_rec_b.name:=l_rec_a.name;
l_rec_b.type:=l_rec_a.type;
l_rec_b.thread_id:=x;
l_rec_b.ins_time :=systimestamp;
insert into kect_testb values l_rec_b;
commit;
l_sql:='begin ';
l_sql:=l_sql||'dbms_job.broken('||l_jobid||',false,:2);';
l_sql:=l_sql||'end;';
execute immediate l_sql using sysdate;
commit;
end loop;
end main;

procedure pre_run(p_thread_id number) as
l_rec_b kect_testb%rowtype;
begin
l_rec_b.pre_start_time:=systimestamp;
update kect_testb set pre_start_time=l_rec_b.pre_start_time
where thread_id=p_thread_id;
commit;
end pre_run;

end KECT_TEST;
/