Why can't I see the classic view?

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

• 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

• 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?