Suggestion for Oracle AQ dequeue approach

Question

I have a need to dequeue messages coming from an Oracle Queue on a continuous basis.

As far as I could imagine, we can deuque the message in two ways, either through Asyncronous Auto-Notification approach or by manual polling process where one can dequeue one message at a time.

I can't go for Asyncronous notification feature as the number of messages it receives could go upto 1000 within 5 mintues during peak hours and I do not want to overload the database by spawning multiple callback procedures in the background.

With the manual polling process,I can create a one-time scheduler job that runs 24*7 which calls a stored proc that dequeus the messages in a loop in WAIT mode(kind of listening for a message). The problem with this approach is that 1) the scheduler job runs continously and occupies one permanent job slot 2) the stored procedure does not EXIT as it runs in a loop waiting for messages.

Are there any alternative/better solutions where I do not need to have a job/procedure running continuously looking for messages?

Can I use auto-notification approach to get notification for the very first message,un-subscribe the subscriber and dequeue further messages and subscribe to the queue again when there are no more messages ? Is this a safe approach and will i lose any message in between subscription and un-subscription ? BTW, We use Oracle 10gR2 database, so I can't use PURGE ON NOTIFICATION option.

Appreciate your expert solution!!

Answer 1

You're right, it's not a good idea to use auto-notification for a high-volume queue.

At one client I've seen a one-time scheduler job which runs 24*7, it seems to work reasonably well, and they can enqueue a special "STOP" message (which goes to the top of the queue) that it listens for and stops processing messages.

However, generally I'd lean towards a job that runs regularly (e.g. once per minute, or whatever granularity is suitable for you) which would dequeue all the messages. I'd put the dequeue in a loop with a loop counter and a "maximum messages" limiter based on the maximum number of messages you'd expect in a 1-minute period. The job would keep processing messages until (a) there are no more messages in the queue, or (b) the maximum limit has been reached.

You can then set the schedule for the job based on the maximum delay you want to see between an enqueue and a dequeue. E.g. if it doesn't matter if a message isn't processed within 5 minutes, you could set the job to run once every 5 minutes.

The maximum limit needs to be quite a high figure - e.g. 10x or 100x the expected maximum number - otherwise a spike could flood your queue and it might not keep up. The idea of the maximum limit is to ensure that the job never runs forever. This should give ops enough time to detect a problem with the queue (e.g. if some rogue process is flooding the queue with bogus messages).