oracle jobs 执行耗时,Oracle job运行的最短 interval间隔时间是多短？

好贴

DBMS_JOB 受到参数JOB_QUEUE_INTERVAL的影响 默认最小间隔是5s，可以通过手动设置减少到1s一次， 1s 是最短间隔

SQL> alter system set "_JOB_QUEUE_INTERVAL"=1 scope=spfile;

System altered.

reboot instance !!!

SQL> exec dbms_job.submit(:v_job,what=>'begin insert into ttime values(systimestamp); end;',next_date=>SYSDATE,INTERVAL=>'sysdate+1/86400');

PL/SQL procedure successfully completed.

SQL> exec dbms_job.run(:v_job);

PL/SQL procedure successfully completed.

SQL> /

T1

---------------------------------------------------------------------------

11-JUN-12 08.55.26.096802 AM

11-JUN-12 08.55.27.409266 AM

11-JUN-12 08.55.28.406008 AM

11-JUN-12 08.55.29.409585 AM

11-JUN-12 08.55.30.410420 AM

11-JUN-12 08.55.31.414326 AM

11-JUN-12 08.55.32.416529 AM

11-JUN-12 08.55.33.419503 AM

11-JUN-12 08.55.34.420857 AM

11-JUN-12 08.55.35.428322 AM

11-JUN-12 08.55.36.431015 AM

T1

---------------------------------------------------------------------------

11-JUN-12 08.55.37.430584 AM

11-JUN-12 08.55.38.434955 AM

11-JUN-12 08.55.39.437548 AM

11-JUN-12 08.55.40.439631 AM

11-JUN-12 08.55.41.441646 AM

11-JUN-12 08.55.42.441856 AM

11-JUN-12 08.55.43.446503 AM

11-JUN-12 08.55.44.447652 AM

11-JUN-12 08.55.45.449934 AM

11-JUN-12 08.55.46.451910 AM

DBMS_SCHEDULER

SQL> create table ttime_scheduler(t1 timestamp);

Table created.

SQL> alter system set "_JOB_QUEUE_INTERVAL"=30 scope=spfile;

System altered.

SQL> startup force;

ORACLE instance started.

Total System Global Area  851443712 bytes

Fixed Size                  2100040 bytes

Variable Size             738198712 bytes

Database Buffers          104857600 bytes

Redo Buffers                6287360 bytes

Database mounted.

Database opened.

建立每秒运行1次的 scheduler job

SQL> set linesize 140 pagesize 1400

SQL> show parameter job_queue

NAME                                 TYPE                             VALUE

------------------------------------ -------------------------------- ------------------------------

_job_queue_interval                  integer                          30

SQL> exec DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'my_report_job',JOB_TYPE => 'PLSQL_BLOCK',JOB_ACTION => 'begin insert_job; end;', START_DATE => SYSDATE,REPEAT_INTERVAL => 'FREQ=SECONDLY; INTERVAL=1;', END_DATE => SYSDATE+30,ENABLED => TRUE);

SQL> select * from ttime_scheduler;

T1

---------------------------------------------------------------------------

11-JUN-12 09.09.38.117732 AM

11-JUN-12 09.09.39.215509 AM

11-JUN-12 09.09.40.309133 AM

11-JUN-12 09.09.41.413390 AM

11-JUN-12 09.09.42.513138 AM

11-JUN-12 09.09.43.609060 AM

11-JUN-12 09.09.44.700273 AM

11-JUN-12 09.09.45.818303 AM

11-JUN-12 09.09.46.909685 AM

11-JUN-12 09.09.48.012584 AM

11-JUN-12 09.09.49.111379 AM

11-JUN-12 09.09.50.208165 AM

11-JUN-12 09.09.51.306311 AM

11-JUN-12 09.09.52.404987 AM

11-JUN-12 09.09.53.512844 AM

11-JUN-12 09.09.54.610366 AM

11-JUN-12 09.09.55.710293 AM

11-JUN-12 09.09.56.814974 AM

11-JUN-12 09.09.57.917172 AM

11-JUN-12 09.09.59.006032 AM

11-JUN-12 09.10.00.108008 AM

11-JUN-12 09.10.01.218745 AM

11-JUN-12 09.10.02.311328 AM

可以看到scheduler 不受_job_queue_interval 参数的影响