Workflow purge

FAQ on Purging Oracle Workflow Data (Doc ID 277124.1)

Which Workflow Tables Need To Be Checked?

Master workflow runtime table is WF_ITEMS.
All other runtime table records are associated to record in WF_ITEMS.
If records are purged in WF_ITEMS, records ar purged in all other workflow runtime tables.
Checking the workflow runtime table master table for progress purging is the same as check any other workflow runtime table.

This query will help you to determine volume of wf process classified by Status, item Type, and permanency.

select wi.item_type ITEM_TYPE,
       wit.persistence_type P_TYPE,
       decode (wi.end_date, NULL, 'OPEN', 'CLOSED') Status,
       count(*) COUNT
from wf_items wi,
     wf_item_types wit
where wit.name = wi.item_type
group by item_type,
         wit.persistence_type,
         WIT.PERSISTENCE_DAYS,
         decode (wi.end_date, NULL, 'OPEN', 'CLOSED')
order by decode (wi.end_date, NULL, 'OPEN', 'CLOSED'), 4 desc;

This query displays the parameter arguments in order for a Concurrent Program:

SELECT p.USER_CONCURRENT_PROGRAM_NAME "NAME",
c.CONCURRENT_PROGRAM_NAME "INTERNAL",
f.END_USER_COLUMN_NAME "PARAMETER",
f.ENABLED_FLAG "ON_OFF",
f.DEFAULT_VALUE,
f.REQUIRED_FLAG, f.DESCRIPTION
FROM FND_DESCR_FLEX_COL_USAGE_VL f, FND_CONCURRENT_PROGRAMS_TL p, fnd_concurrent_programs c
WHERE substr(f.DESCRIPTIVE_FLEXFIELD_NAME,7,8)=c.CONCURRENT_PROGRAM_NAME
and c.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID
--and p.USER_CONCURRENT_PROGRAM_NAME LIKE '%Workflow%'
and (f.DESCRIPTIVE_FLEXFIELD_NAME like '$SRS$.FNDWFPR%')
AND p.LANGUAGE = 'US'
order by f.DESCRIPTIVE_FLEXFIELD_NAME, f.COLUMN_SEQ_NUM;

When workflow processes are not closed and purged, these workflow runtime tables could grow exponentially:

WF_ITEM_ATTRIBUTE_VALUES
WF_ITEM_ACTIVITY_STATUSES
WF_NOTIFICATION_ATTRIBUTES

Records in these tables are purged only if the records in the workflow master table (WF_ITEMS) qualifies to be purged.

These queries give the volume of data in the tables.

select count(*) from WF_ITEM_ATTRIBUTE_VALUES;
select count(*) from WF_ITEM_ACTIVITY_STATUSES;
select count(*) from WF_NOTIFICATION_ATTRIBUTES;

The above Workflow tables contain data which is used for Workflow background processing. If these tables grow too large the Workflow performance level becomes slower. You should be purging these tables should be purged on a regular basis.

References
Also, check the Note 165316.1-'bde_wf_data.sql - Query Workflow Runtime Data That Is Eligible For Purging'.  For ATG RUP4 and higher, please reference Note 750497.1.

What Are The APIs Provided For Purging Workflow Data?

WF_PURGE is the database defined package which contain the APIs to purge workflow runtime tables. There are also APIs to purge design data like workflow directory services data.  Workflow design data are  tables where workflow process definitions are stored and are not considered runtime data.

The most commonly used procedures are:
Wf_Purge.Items:

Purge all runtime data associated with completed items, their processes, and
notifications sent by them.
Deletes from the tables: WF_NOTIFICATIONS, WF_ITEM_ACTIVITY_STATUSES,
WF_ITEM_ATTRIBUTE_VALUES AND WF_ITEMS/
Parameters:

itemtype : Item type to delete, or null for all item types
itemkey : Item key to delete, or null for all item keys
enddate : Purges wf processes closed after this date
force : Forces to purge closed wf processes even if it has wf slibing
processes open.
docommit : TRUE does commit, FALSE deleted but does not commit.
Core Workflow Only

Wf_Purge.Activities :

