Open Workflow notifications list is very slow

Here I am publishing a note which I already published on metlaink through the Customer Knowledge Exchange program the note id is 428573.1, they had this program a couple of months ago and I managed to publish two notes and I will be sharing them here with you.

I faced a slow performance issue which only applies on the notifications list page, opening the workflow notifications list to see a list with two notifications when the problem happened used to take a lot of time (more that 20 minutes and most of the times it used to time out) so I followed the below to solve the issue

SQL> select message_type,count(*) from WF_NOTIFICATIONS group by message_type;

MESSAGE_ COUNT(*)
-------- ----------
AZNM000 3
FERASWF 8
POAPPRV 121
POERROR 29
PORCPT 38
POSDSALE 332
REQAPPRV 131
WFERROR 1137436

8 rows selected.
SQL> select count(*) from WF_NOTIFICATIONS where status='OPEN'
and MESSAGE_TYPE='WFERROR'

998976 rows selected.

I find out that I 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 I 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' ;

998976 rows created
SQL> commit;

After that I tried the notifications list page it did not take more than 3 seconds and every thing went back to normal, so I 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 I am make sure that all the sysadmin notifications are closed and I also have monthly task to check that the above job is running.

i hope that helped
fadi