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

逃离我推掉我的手 2023-01-18 05:29 119阅读 0赞

好贴

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> /

\---------------------------------------------------------------------------

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

\---------------------------------------------------------------------------

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;

\---------------------------------------------------------------------------

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