Purges wf process definition versions that are not used and that are obsolete.
Deletes from tables:
WF_ACTIVITY_ATTR_VALUES,
WF_ACTIVITY_TRANSITIONS,
WF_PROCESS_ACTIVITIES,
WF_ACTIVITY_ATTRIBUTES_TL,
WF_ACTIVITY_ATTRIBUTES,
WF_ACTIVITIES_TL and
WF_ACTIVITIES that are associated with the specified item type,
have an END_DATE less than or equal to the specified end date and
are not referenced by an existing item as either a process or activity.

Wf_Purge.Total : Purge both item data and activity data.

Wf_Purge.AdHocDirectory :

Purge users and roles in the WF_LOCAL_* tables
whose expiration date has elapsed and that are not referenced in any notification.

Note:

The WF_PURGE_APIS only purge data associated with Temporary item type whose
persistence, in days, has expired. A persistence type PL/SQL variable is set
to 'TEMP' (Temporary) by default and should not be changed. Use the WF_PURGE.
TotalPERM API to delete all eligible obsolete runtime data associated with
item types of with a persistence type of 'Permanent'.

What Parameters Are Passed To The Purge APIs?

Many of the Purge APIs accept the following parameters:

• Item Type: The item type associated with the obsolete runtime data you want to delete. Leave this parameter null to delete obsolete data for all item types
• Item Key: A string generated from the application object's primary key. The string uniquely identifies the item within an item type. Leave this parameter null to purge all items in the specified item type.
• End Date: A specified date to delete up to.

Which Concurrent Program Is Used For Purging?

Answer
In Oracle E-Business Suite, use the concurrent program 'Purge Obsolete Workflow
Runtime Data' (short name FNDWFPR) which calls database package WF_Purge.Total.

This program deletes old Workflow runtime data for completed Workflows.
It is recommended that this program or package is run regularly as required.

What's The Recommended Frequency For Running The 'Purge Obsolete Workflow Runtime Data' Concurrent Program?

In short, Once Every 12 Hours for ALL item types daily.  For maintaining a healthy workflow environment, the Purge Obsolete Workflow Runtime Data request should be scheduled regularly.  Below is from the Workflow Administrator's Guide which reflects as a guideline, but the volumes differ for each instance:

The Oracle Applications Manager console helps you easily maintain the Oracle Workflow and Oracle XML Gateway database tables. Oracle Workflow and Oracle XML Gateway access several tables that can grow quite large with obsolete workflow information that is stored for all completed workflow processes, as well as obsolete information for XML transactions. The size of these tables and indexes can adversely affect performance. These tables should be purged on a regular basis, using the Purge Obsolete Workflow Runtime Data concurrent program.

What Are The Parameter Values To Define To Run The 'Purge Obsolete Workflow Runtime Data' Concurrent Program?

• Item Type : The item type to purge. Leaving this field blank defaults to purging the runtime data for all item types.
• Item Key : The item key to purge. Leaving is field blank defaults to purging the runtime data for all item keys.
• Age : Minimum age of data to purge, in days.
• Persistence Type : The persistence type to be purged, either for Temporary or for Permanent. The default is temp.
• Core Workflow Only - Enter 'Y' to purge only obsolete runtime data associated
  with work items, or 'N' to purge all obsolete runtime data as well obsolete design
  data. The default is 'N'.
• Commit Frequency - Enter the number of records to purge before the program
  commits data. To reduce rollback size and improve performance, set this parameter
  to commit data after a smaller number of records. The default is 500 records.

Note: After performing a commit, the program resumes purging
work items with the next subsequent begin date. In some cases, if
additional items have the same begin date as the last item that was
purged before a commit, the program may not purge all eligible
items. To purge these remaining work items, simply rerun the
program.

• Signed Notifications - Enter 'N' to preserve signature evidence, including
  notifications that required electronic signatures and their associated signature
  information. Enter 'Y' to purge signature-related information. The default is 'N'.

Workflow Information Center, Diagnostics, & Community

• Please reference the Workflow Product Information Center Document for Top Workflow Resources: Document 1320509.1

• For additional help, please refer to one of the following documents on diagnostics to address current needs. Providing diagnostic output on an issue for support when logging a service request is very helpful.
  
  Note 179661.1 for 11i or Note 421245.1 for Rel 12.x

• Visit the Core Workflow community for help with industry experts or to share knowledge.