Skip Headers

Oracle Workflow Administrator's Guide
Release 2.6.4
Part Number B15852-02
Go to Table of Contents
Contents
Go to previous page
Previous
Go to next page
Next

Oracle Workflow Performance Concepts

Oracle Workflow Performance Concepts

The performance of Oracle Workflow depends on several different factors. You can enhance performance in your workflow process design through effective use of synchronous, asynchronous, and forced synchronous processes, item attributes, message attributes, subprocesses, and deferred activities. You can also use partitioning and purging techniques to address performance issues associated with large quantities of runtime data.

For more information, see: The Oracle Applications Tuning Handbook by Andy Tremayne and Steve Mayze (Oracle Press, ISBN 0-07-212549-7) and Oracle Database Concepts.

Designing Workflow Processes for Performance

You can enhance the performance of your workflow processes through effective process design.

Synchronous, Asynchronous, and Forced Synchronous Workflows

When designing a workflow process, you must decide whether you want it to be executed as a synchronous, asynchronous, or forced synchronous process. The process design impacts the amount of time it takes for the Workflow Engine to return control to the calling application that initiates the process.

See: Overview of the Workflow Engine, Oracle Workflow API Reference and Synchronous, Asynchronous, and Forced Synchronous Processes, Oracle Workflow API Reference.

Item Attributes

Item attributes act as global variables that can be referenced or updated by any activity within a workflow process. The number of item attributes directly affects the startup time for work items, because the Workflow Engine creates runtime copies of all item attributes when a new work item is created. For this reason, item attributes should be kept to a minimum.

Item attributes should be used for:

Item attributes should reference static values or values that are not in the database so that there are no concerns about keeping the values synchronized. (Primary key values, however, do not change.) Do not implement every column within a table as an item attribute.

See: Item Type Attributes, Oracle Workflow Developer's Guide and To Define an Item Type or Activity Attribute, Oracle Workflow Developer's Guide.

Item attribute types that can help you reduce the number of attributes you need include the following:

See: Attribute Types, Oracle Workflow Developer's Guide.

Whenever multiple item attributes will be created, or multiple item attribute values will be set during workflow processing, use the array versions of the Add Item Attribute and Set Item Attribute workflow engine APIs (AddItemAttributeArray and SetItemAttributeArray, respectively). These APIs will significantly decrease the number of calls to Workflow Engine APIs, which can have a measurable impact on performance during batch processing. See: AddItemAttributeArray, Oracle Workflow API Reference and SetItemAttributeArray, Oracle Workflow API Reference.

Message Attributes

To enhance performance, message attributes should be kept to a minimum. For messages where the number of lines may vary, such as in repeating groups, do not create individual item and message attributes for each line (LINE_INFO1, LINE_INFO2, etc.). Instead, use item and message attributes of type Document to combine the lines together.

See: Attribute Types, Oracle Workflow Developer's Guide, Send and Respond Message Attributes, Oracle Workflow Developer's Guide, and To Define a Message Attribute, Oracle Workflow Developer's Guide.

Subprocesses

When you design a workflow process, you can group a collection of activities together in a process activity which represents a subprocess within the main process. Using subprocesses judiciously can help make workflow diagrams clearer and easier to read and can simplify workflow monitoring and maintenance. However, subprocesses also result in additional DML operations and additional state information stored in Workflow tables. Consequently, you should avoid unnecessary use of subprocesses when there is no functional benefit.

For example, the following two processes, Process 1 and Process 2, are functionally identical, both performing a function called Function 1. However, they result in different numbers of state rows being stored in Workflow tables.

Process 1 contains a Start activity, a Subprocess activity, and an End activity. The subprocess contains a Start activity, the Function 1 activity, and an End activity. This process stores 7 state rows in Workflow tables.

Example Process 1

the picture is described in the document text

Process 2 simply contains a Start activity, the Function 1 activity, and an End activity. This process stores only 4 state rows in Workflow tables.

Example Process 2

the picture is described in the document text

Because more rows are stored in Workflow tables, the kind of design shown in the Process 1 diagram will result in slower workflow throughput and a need to purge Workflow runtime tables more frequently than what should be necessary with the Process 2 design.

Note: This guideline is not meant to imply that subprocesses should not be used at all. Collapsing all subprocesses can make workflow diagrams unreadable and difficult to maintain. This recommendation merely highlights that unnecessary overuse of subprocesses can have a negative performance impact.

See: Process Activity, Oracle Workflow Developer's Guide and To Create a Process Activity, Oracle Workflow Developer's Guide.

Deferring Activities

The simplest and most effective way to improve the online user's response time is to defer function activities. You may want to defer activities that require a large amount of processing resource or time to complete. Oracle Workflow lets you manage the load on the Workflow Engine and the response time for the user by setting up supplemental engines to run these costly activities as background tasks. In these cases, the costly activity is deferred by the Workflow Engine and run later by a background engine.

When an activity is deferred, the main Workflow Engine can then continue to the next available activity, which may occur on some other parallel branch of the process. If no other activity is available to be executed, the Workflow Engine returns control immediately to the calling application. The user remains unaware that processing is still taking place, rendering a faster execution time.

To defer an activity, set the activity's cost above the default threshold cost at design time. The threshold cost is a PL/SQL package variable with a default value of 50 hundredths of a second. Set a cost above this threshold for all activities that you don't want the user to wait for.

At runtime, the Workflow Engine defers any thread to the background as soon as it encounters an activity with a cost higher than the threshold. Then the background engine later identifies the process as deferred and continues its execution.

In addition to deferred activities, background engines also handle timed out activitites and stuck processes. You can run as many background engines as you want. You must have at least one background engine that can check for timed out activities, one that can process deferred activities, and one that can handle stuck processes. At a minimum, you need to set up one background engine that can handle both timed out and deferred activities as well as stuck processes.

Generally, you should run a separate background engine to check for stuck processes at less frequent intervals than the background engine that you run for deferred activities, normally not more often than once a day. Run the background engine to check for stuck processes when the load on the system is low.

See: Deferred Processing, Oracle Workflow API Reference, Activity Cost, Oracle Workflow Developer's Guide, Setting Up Background Workflow Engines, and To Set Engine Thresholds.

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 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 subdirectory 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.

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.

Related Topics

WF_PURGE, Oracle Workflow API Reference

Persistence Type, Oracle Workflow Developer's Guide