Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Reasons for Job queues misbehaviour??
A copy of this was sent to pgaur_at_my-dejanews.com
(if that email address didn't require changing)
On Wed, 13 Jan 1999 09:57:58 GMT, you wrote:
>Hi, I submitted a job in Oracle 7.3.2 , with the execution interval of 1
>minute. According to the rules of job queues, the job should be marked
>brocken only after 16 failed attempts. Here, since the execution interval is
>1 minute, either oracle will fail in 17 minutes (when detects the second
>failure after 2 more minutes, will see that the execution interval was 1 min
>so should try again after 1 min), or if the retry is made with doubled the
>time span between two consecutive retries , than it should come in days. But
>both the things were not found here. Here the failure was indicated finally
>in about 5 hours.Neither in 17 min nor in many days(1+2+4+8......). I am
>really stucked up here. Can anybody help me! Thanx. Praveen Gaur
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
What is the interval on the job itself. The documentation is wrong, the real "algorithm" is:
If there is one SNP process, unless the time to execute all ready jobs exceeds the interval for job J, job J will execute at most once every job_queue_interval. You will not see the exponential backoff described in the documentation..
Because the exponential backoff is limited by the interval for job J, you will not see any exponential backoff if the interval for job J is less than or equal to one minute..
The following should illustrate some backoff:
job_queue_interval=10 (the units are in seconds)
job_queue_processes=1
execute job J every 10 minutes: interval=>'sysdate+10/(24*60)' .
When job J starts failing, you should see a retry at roughly 1 minute later, then 2 minutes after the last retry, then 4 minutes after the last retry, then 8 minutes after the last retry, then 10 minutes after the last retry. Retries will occur roughly every 10 minutes until the job is marked broken.
So, for example, if you have an interval of about 25 minutes or so, you would see the job fail after 5 hours. here is a little pl/sql block that should help compute the broken time. delay is a variable that will have the time in minutes the job will be declared broken. T is a variable that should be defaulted to the time in minutes the job queues execute (init.ora parameter). maximum should be set to the time in minutes the job is set to execute (interval).
1 declare
2 delay number default 0; 3 t number default 1; 4 maximum number default 25; 5 begin 6 for i in 1 .. 16 loop 7 delay := delay + t; 8 t := t * 2; 9 if ( t > maximum ) then 10 t := maximum; 11 end if; 12 dbms_output.put_line( to_char( delay, '9999999' ) || 13 ' minutes, next try in ' || t ); 14 end loop; 15 dbms_output.put_line( '--------------' ); 16 dbms_output.put_line( delay || ' minutes' ); 17 dbms_output.put_line( delay/60 || ' hours' ); 18 dbms_output.put_line( delay/60/24 || ' days' );19* end;
1 minutes, next try in 2 3 minutes, next try in 4 7 minutes, next try in 8 15 minutes, next try in 16 31 minutes, next try in 25
106 minutes, next try in 25 131 minutes, next try in 25 156 minutes, next try in 25 181 minutes, next try in 25 206 minutes, next try in 25 231 minutes, next try in 25 256 minutes, next try in 25 281 minutes, next try in 25 306 minutes, next try in 25
PL/SQL procedure successfully completed.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jan 13 1999 - 08:44:47 CST