Too much selects when using oracle aq and spring-jms

Question

We started to use oracle AQ and spring-jms and it works fine. But when ~1000 messages goes though my queue BUS_BILLING_RECEIVE_QT there is 110000 selects I wrote below:

select /*+ INDEX(TAB AQ$_BUS_BILLING_RECEIVE_QT_I) */ tab.rowid, tab.msgid, tab.corrid, tab.priority, tab.delay, tab.expiration , tab.retry_count, tab.exception_qschema, tab.exception_queue, tab.chain_no, tab.local_order_no, tab.enq_time, tab.time_manager_info, tab.state, tab.enq_tid, tab.step_no, tab.sender_name, tab.sender_address, tab.sender_protocol, tab.dequeue_msgid, tab.user_prop, tab.user_data from "WORKLOAD_PROD"."BUS_BILLING_RECEIVE_QT" tab where q_name = :1 and (state = :2 ) order by q_name, state, enq_time, step_no, chain_no, local_order_no for update skip locked

It causes performance problem... May be sombody knows which produces this select, and how I can reduce this enormous amount?

Answer 1

This statement is the internal SQL statement that's behind the dbms_aq.dequeue statement, when the dequeue_mode (field of dequeue_options_t type) is either LOCKED or REMOVE (not BROWSE or REMOVE_NODATA).

To reduce the amount, you'll have to dequeue less often.

Answer 2

See https://erikwramner.wordpress.com/2016/06/08/oracle-aq-jms-performance, the part about receive timeout. In short when a message arrives Oracle wakes ALL waiting sessions and they will all issue the same select. Only one will succeed, but this is very expensive. We have reported it to Oracle but they don't want to fix it (by waking up one thread, compare notify and notifyAll in Java).

See https://jira.spring.io/browse/SPR-14225 and in particular the pull request I submitted for a fix in Spring. Our current solution is to subclass DefaultMessageListenerContainer, polling with receiveNoWait and sleeping for short periods in Java. That greatly reduces the load on the database. Alternatively you can reduce the number of listeners, but normally that is not an option!