We are facing issues while de-queue message from Q-Table.
Right now one of the job en-queue around 55,00,000 messages in one hours and we need to process these message on priority basis.
Currently listener pickup around 300 messages and it grows up to 63000 messages per run.
After processing around 35,00,000 messages performance degrade (20,00,000 Left) listener start picking up less message for de-queue. And it come to 300 message per run.
Can someone tell me why this happen?
It is probably because of the size of the queue table and related tables and possibly your own DQ options, few things to consider:
- What specific version of Oracle do you have?
- Have you checked how big the queue table (and related IOT's) are in DBA_SEGMENTS? If the segments are very large consider shrinking / coalesce before restarting your DQ. If you have already DQ'd 30 million messages there is probably quite a bit of space to be reclaimed. Have a look at MOS note How do I reduce the High Watermark (HWM) of Advanced Queueing objects? [ID 421474.1] and Procedure to manually Coalesce all the IOTs/indexes Associated with Advanced Queueing tables to maintain Enqueue/Dequeue performance, reduce QMON CPU usage and Redo generation [ID 271855.1]
- Can you run more than one DQ process?
- Can you make use of bulk DQ?
- What navigation method are you using (FIRST_MESSAGE or NEXT_MESSAGE) for DQ - FIRST_MESSAGE will perform queries on the queue repeatedly, NEXT_MESSAGE doesn't ?