|Oracle Workflow Administrator's Guide|
Part Number B10283-02
When the Workflow Engine executes any type of workflow other than forced synchronous processes, status information is stored in runtime tables. The amount of data stored in these tables will grow depending on the complexity and number of workflows being executed.
Performance issues associated with large quantities of runtime data can be addressed by:
Partitioning addresses key issues in supporting very large tables and indexes by letting you decompose them into smaller and more manageable pieces called partitions. SQL queries and DML statements do not need to be modified in order to access partitioned tables. However, once partitions are defined, DDL statements can access and manipulate individual partitions rather than entire tables or indexes. In this way, partitioning can simplify the manageability of large database objects. Also, partitioning is entirely transparent to applications.
You can optionally run a script to partition certain Workflow tables that store runtime status data. This step is highly recommended for performance gain. Before running the script, you should ensure that you have backed up the tables that will be partitioned and that you have allowed sufficient free space and time for the script to run. For the version of Oracle Workflow embedded in Oracle Applications, the script is called wfupartb.sql and is located in the admin/sql subdiretory under $FND_TOP. For the standalone version of Oracle Workflow, the script is called wfupart.sql and is located in the wf/admin/sql subdirectory in your Oracle Home. See: Partitioning Workflow Tables.
You can use the Workflow purge APIs to purge obsolete runtime data for completed items and processes, and to purge information for obsolete activity versions that are no longer in use. You may want to periodically purge this obsolete data from your system to increase performance. The Workflow purge APIs are defined in the PL/SQL package called WF_PURGE.
The availability of runtime data for purging depends on the persistence type of the item type. The persistence type controls how long a status audit trail is maintained for each instance of the item type.
Additionally, the administration script wfrmtype.sql is provided to delete runtime data for a particular item type. This script prompts you for an item type to purge from a list of valid item types, then asks you to choose between deleting all runtime data associated with the specified item type or deleting only runtime data for the completed activities and items of the specified item type. See: Wfrmtype.sql.
WF_PURGE, Oracle Workflow API Reference
Persistence Type, Oracle Workflow Developer's Guide