Managing Runtime Data for Performance (Oracle Workflow Administrator's Guide)

Oracle Workflow Administrator's Guide
Release 2.6.3
Part Number B10283-02


	Previous	Next		Contents	Index	Glossary

Managing Runtime Data for Performance

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

Purging

Partitioning for Performance

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.

Purging for Performance

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.

If you set an item type's Persistence to Permanent, the runtime status information is maintained indefinitely until you specifically purge the information by calling the procedure WF_PURGE.TotalPerm( ).

If you set an item type's Persistence to Temporary, you must also specify the number of days of persistence ('n'). The status audit trail for each instance of a Temporary item type is maintained for at least 'n' days of persistence after its completion date. After the 'n' days of persistence, you can then use any of the WF_PURGE APIs to purge the item type's runtime status information. See: WF_PURGE, Oracle Workflow API Reference.

If you set an item type's Persistence to Synchronous, Oracle Workflow expects instances of that item type to be run as forced synchronous processes with an item key of #SYNCH. Forced synchronous processes complete in a single SQL session from start to finish and never insert into or update any database tables. Since no runtime status information is maintained, you do not normally need to perform any purging for a process with the Synchronous persistence type. However, if you run the process with a unique item key in asynchronous mode for testing or debugging purposes, Oracle Workflow does maintain runtime status information for that process instance. You can purge this information by changing the item type's Persistence to Temporary and running any of the WF_PURGE APIs. Then change the item type's Persistence back to Synchronous. See: Synchronous, Asynchronous, and Forced Synchronous Processes, Oracle Workflow API Reference.

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.

Managing Runtime Data for Performance

Partitioning for Performance

Purging for Performance

See Also