Oracle Job排队执行实验

1验证的SP

指定插入数据的标识is_key，并记录时间。

create or replace procedure sp_insert(is_key varchar2,in_rownum number)
is
begin
  for c in 1.. in_rownum loop
    insert into c values(is_key,c,sysdate);
    commit;
    dbms_lock.sleep(1);
  end loop;
  commit;
end;    

2 job_queue_processes

参数设为3.

SQL> alter system set job_queue_processes=3;
System altered  

3 提交3个job

declare
  jobid number;
begin
  dbms_job.submit(jobid,
                  what      => 'sp_insert(''key1'',10);',
                  next_date => to_date('20180821150500', 'yyyymmddhh24miss'));
  dbms_job.submit(jobid,
                  what      => 'sp_insert(''key2'',10);',
                  next_date => to_date('20180821150500', 'yyyymmddhh24miss'));
  dbms_job.submit(jobid,
                  what      => 'sp_insert(''key3'',10);',
                  next_date => to_date('20180821150500', 'yyyymmddhh24miss'));  
commit；                                       
end;    

4 检查job执行时间

select item, min(datadate), max(datadate), count(c.item)
  from c
 group by c.item
 order by 1;    

数据标识	开始时间	结束时间
key1	2018-8-21 15:05:03	2018-8-21 15:05:12
key2	2018-8-21 15:05:03	2018-8-21 15:05:12
key3	2018-8-21 15:05:03	2018-8-21 15:05:12

可以看到，job同时开始。

5同时提交5个job

同样方法提交，不再赘述。

主键	开始时间	结束时间
key1	2018-8-21 15:05:03	2018-8-21 15:05:12
key2	2018-8-21 15:05:13	2018-8-21 15:05:22
key3	2018-8-21 15:05:13	2018-8-21 15:05:22
key4	2018-8-21 15:05:03	2018-8-21 15:05:12
key5	2018-8-21 15:05:03	2018-8-21 15:05:12

可以看到，key1、4、5的按时执行，key2、3的等待了，应该是在原来job完成子进程空闲后执行的。与预期一致。