Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Lazy dbms_job

Re: Lazy dbms_job

From: Terry Sutton <terrysutton_at_usa.net>
Date: Thu, 21 Apr 2005 09:27:10 -0700
Message-ID: <016a01c5468f$95610bc0$6400a8c0@TerrySutton>


First, I'm curious why you do the rescheduling within the job. Why don't you just set the interval to 1/(60*60*24)? You still aren't going to get it to run every second, but I know of job which run every 4-5 seconds this way. But the dbms_job facility is not designed to do super-precise scheduling.

If you need these procedures to run every second, create a procedure with an infinite loop, using the dbms_lock.sleep procedure to stop for a second between executions of the 3 procedures. Start the procedure and let it run constantly. You don't even need to use dbms_job, though that can be used to ensure the job starts again if should ever stop. You don't need to stop the procedure, and the sleep procedure shouldn't consume significant resources. You will see you enqueue waits increase (sleep creates an enqueue), so if you're monitoring wait events, you'll want to note this.

--T

I am running following procedure ( Oracle 9i ):=20

dbms_job.submit( l_job, 'background_submit_proc( JOB, NEXT_DATE);' );

create or replace

    procedure background_submit_proc( p_job in number, p_NEXT_DATE OUT DATE=   )
    as
    begin

               uui(); -- these are procedures that are executed;
               uui1();=20
               uui2();

=09p_next_date :=3D sysdate + 1/24/60/60*1;

    exception when others then raise;
end;
/

Whole thing runs fine. It modifies NEXT_DATE so it reschedules itself as soon as
3 procedures are finished. Procedures are very fast ( takes less than a second to execute them ). So job's NEXT_DATE should be incremented almost every second.
It is not. It is incremented every 15 seconds.=20 Is there a way to make it more responsive ? I want it to execute every seco= nd .=20

Regards, Ranko.
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Apr 21 2005 - 12:35:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US