OraFAQ Forum: Performance Tuning » Oracle Advance Queue consumes messages very slowly

Home » RDBMS Server » Performance Tuning » Oracle Advance Queue consumes messages very slowly (Oracle 10g, HP-UX/ia64)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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!

Report message to a moderator

Re: Oracle Advance Queue consumes messages very slowly [message #658241 is a reply to message #658233]

Wed, 07 December 2016 07:58

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: Oracle Advance Queue consumes messages very slowly [message #658258 is a reply to message #658241]

Wed, 07 December 2016 10:11

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.

Report message to a moderator

Previous Topic:	query Dead Slow
Next Topic:	stale stats

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 01:30:17 CDT 2025