Speeding up and Purging Workflow

Speeding up and Purging Workflow

Andy Tremayne and Steve Mayze
An enhanced extract from The Oracle Applications Tuning Handbook ISBN 0-07-212549-7

Updated patch list and script notes on 14/02/2002
Added patch reference 2561517 on 17/01/2003
Added patch reference 2755885 on 08/04/2003

Contents:

Performance Profile Options
Performance Patches and Histograms
Purging Workflow Data
Investigative Queries
Dealing with Workflow errors
Scripts to create Histograms

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

Performance Profile Options
Some products of Oracle Applications have utilized the internal features of Oracle Workflow and brought them to the surface as configurable application profile options. Two profile options have historically affected performance:

Account Generator: Run in Debug Mode
Purchasing: Workflow Processing Mode
Account Generator: Run in Debug Mode
This controls the Forced Synchronous mode of the Workflow Engine. Always ensure that this is switched off unless you are trying to debug the output. The two settings are as follows:
Yes The Account Generator workflows runs in audit mode and status information is saved to the Workflow history tables.
No The Account Generator workflows run in non-audit mode and no status information is saved, therefore increasing performance and reducing the wait time when generating an account.

Purchasing: Workflow Processing Mode
This profile option can be set to Online or Background:

Online The Workflow Engine will run the Purchase Order and Requisition workflows online when transactions are created.
Background This causes the purchase order and requisition workflows to be deferred to the Background Engine. This can increase throughput; but before setting this value, ensure that you have configured at least one Background Engine and that it is running.

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

Performance Patches and Histograms
Patch 2755885 program loops and child worklow processes are not purged when the parent process are still active
Patch 2561517 contains a performance update to 2136244, which addresses stuck processes.
Patch 1661521 changes literals to bind arguments for the HandleSenderror and setContext procedures
Patch 2070056 relates directly to purging performance
Patch 1953214 relates to performance in the Find Notifications screen
Patch 1384537 relates to purge rollback issues and commits after each purged item

Patch 1508871 for workflow includes a number of fixes that target performance (fixed 11.5.5)
Bug 1659212 discusses checking a number of workflow histograms and if necessary running the scripts below to create them and populate the corresponding statistics.

Check/create a histogram for ACTIVITY_STATUS in WF_ITEM_ACTIVITY_STATUSES
Check/create a histogram for MAIL_STATUS and STATUS in WF_NOTIFICATIONS
Scripts to create histograms can be found at the bottom of this note.


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

Purging Workflow Data

Purging Run-Time Information

When the engine executes any type of workflow other than Forced Synchronous, status information is stored into the Workflow history tables. Over time, this can amount to many thousands of rows; the growth of these tables depend on the complexity of the workflows and how often they are used.

The problems associated with large volumes of data can be avoided by regularly purging run-time information. There are two types of information:

Design time information This is the workflow definition information that was created within the Oracle Workflow Builder.
Run-time information This includes the status history, Item Attribute values, and notifications that are created during the execution of a workflow process.
Managing the volume of information is performed using Workflow Purge APIs. They only operate on obsolete run-time information, leaving all design-time information intact. Administration scripts are provided to operate on both run-time and design-time information. Both APIs and scripts are discussed in the following sections.
Workflow Purge APIs

The Purge APIs are a set of procedures contained in the WF_PURGE package. Table 14-1 lists some of the procedures available and the parameters that they accept, which control the amount of data to purge. In their simplest form, the purge procedures can be run with no parameters at all. For example, to purge both item and activity data for completed workflow processes use the following:

execute wf_purge.total;

The concept of workflow persistence was introduced in Oracle Workflow 2.5. The Persistence Type controls how long a status audit trail is maintained for each instance of the Item Type. The three types of persistence are Permanent, Temporary, and Synchronous. Synchronous, when run with no auditing, does not have any history information. The history information for workflows marked as Permanent can only be removed by WF_PURGE.TOTALPERM; the history information for workflows marked as Temporary is removed by WF_PURGE.TOTAL. For best performance, run WF_PURGE.TOTAL frequently, and WF_PURGE.TOTALPERM periodically, but be aware of your site’s policy on status history retention. Further details can be found in the Oracle Workflow User Guide.

Procedure Parameters Description
WF_PURGE.ITEMS Itemtype
Itemkey
Enddate Removes all run-time data associated with completed items.
WF_PURGE.ACTIVITIES Itemtype
Enddate Removes obsolete activities versions. These are versions of activities that are no longer used by any item.
WF_PURGE.NOTIFICATIONS Itemtype
Enddate Removes old notifications. These are notifications that are no longer used by any item.
WF_PURGE.TOTAL Itemtype
Itemkey
Enddate Purges both item and activity data.
WF_PURGE.TOTALPERM Itemtype
Itemkey
Enddate Deletes all eligible obsolete run-time data that has a persistence type of Permanent.
WF_PURGE.ADHOCDIRECTORY Enddate Purges all ad hoc users and roles that are not associated with a notification.


Table 1: WF_PURGE Procedures

Using the Generric Purge Procedures

You can run execute WF_PURGE.TOTAL from the SQL command prompt. This will not delete anything in process or active. However you should also run execute WF_PURGE.TOTALPERM which will delete any items with a persistence type of permanent and execute WF_PURGE.NOTIFICATIONS.

