BTW: In your system are Oracle Apps DBA you need to keep an eye on all most active QUEUES (as Apps using AQ quite rapidly).
In a healthy system active messages count in such queues as WF_DEFERRED suppose to be minimal ~100-1000. If you have got bigger figure than that you need to investigate why there is unprocessed messages.
APPS:LIVE> SELECT msg_state, count(*) APPS:LIVE> FROM applsys.aq$WF_DEFERRED APPS:LIVE> group by msg_state;
MSG_STATE COUNT(*) ------------- ---------- PROCESSED 2824 READY 1 WAIT 271
APPS:LIVE>
Check you system and enjoy, Yury
Hello team,
Please take a look on the following output. There are 795 messages in the WF_NOTIFICATION_OUT queue in our LIVE system. Some of them have been put in the queue just today. I wonder how messages in that queue become EXPIRED? Is anybody experienced the same behavior? Those messages are not dequeued and are not sent to end users. What would be the right method to troubleshoot those type of messages?
Thank you in advance, Yury
APPS:LIVE> alter session set nls_date_format='YYYY.MM.DD HH24:MI:SS';
Session altered.
APPS:LIVE> APPS:LIVE> SELECT msg_state, count(*) APPS:LIVE> FROM applsys.aq$wf_notification_out APPS:LIVE> group by msg_state;
MSG_STATE COUNT(*) ------------- ---------- EXPIRED 795 PROCESSED 633
APPS:LIVE> APPS:LIVE> SELECT o.enq_time APPS:LIVE> FROM applsys.aq$wf_notification_out o APPS:LIVE> where o.msg_state = 'EXPIRED' APPS:LIVE> and trunc(o.enq_time) = trunc(sysdate);
ENQ_TIME ------------------- 2006.06.16 00:46:15 2006.06.16 00:46:15 2006.06.16 00:46:15
APPS:LIVE>
-- Yury +44 7738 013090 (GMT) ============================================ http://otn.oracle.com/ocm/jvelikanovs.html
-- Yury +44 7738 013090 (GMT) ============================================ http://otn.oracle.com/ocm/jvelikanovs.html