BTW - not sure about those figures being quoted as 'healthy' - we
regularly run with 1 million in deferred and we run a deferred process
cleanup every 6 hours.
Its all about system usage and what you use it for. We experience no
performance problems with such high numbers in these tables. But then we
are a very big organisation.
-----Original Message-----
From: ora-apps-dba-bounce@xxxxxxxxxxxxx
[mailto:ora-apps-dba-bounce@xxxxxxxxxxxxx] On Behalf Of Jurijs
Velikanovs
Sent: 16 June 2006 13:00
To: ora-apps-dba@xxxxxxxxxxxxx
Subject: Re: EXPIRED messages in the WF_NOTIFICATION_OUT queue
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
On 6/16/06, Jurijs Velikanovs <j.velikanovs@xxxxxxxxx> wrote:
> 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
--------------------------------------------------------
If you are not an intended recipient of this e-mail, please notify the sender,
delete it and do not read, act upon, print, disclose, copy, retain or
redistribute it. Click here for important additional terms relating to this
e-mail. http://www.ml.com/email_terms/
--------------------------------------------------------
