Scheduler job on Oracle does not run every second

Question

I have a specific scheduler job in Oracle that needs to be run every second.

I tried to create this (using a procedure):

begin
  sys.dbms_scheduler.create_job(job_name            => 'WBC6_PUBLIC.TESTE',
                                job_type            => 'STORED_PROCEDURE',
                                job_action          => 'proc_insert_data',
                                start_date          => to_date('19-02-2020 09:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                                repeat_interval     => 'Freq=Secondly;Interval=1',
                                end_date            => to_date(null),
                                job_class           => 'DEFAULT_JOB_CLASS',
                                enabled             => true,
                                auto_drop           => false,
                                comments            => '');
end;
/

And this (using PLSQL_BLOCK):

begin
  sys.dbms_scheduler.create_job(job_name            => 'WBC6_PUBLIC.TESTE',
                                job_type            => 'PLSQL_BLOCK',
                                job_action          => 'insert into my_table (date) values (sysdate);',
                                start_date          => to_date('19-02-2020 09:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                                repeat_interval     => 'Freq=Secondly;Interval=1',
                                end_date            => to_date(null),
                                job_class           => 'DEFAULT_JOB_CLASS',
                                enabled             => true,
                                auto_drop           => false,
                                comments            => '');
end;
/

But the result is the same, the job runs every ~ 4 seconds.

Is there a parameter or something I can do to run every second?

That may be too small of an interval. Why are you trying to run every second? — OldProgrammer
Because of the business rules require it. (changes item status) — dmoitim
Try adding job_style => 'LIGHTWEIGHT', to the create_job call See here — OldProgrammer
@OldProgrammer, thanks for the answer. I tried it with LIGHTWEIGHT too and the behavior was the same. Any other ideas? — dmoitim

Roger Cornejo Roger Cornejo · Accepted Answer · 2020-02-21T14:24:36

I've tried something like this before and if the procedure takes or could take more than the interval, then it is impossible. Al alternative is to run the code in a continuous loop with some kind of stop mechanism inbuilt (e.g. check a stop_table.stop_column for a "stop" condition). For example:

BEGIN
  LOOP 
     proc_insert_data;               /* run your insert */
     sys.DBMS_SESSION.sleep(1);      /* Pause for 1 second. */
     stop_condition := stop_condition + 1;
     EXIT WHEN stop_condition = 900; /* exit after 15 min  OR some other mechanism of  your choice */
   END LOOP;
END;
/

Scheduler job on Oracle does not run every second

1 Answers