using the _job_queue_interval in Oracle 9i

[ssmith]

We want to run a job every 1 second, but as job_queue_interval in Oracle 9i is obsolete, the default is now 5 seconds. If we alter the hidden parameter _job_queue_interval to 1 second, the job still runs every 1-3 seconds. Why? and What impact does this parameter have? Thanks.

[jmodic]

What kind of job is this you want to run every second?? Are you sure it will allways finish in less than 1 second?

[ssmith]

Thanks. You have probably just 'hit the nail on the head', the job could take longer than a second, although our client 'swears' that is doesn't. Another question, if you have job_queue_processes=20, and the hidden parameter _job_queue_interval=1, does this mean that Oracle will wake up all 20 processes (j001-j020) every second?

[ssmith]

Apparently, the job is finishing in milliseconds, so this cannot be the cause.

[jmodic]

I would try to experiment a little if I were you.

Option 1:
Create a procedure with an infinite loop. Inside the loop, do the following:
a) read the current time from DBMS_UTILITY.GET_TIME (or directly from v$timer)
b) execute the procedure you need to run every 1 second
c) read the time again
d) if 1 second has allready elapsed from point a, repeat everyting, starting at point a); If 1 second has not yet elapsed, loop few "dead cicles" (perform some useless, cpu-nonintensive work that lasts for few hundreds of second) end return to point c)

Option 2:
Submit 10 jobs with the same WHAT parameter and the same interval, that is each job should repeat itself every 10 seconds. Starttime of each job should be exactly 1 second appart.

[julian]

Just curious: what is that job doing? What do you need to do each second?

[ssmith]

The job is processing messages, therefore checking a table to see if it needs to process any messages. What about using dbms_lock.sleep(1) within the procedures. What effect will this have?

[julian]

Originally posted by ssmith
The job is processing messages, therefore checking a table to see if it needs to process any messages. What about using dbms_lock.sleep(1) within the procedures. What effect will this have?

Oracle introduced powerful queuing mechanisms where messages can be exchanged between different programs. They called it Advanced Queuing AQ.