using the _job_queue_interval in Oracle 9i
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: using the _job_queue_interval in Oracle 9i

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258

    using the _job_queue_interval in Oracle 9i

    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.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    What kind of job is this you want to run every second?? Are you sure it will allways finish in less than 1 second?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    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?

  4. #4
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    Apparently, the job is finishing in milliseconds, so this cannot be the cause.

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: using the _job_queue_interval in Oracle 9i

    Just curious: what is that job doing? What do you need to do each second?
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  7. #7
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    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?

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width