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