Note: If you run into problems such as running out of rollback, you will need to purge by item type or something else to limit the amount of data (committing between each iteration). .

Standard Purge Scripts

These scripts remove not only run-time data, but also Item Type and workflow definition information. Table 14-2 lists and describes some of the available scripts. Carefully consider the impact of running these scripts, as purging run-time information for incomplete workflow processes may result in the underlying application being placed into an unstable state. Any transactions that are incomplete when the process has been removed may not be able to be completed.

Caution: Extreme care should be taken when using these scripts!
Script Parameters Description
$FND_TOP/sql/wfrmitms.sql
$ORACLE_HOME/admin sql/wfrmitms.sql Itemtype
Itemkey Removes the status information for a specified Item Type and Item Key
$FND_TOP/sql/wfrmtype.sql
$ORACLE_HOME/admin /sql/wfrmtype.sql None - You will be prompted Purges all run-time information for a given item type

Table 2: Workflow Purge Scripts



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

Investigative Queries
The main tables of concern when dealing with workflows are WF_ITEM_ACTIVITY_STATUS and WF_ITEM_ATTRIBUTE_VALUES. The SQL statements below will help analyze their contents.

select item_type,activity_status,count(*)
from wf_item_activity_statuses
group by item_type,activity_status
ITEM_TYP ACTIVITY COUNT(*)
-------- -------- ----------
INVTROAP COMPLETE 8
WFERROR COMPLETE 413298
INVTROAP DEFERRED 1
CREATEPO ERROR 5
An understanding of WFERROR will help define what needs to happen next:

ACTIVE Nobody has reviewed these yet
COMPLETE Someone has responded to the error notification with abort/retry/ignore. These are complete and should
be purged.
ERROR The error process is in error. This usually happens on new sites where the configuration is incomplete. For example, an error process notification was sent to non-existent person. Reviewing the error stack should tell you what the problem is.
NOTIFIED A workflow exception has been raised and the wferror process started. There will be an unactioned notification somewhere on the system.


select item_type,count(*)
from wf_item_attribute_values
group by item_type

ITEM_TYP COUNT(*)
-------- ----------
APEXP 4490
AZNF003 5
AZNF004 5
AZNF005 5
CREATEPO 360
ECO_APP 35
If there are a lot of rows to be purged, you will risk running of out of rollback. One way of reducing the amount of data is to use this second query to purge by ITEM_TYPE. If you still experience problems, then you can restrict the amount of data further using a date range. Alternatively apply patch 1384537


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

Dealing With Workflow Errors
In order to address the errors, go in through the Workflow Web agent as SYSADMIN, view the waiting notifications on these errors and review their content. They will contain why the WFERROR process was fired off. You can take the appropriate action from those notifications. Abort, retry, ignore.

Working with large numbers of Errors

If you have a huge number of errors then you could use the SQL queries above to speed up your analysis rather than reviewing each record. Consider using the wfretry.sql script to restart the processes from SQL*Plus. Note that it is not a good idea to go through and abort all of these error'd processes as they could be active legitimate workflows that are pending within the application. Only if you are happy that all of the error'd processes can be deleted then you could consider using the wf_engine.abortProcessAPI. Again, use extreme caution when manipulating data this way.



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

Scripts to create Histograms and gather statistics
Bug 1659212 discusses checking a number of workflow histograms and if necessary running the scripts below to create them and populate the corresponding statistics.

Check/create a histogram for ACTIVITY_STATUS in WF_ITEM_ACTIVITY_STATUSES
Check/create a histogram for MAIL_STATUS and STATUS in WF_NOTIFICATIONS
REM histogram.sql
REM Create histogram for ACTIVITY_STATUS in WF_ITEM_ACTIVITY_STATUSES
REM for bug 1417941
REM
prompt 'Enter FND schema name e.g. APPLSYS'
def fnd_schema = '&&1'
.
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
.
begin
FND_STATS.LOAD_HISTOGRAM_COLS(
action=>'INSERT'
,appl_id=>0
,tabname=>'WF_ITEM_ACTIVITY_STATUSES'
,colname=>'ACTIVITY_STATUS'
);
.
FND_STATS.GATHER_TABLE_STATS(
ownname=>'&fnd_schema'
,tabname=>'WF_ITEM_ACTIVITY_STATUSES'
);
.
end;
/
.
commit;
REM hist_notif.sql
REM Create histogram for MAIL_STATUS and STATUS in WF_NOTIFICATIONS
REM for bug 1563380
prompt 'Enter FND schema name e.g. APPLSYS'
def fnd_schema = '&&1'
.
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
.
begin
FND_STATS.LOAD_HISTOGRAM_COLS(
action=>'INSERT'
,appl_id=>0
,tabname=>'WF_NOTIFICATIONS'
,colname=>'MAIL_STATUS'
);
.
FND_STATS.LOAD_HISTOGRAM_COLS(
action=>'INSERT'
,appl_id=>0
,tabname=>'WF_NOTIFICATIONS'
,colname=>'STATUS'
);
.
FND_STATS.GATHER_TABLE_STATS(
ownname=>'&fnd_schema'
,tabname=>'WF_NOTIFICATIONS'
);
.
end;
/
.
commit;