Home / server / Questions / 29756
Accepted
slovon
Asked: 2009-06-23 06:21:26 +0800 CST

Oracle AQ - why I don't see processed messages in global view?

  • 772

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

oracle

1 Answers

  1. Best Answer

    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?

    • 0