Monday, June 11, 2007

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

2 comments:

Peter K said...

Forgive me as I don't know E-Biz that well but by closing all these WFERROR without looking at them, you managed to improve performance when listing notifications on the OAM page.

If that is the case, wouldn't it be simpler to just turn off notification? This sounded like a make work approach.

fhasweh said...

hi peter, thanks for the comments.
well you are correct you need to look at them and by closing them i am not deleting them they are still there on the system but with close status just to make sure the open notification list will not look at the unnecessary wferror open notifications, and we cannot turn it off because we need those notifications. For more info. You can look at note 272067.1 where you can more specific and put the Message_Name='DEFAULT_EVENT_ERROR'

fadi