reducing the time of scheduled job

Question

Hi ,

I have written this scheduled job for the ORACLE DB which is run every second.

Now in my schedule program what I want to do is to set the frequency to every 5 millisecond.

BEGIN    
    sys.dbms_scheduler.create_schedule( 
            repeat_interval =>'FREQ=SECONDLY;INTERVAL=1',
            start_date => to_date('15:19 09/16/10','HH24:MI MM/DD/YY'),
            comments => 'Schedule for periodic loads',
            schedule_name => 'UserName.DOLOADS');   
END;

Waiting for your valuable suggestions..

Answer 1

"A second here, a second there, pretty soon you're talking Real Time."

Could you explain in a little more detail what you are trying to achieve? It is unclear from your question whether you merely want to have a scheduled job running every 5 milliseconds, or whether you want the scheduled job itself to change its frequency from 1 second to 5ms.

I have no idea if it is possible to specify a frequency of 5ms and it sounds like something you really shouldn't be doing.

Perhaps investigate an alternative mechanism? In one situation where i needed a near-instantaneous job execution, I used Advanced Queuing to respond to events quickly and in parallel to the primary logic flow.

Answer 2

You cannot schedule a DBMS_SCHEDULER (or DBMS_JOB) job to run every 5 milliseconds. Even when you schedule it to run every second, that's only an approximation-- it wouldn't be unexpected if there were 2 or 3 seconds between consecutive runs just because of normal delays between when a job is eligible to be run and the job scheduler actually picks it up and starts running it.

Why would you want a job that your comments describe as a "periodic load" to run every 5 milliseconds? Can you describe the data flow a bit? If you are looking for something like near real-time data replication, there are almost certainly better ways to approach the problem.

All that being said, if you really want to poll very rapidly, the closest you can probably get is to write a job that runs an infinite loop that does a dbms_lock.sleep of 10 milliseconds (0.01 seconds). This will introduce all sorts of additional complexity when you have to do maintenance or shut down the database and have to kill the currently running process, etc. But if schema_name.DoLoads were rewritten to do something like

CREATE PROCEDURE NewDoLoads
AS    
BEGIN
  WHILE( true )
  LOOP
    schema_name.DoLoads;
    dbms_lock.sleep( 0.01 );
  END LOOP;
END NewDoLoads;

that would run the current DoLoads process every 0.01 seconds (10 milliseconds) give or take. That's the smallest increment dbms_lock can handle.