Monday, October 15, 2007

WF_DEFERRED Queue is having few million messages in "Ready" State

I would like to publish this blog post in the form of a set of questions and answers that will help you resolve a situation if "Ready" state messages in WF_DEFERRED queue are in few millions and Workflow Deferred Agent listener is down from years which caused the pile up of records in WF_DEFERRED Queue.


Q1. How do I find out in first place WF_DEFERRED Queue is having millions of records in "Ready" state ?

The simplest way to find out this is by executing the script $FND_TOP/sql/wfver.sql. In the spool file that the script creates, look for

--Clip--

Notification Subsystem Queue Info This requires package WF_QUEUE to be valid ______________________________________________________________________________ WF_DEFERRED messages ready:8406878, waiting:16, expired:0, undeliverable:0, processed:2586

--End Clip--

The highlighted one indicates the number of messages in "Ready" state. If you see big numbers in this section of wfver.sql output, this is a matter of concern. Get ready to do an operation ;)

Q2. What happens if the number of messages in this queue are very high.

One of the obvious things that can happen when this queue is so big is the delay in processing of the messages. Just imagine if the Order Management related messages are not processed on time and delay of few hours in notifying the users.

Q3. What is the reason for this queue to grow up in such huge numbers ?

"Workflow Deferred Agent Listener" is the one which processes the messages in WF_DEFERRED queue. If the listener is down, you should try and bring it up. If the number of messages in WF_DEFERRED queue are very huge then this listener fails to come up or dies immediately after few minutes.

Q4. How do I bring up "Workflow Deferred Agent Listener" in this situation.

Believe me. It is not easy.

Step 1: Execute the following script to find out event wise "Ready" state messages.

--Script Begins here--

set linesize 100
col corrid format a50
col state format a15
col count format 99999999
select NVL(substr(wfe.corrid,1,50),'NULL - No Value') corrid, decode(wfe.state,0,'0 = Ready',1,'1 = Delayed','2=Retained','3=Exception',to_char(substr(wfe.state,1,12))) State, count(*) COUNT from applsys.wf_deferred wfe group by wfe.corrid, wfe.state ;

--Script Ends here--

Note: This script might take longtime depending up on the number of messages in WF_DEFERRED Queue.

You should see the output something like this.

CORRID STATE COUNT
------------------------------------------------------------ -------------------- ---------
APPS:oracle.apps.ar.adjustments.Adjustment.create 0 = Ready 72488
APPS:oracle.apps.ar.applications.CashApp.apply 0 = Ready 557692
APPS:oracle.apps.ar.applications.CashApp.unapply 0 = Ready 145695
APPS:oracle.apps.ar.applications.CreditMemoApp.app 0 = Ready 58107
APPS:oracle.apps.ar.applications.CreditMemoApp.una 0 = Ready 2512
APPS:oracle.apps.ar.batch.AutoInvoice.run 0 = Ready 32950

Step 2: This step is to process the events manually. The job of "Workflow Deferred Agent Listener" is being carried out manually by executing the following scripts.

--Script Begins here--

spool oracle.apps.ar.adjustments.Adjustment.create.log
set serveroutput on size 100000;
begin wf_log_pkg.wf_debug_flag := TRUE;
wf_event.listen(p_agent_name =>'WF_DEFERRED',p_correlation=>'oracle.apps.ar.adjustments.Adjustment.create');
end;
/
commit
/

--Script Ends here--

Execute the above script for all the events that are in the "Ready" state. Once after the count of "Ready" state messages comes down to some number, try starting "Workflow Deferred Agent Listener". This process is really time consuming, so plan out your vacation after the issue is resolved. You need a break !! :)

Q5. What do you as an Apps DBA need to do when users complain that OM notifications are stuck.

Use the following query to check to see whatever the users are saying is correct ;).

SQL> select message_type,count(1) from wf_notifications where status='OPEN' and mail_status='MAIL' group by message_type;

MESSAGE_ COUNT(1)
-------- ----------
FNDCMMSG 6
IBEALERT 32
JTFTASK 2441
OEOH 112
OEOL 462
OKCAUKAP 116
POAPPRV 121
REQAPPRV 40

MESSAGE_ COUNT(1)
-------- ----------
WFERROR 4524

Q6. What can you do to speed up the processing of sending notifications.

Execute the following script manually to speedup delivering of notifications.

--Script Begins here--

spool oracle.apps.wf.notification.log
set serveroutput on size 100000;
begin wf_log_pkg.wf_debug_flag := TRUE;
wf_event.listen(p_agent_name => 'WF_DEFERRED',p_correlation=>'oracle.apps.wf.notification.%');
end;
/

--Script Ends here--

Enjoy !!



9 comments:

Anonymous said...

Hi Madhu,

Finest way of explaination. Hats Off to you.

Regards,
Irfan.

Vijay Reddy said...

Hi Madhu,
We've the same kind of situation. But the queue is not huge as you said. It is having around 100 events in deferred state and not getting processed.
We've run the same script
wf_event.listen(p_agent_name => 'WF_DEFERRED',p_correlation=>'oracle.apps.jtf.cac.task.createTask');

but this is taking hell out of time. Whole night it has processed only 50 events. after stopping it again the event count has gone up. can you provide any help in this regard? If you have any ideas please drop me a mail vijaykumar.mallareddy@gmail.com

Regards,
Vijay

Madhu Sudhan said...

Dear Vijay,

Thanks for writing. Try if this helps you. Execute the following and then run your listen script.

exec dbms_aqadm.alter_queue (queue_name => 'APPLSYS.WF_DEFERRED',retention_time => 0);

Regards,
Madhu

Note: Please see my disclaimer in the blog.

Madhu Sudhan said...

Dear Vijay,

Also, this is the job of a Workflow Deferrend Agent Listener. Please ensure the WF Deferred Agent Listener up and running.

Thanks,
Madhu

Unknown said...

Hi Madhu,
Thanks a lot for the post, it really helped me. Can you tell me is it recommended to run wf_event.listen after bringing down Agent Listener?

Madhu Sudhan said...

Dear Ashwin,

If both the Deferred Agent listeners are healthy and are doing their job, then you will not have to run the "listen" package manually. Please investigate why so many messages are in "Ready" state and hold a meeting with functional folks and see if all or any of the events that are unused need to be disabled. I would recommed you to work with Oracle Support.

Thanks,
Madhu

Anonymous said...

Hi Madhu,
this is really a good info about workflow.
please send me a alert mail to know about the new posts whenever posted to refer.
here is my mail: anthonyreddy.thumma@gmail.com


Anthony Reddy,
Apps DBA,
SIIX Singapore pvt. ltd

Madhu Sudhan said...

Dear Anthony Reddy,

Infact there is easy way to do this. Login to reader.google.com with your gmail username and password and subscribe to any blog that you are interested in. reader.google.com will show you the unread blog posts that you have subscribed to.

Happy redaing !!

Regards,
Madhu

Siva said...

Great Post. Very informative.