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;
8 rows selected.
SQL> select count(*) from WF_NOTIFICATIONS where status='OPEN'
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;
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
and MESSAGE_TYPE='WFERROR' ;
998976 rows created
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