1

Does anyone have an idea on why I see PROCESSED messages here:

select 'MFS_MISSION' as queue_name, CONSUMER_NAME, MSG_STATE, count(*) from aq$MFS_MISSION_QTAB  group by consumer_name, MSG_STATE

 QUEUE_NAME     CONSUMER_NAME     MSG_STATE     COUNT(*)    
 -------------  ----------------  ------------  ----------- 
 MFS_MISSION    T1                PROCESSED     143216      
 MFS_MISSION    S1                PROCESSED     377208 

but not here:

select dq.queue_table, dq.queue_type, dq.name, v$aq.waiting, V$AQ.ready, V$AQ.expired, V$AQ.total_wait, V$AQ.average_wait 
from V$AQ, dba_queues dq where dq.qid=v$aq.qid and dq.name like '%MISSION%'

 QUEUE_TABLE       QUEUE_TYPE       NAME                    WAITING     READY     EXPIRED     TOTAL_WAIT     AVERAGE_WAIT    
 ----------------  ---------------  ----------------------  ----------  --------  ----------  -------------  --------------- 
 MFS_MISSION_QTAB  EXCEPTION_QUEUE  AQ$_MFS_MISSION_QTAB_E  0           0         0           0              0               
 MFS_MISSION_QTAB  NORMAL_QUEUE     MFS_MISSION_QUEUE       0           539002    0           0              0               
 MFS_MISSION_QTAB  EXCEPTION_QUEUE  AQ$_MFS_MISSION_QTAB_E  0           0         0           0              0               
 MFS_MISSION_QTAB  NORMAL_QUEUE     MFS_MISSION_QUEUE       0           480333    0           0              0

The queue has retention of 0, so messages should either be PROCESSED or disappear altogether as soon as they are committed. The count of messages tells me they have accumulated over course of months.

I'll be glad for any pointers, esperience or insights.

Edit: Oracle Database 10g Release 10.2.0.3.0

2
  • 1
    Looks like you can get results like this when there are several consumers to a queue. However I still don't get in what way the view is different to yield different results.
    – slovon
    Jul 2, 2009 at 13:21
  • Could you post the queue definition DDL and SQL you used to establish this queue? Dec 4, 2009 at 18:00

1 Answer 1

0

In a multiple consumer queue, messages remain in the queue until all subscribers have consumed the message. I would seem like the discrepancy between v$aq and aq$MFS_MISSION_QTAB would indicate that there are messages being shown in v$aq that are still waiting for all of the subscribers to consume so they can be marked as processed.

Also, you have 2 entries in aq$MFS_MISSION_QTAB for you MFS_MISSION_QUEUE. Do these have different Owners are are you using RAC?

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .