Re: EXPIRED messages in the WF_NOTIFICATION_OUT queue
- From: "Jurijs Velikanovs" <j.velikanovs@xxxxxxxxx>
- To: ora-apps-dba@xxxxxxxxxxxxx
- Date: Fri, 16 Jun 2006 13:05:48 +0100
Another good thing to check is Active Workflow Processes count in the system.
If you got thousands of active processes it is indication that you
need to investigate it.
SELECT DECODE (a.end_date, NULL, 'NOT FINISHED', 'FINISHED')
,substr(t.display_name,0,30) display_name
,a.item_type
,COUNT (*)
,min(a.begin_date) min_begin_date
,max(a.begin_date) max_begin_date
FROM apps.wf_items a
,applsys.wf_item_types_tl t
WHERE 1 = 1
AND t.NAME = a.item_type
AND a.end_date IS NULL
GROUP BY DECODE (a.end_date, NULL, 'NOT FINISHED', 'FINISHED')
,t.display_name
,a.item_type
ORDER BY COUNT (*)
/
DECODE(A.END DISPLAY_NAME ITEM_TYP COUNT(*)
MIN_BEGIN_DATE MAX_BEGIN_DATE
------------ ------------------------------ -------- ----------
------------------- ----------------
NOT FINISHED XXLG Loading Process XX_LOAD 1
2006.04.14 09:30:28 2006.04.14 09:30:28
NOT FINISHED Business Rule Monitor Task Pro JTFBRMPR 2
2006.05.10 16:31:17 2006.05.10 16:31:17
NOT FINISHED PO Approval Error POERROR 106
2005.11.22 10:20:42 2006.06.15 15:25:36
NOT FINISHED System: Error WFERROR 170
2006.05.19 11:14:43 2006.06.16 11:56:05
NOT FINISHED Workflow for Task Manager JTFTASK 290
2006.04.12 17:09:13 2006.06.16 11:57:47
NOT FINISHED PO Approval POAPPRV 709
2003.12.10 08:41:43 2006.06.16 12:35:47
NOT FINISHED Service Request SERVEREQ 1373
2006.04.18 17:58:52 2006.06.16 13:02:24
7 rows selected.
APPS:LIVE>
On 6/16/06, Jurijs Velikanovs <j.velikanovs@xxxxxxxxx> wrote:
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
--
Yury
+44 7738 013090 (GMT)
============================================
http://otn.oracle.com/ocm/jvelikanovs.html
Other related posts:
