Home » RDBMS Server » Performance Tuning » Oracle Advance Queue consumes messages very slowly (Oracle 10g, HP-UX/ia64)
Oracle Advance Queue consumes messages very slowly [message #658233] Wed, 07 December 2016 06:46 Go to next message
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!
Re: Oracle Advance Queue consumes messages very slowly [message #658241 is a reply to message #658233] Wed, 07 December 2016 07:58 Go to previous messageGo to next message
BlackSwan
Messages: 25797
Registered: January 2009
Location: SoCal
Senior Member

ORAFAQ tuning below -
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) ddl for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Oracle Advance Queue consumes messages very slowly [message #658258 is a reply to message #658241] Wed, 07 December 2016 10:11 Go to previous message
panos9
Messages: 2
Registered: December 2016
Junior Member
@BlackSwan thank you for your prompt reply. We resorted to the last means we had to fix the issue, we restarted the db server and after this the messages are consumed quite fast.

Thank you again for taking the time to answer to our question.
Previous Topic: query Dead Slow
Next Topic: stale stats
Goto Forum:
  


Current Time: Sat Jan 20 14:20:17 CST 2018

Total time taken to generate the page: 0.01226 seconds