| Oracle Advance Queue consumes messages very slowly [message #658233] |
Wed, 07 December 2016 06:46  |
 |
panos9
Messages: 2
Registered: December 2016
|
Junior Member |
|
|
Hello,
we are facing an issue with a PL/SQL query. The issue here is that we have a listener that listens to an oracle advanced queue. The queue is enqueuing properly and dequeueing with a very slow rate.
The session we see by this process in our monitors is created by the following query and seem stuck:
SELECT msg_enq_tid
FROM "CCS3"."AQ$_QT_UNIMPORTED_PAYMENTS_I" qidx1
WHERE subscriber# = :1 AND name = :2 AND queue# = :3
AND txn_step# =
(SELECT MIN (qidx2.txn_step#)
FROM "CCS3"."AQ$_QT_UNIMPORTED_PAYMENTS_I" qidx2
WHERE qidx2.subscriber# = qidx1.subscriber#
AND qidx2.name = qidx1.name
AND qidx2.queue# = qidx1.queue#
AND qidx2.msg_enq_tid = qidx1.msg_enq_tid)
ORDER BY msg_enq_tid,
sender#,
txn_step#,
msg_enq_time,
msg_step_no,
msg_chain_no,
msg_local_order_no
Can you please give us some ideas in order to figure out the reason of the delay?
FYI
1. We have gathered table stats for the respective queue's related tables
2. We have restarted dequeue/enqueue of queue
3. We have also restarted pl sql job that calls the queue's listener
4. We have also recreated the queue
None of the above worked.
Furthermore we see the following waits:
wait for unread message on broadcast channel
db file sequential read
jobq slave wait
latch: cache buffers chains
Thank you in advance!
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
