Performance Degradation when the Workflow Background Process is Running

Performance Degradation when the Workflow Background Process is Running [ID 743338.1]
Modified 21-JUL-2010 Type PROBLEM Status PUBLISHED
In this Document
Symptoms
Cause
Solution
References

Applies to:

Oracle Workflow Cartridge - Version: 11.5.10.0 to 12 - Release: 11.5 to 12
Information in this document applies to any platform.
Checked for relevance on April 21st 2010
FNDWFBG - Workflow Background Process
Symptoms

When the Workflow Background Process concurrent program is running, you notice a degradation of e-business suite performance. In the meantime, the database diagnostic script STATSPACK reports a very high number of Buffer Gets done by FNDWFBG module.

STATSPACK output shows FNDWFBG module as the top performer of Buffer Gets:

----------------------------------------------------------------

SQL ordered by Gets for DB:
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
35,914,812 22 1,632,491.5 99.3 62.32 60.92 2076298416
Module: FNDWFBG
BEGIN WF_ENGINE.BACKGROUNDCONCURRENT( :errbuf, :rc,:A0,:A1,:A2,:
A3,:A4,:A5); END;

35,838,763 22 1,629,034.7 99.1 61.74 60.34 2254475455
Module: FNDWFBG
SELECT /*+ ORDERED USE_NL (WIASP WI WPAP WAP) INDEX
(WIASP WF_ITEM_ACTIVITY_STATUSES_N1) */ WIASP.ROWID ROW_ID FROM
WF_ITEM_ACTIVITY_STATUSES WIASP, WF_ITEMS WI, WF_PROCESS_ACTIVIT
IES WPAP, WF_ACTIVITIES WAP WHERE WIASP.ITEM_TYPE = :B2 AND WIAS
P.PROCESS_ACTIVITY = WPAP.INSTANCE_ID AND WPAP.ACTIVITY_ITEM_TYP
--------------------------------------------------------------------------------
Cause

The issue is caused by the following:

Workflow Background Process is launched every minute with the following parameters:

- Item Type=NULL
- Minimum Threshold = NULL
- Maximum Threshold = NULL
- Process Deferred = Y
- Process Timeout= Y
- Process Stuck = Y

Unpublished bug 5016036 "WF: HIGH BUFFER GETS IN FNDWFBG MODULE" explains that running the Workflow Background Process with "Process Stuck = Y" is extremely expensive and consequently this should be done sparingly.

This means the Workflow Background Process should run with parameter "Process Stuck = Y" much less frequently than the Workflow Background Process with parameter "Process Deferred = Y" or "Process Timeout = Y".

Solution

To implement the solution, please execute the following steps:

1) Using System administrator responsibility, navigate to Concurrent\Requests form.

2) In this screen, remove the scheduling for the existing Workflow Background Process.

3) Still using this screen, schedule a new Workflow Background Process to be executed every 5 minutes with the following parameters:
- Item Type = NULL
- Minimum Threshold = NULL
- Maximum Threshold = NULL
- Process Deferred = Y
- Process Timeout = N
- Process Stuck = N

This Workflow Background Process will run every 5 minutes and will process only the deferred activities.

4) Then schedule a new Workflow Background Process to be executed every 60 minutes with the following parameters:
- Item Type = NULL
- Minimum Threshold = NULL
- Maximum Threshold = NULL
- Process Deferred = N
- Process Timeout = Y
- Process Stuck = N

This Workflow Background Process will run every 60 minutes and process only the timed out workflow activities.


5) Schedule a new Workflow Background Process to be executed every week with the following parameters:
- Item Type = NULL
- Minimum Threshold = NULL
- Maximum Threshold = NULL
- Process Deferred = N
- Process Timeout = N
- Process Stuck = Y

This Workflow Background Process will run once a week and process only the stuck workflow processes. In addition it is better to schedule it so it runs when the load on the system is low.


Note: if a given item type has very high volumes of deferred activities to process, in addition to the above scheduling you may need to schedule an additional Workflow Background Process to handle the deferred activities of this item type.