Open Workflow Notifications list is very slow in Oracle EBS
A slow performance issue is faced while opening the notifications list page on Oracle EBS, opening the workflow notifications in Oracle EBS used to take a lot of time (more that 5 to 10 minutes and sometimes is used to time out) So I followed the below it may solve the issue slowness in workflow
SQL> select message_type,count(*) from WF_NOTIFICATIONS group by message_type order by count(*) desc;
MESSAGE_ COUNT(*)
-------- ----------
WFERROR 228769
XXARWF 148949
XXCIAWF 125796
IRC_NTF 92510
HRSSA 87831
POAPPRV 64052
XXETC032 41693
XXPACEAR 41049
XXETC048 39971
XXFAWOWF 35954
XXETC008 30925
XXETC052 28937
FNDCMMSG 27676
XXETC002 13354
XXETC010 12124
XXSPINVN 11822
REQAPPRV 10209
XXETC006 10001
IRC_WF 8900
XXETC043 8194
XXAPPABA 7805
GLBATCH 5683
OMERROR 4455
XXETC211 4169
XXETCAD1 3613
FUNRMAIN 3455
XX3ALRTS 2025
XXETC030 1600
XXPCAWF 1459
XXPORLSE 1418
XXETC036 1223
XXETC049 1183
XXSUPBNK 1034
POSNOTIF 951
XXFACHGE 858
UMXLHELP 846
XXAPYMNT 662
PAPROWF 662
XXETC099 637
XXOTL001 611
XXETC053 607
XXALMRFS 507
XXOTL005 479
XXETC204 475
MRNAPPRV 471
XXETCIRC 455
XXRCPTWF 429
XXETC047 418
PAWFPPWP 392
POSSPM1 387
XXBRAPWF 360
XXMRUNPO 337
POERROR 323
XXETC057 305
PAWFCISC 297
OECHGORD 291
POSREGV2 280
XXISRTV 271
XXETC202 249
XXETC050 249
XXVVIP01 212
UTLPYRCT 162
XXETC011 132
XXETC007 127
XXBLDSTL 123
WFTESTS 114
XXALMSTC 100
POSBPR 100
XXETC060 98
XXETC014 97
XXETC037 88
HRSFL 87
XXTDNOC 81
RCVDMEMO 74
CS_MSGS 73
XXVAPPWF 71
XXMISCEL 71
XXETC201 69
XXIPAPRV 69
HRWPM 67
XXETC031 66
XXETC051 62
XXETC070 60
XXSUPVAT 50
FUNIMAIN 42
XXGTFD01 39
UMXPXYNF 35
INVTROAP 32
IBEALERT 29
XXETC098 25
XXANETBA 24
AZNM002 23
XXETGP02 18
WFMAIL 17
FAXAPPRV 15
POSASNNB 14
XXALM_SC 13
XXGPSSA 10
XXETC040 9
XXETC038 8
AZNF003 8
AZNM000 7
XXAANTWF 6
XXCPWF 6
PORPOCHA 5
POSSPAPP 5
XXALMPO 4
CREATEPO 4
XXBILLNF 3
HXCEMP 3
XXRFQNOT 3
XXETC003 2
XXOTL002 2
HRCORE 2
AZNM004 2
BENCWBFY 1
UMXNTWF2 1
AZNF004 1
SQL> select count(*) from WF_NOTIFICATIONS where status='OPEN'
and MESSAGE_TYPE='WFERROR'
209138
we find out that we have a lot of the WFERROR message_type and most of them with an open status so I took a backup of the table just incase any thing went wrong using the following statement
SQL> CREATE TABLE WF_NOTIFICATIONS_bck AS SELECT * from WF_NOTIFICATIONS;
Table created.
Then we run the following update statement to set the status of all open notifications of the WFERROR type
SQL> update wf_notifications
set status='CLOSED', mail_status='CANCELED', end_date=sysdate
where status='OPEN'
and MESSAGE_TYPE='WFERROR' ;
209138 rows updated
SQL> commit;
After that we tried the notifications list page it did not take more than 3 seconds and every thing went back to normal, so we scheduled a database job that run every month to close all the open notifications with message_type wferror.To avoid this problem from happing again we make sure that all the sysadmin notifications are closed and we also have monthly task to check that the above job is running.