Using an AQ commit ordered queue. Message type of LOB.
We are queuing and dequeuing simultaneously to one AQ queue. A stored procedure running on the same server as the queue is queuing XML messages faster than a client JDBC process can dequeue.
Under normal loads, dequeuing (including some processing we're doing) happens at a rate of about 80 per second. A normal load is 10,000 or so messages in a few minutes.
When there is more XML to queue, the queue can contain backlog of 100K to over a million messages. Dequeue speeds creep when the message backlog gets this high. We're using the DBMS_AQ package via JDBC calls to dequeue. We're using the 'first-message', 'next-message' method. Info on the internet suggested calling dequeue with first-message, then next-message 1000 times. Then use first-message again to get the next message, then next-message 1000 times, etc.
Based on other info on the internet, we've tried increasing the STREAMS_POOL_SIZE (I think things slowed down further after increasing the size to 200M).
I've also tried to recalculate tables stats on the lob-tables associated with the queues according to OTN Doc ID 1363145.1. I read associated bug 12652364. Recalculating stats didn't help.
We've tried different dequeue methods too. From JDBC I tried building a query against the lob-table to get a list of msgid's and dequeue via DBMS_AQ package using a specific msgid. I figured 'point queries' would be fastest, but no luck. Even with normal loads, first/next message method is faster.
Any help in increasing dequeue speeds or tips on debugging the problem would be greatly appreciated.
Platform: Win 2008 Server
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production