Oracle Workflow Performance Concepts

This appendix describes concepts and techniques that you can use to enhance performance when running Oracle Workflow.

This appendix covers the following topics:

• 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. Additionally, you can enhance Business Event System performance by adjusting its maximum cache size and by enabling static function calls for custom PL/SQL functions.

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.

• Synchronous - A synchronous process contains only activities that can be executed immediately, so that the Workflow Engine executes the process without interruption from start to finish. The Workflow Engine does not return control to the calling application until it completes the process. With a synchronous process, you can immediately check for process results that were written to item attributes or directly to the database. However, the user must wait for the process to complete. If the process takes a long time, the application may appear to hang. In this case, you should change the process to an asynchronous process.

• Asynchronous - An asynchronous process is a process that the Workflow Engine cannot complete immediately because it contains activities that interrupt the flow. Examples of activities that force an asynchronous process include deferred activities, notifications with responses, blocking activities, and wait activities. Rather than waiting indefinitely when it encounters one of these activities, the Workflow Engine sets the audit tables appropriately and returns control to the calling application. The workflow process is left in an unfinished state until it is started again, usually by the Notification System, Business Event System, or the background engine. With an asynchronous process, the user does not have to wait for the process to complete to continue using the application. However, the results of the process are not available until the process is completed at a later time.

• Forced synchronous - A forced synchronous process completes in a single SQL session from start to finish and never inserts into or updates any database tables. As a result, the execution speed of a forced synchronous process is significantly faster than a typical synchronous process. The process results are available immediately upon completion. However, no audit trail is recorded. You may want to use a forced synchronous process if your application needs to generate a specific result quickly and recording an audit trail is not a concern. To create a forced synchronous process, you must set the item key of your process to #SYNCH and follow certain restrictions in designing your process, such as not including any notification activities.

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 by default 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.

You can optionally enhance performance for a process by specifying that the Workflow Engine should create runtime copies of item attributes only on demand. To do so, define a special activity attribute named #ONDEMANDATTR for the top-level runnable process activity. In this case, the Workflow Engine creates runtime copies of item attributes only when the process sets values for those item attributes. Otherwise, the Workflow Engine simply references the default values specified for the item attributes in the design-time workflow definition. See: #ONDEMANDATTR Attribute, Oracle Workflow Developer's Guide.

Item attributes should be used for:

• Storing working information for the work item.

• Token replacement for messages. For messages where the number of lines may vary, such as in repeating groups, do not create individual item attributes for each line. Instead, use item and message attributes of type Document to combine the lines together.

• Storing primary key values so that functions can look up all necessary values from the database.

• Temporary placeholders to set activity attributes dynamically. For example, the performer of a notification may only be known at runtime, so you can reference an item attribute and seed the desired value just before executing the notification.

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:

• Document - The attribute value is an embedded or attached document, which enables a complex structure to be rendered inline, or attached to notifications. You can specify the following types of documents:

  • PL/SQL document - A document representing data from the database as a character string, generated from a PL/SQL procedure.

  • PL/SQL CLOB document - A document representing data from the database as a character large object (CLOB), generated from a PL/SQL procedure.

  • PL/SQL BLOB document - A document representing data from the database as a binary large object (BLOB), generated from a PL/SQL procedure.

  • Oracle Application Framework region - A JSP call to an Oracle Application Framework region for inclusion in a notification message.

• Role - The attribute value is the internal name of a role. If a message attribute of type role is included in a notification message, the attribute automatically resolves to the role's display name, eliminating the need for you to maintain separate attributes for the role's internal and display names. Also, when you view the notification from a Web browser, the role display name is a hypertext link to the e-mail address for that role.

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

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

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

• 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. The script is called wfupartb.sql and is located in the admin/sql subdirectory under $FND_TOP. See: Partitioning Workflow Tables.

Purging for Performance

You can use the Workflow purge APIs to purge obsolete runtime information for completed work items, obsolete runtime information not associated with work items, and obsolete design information. 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.

Related Topics

WF_PURGE, Oracle Workflow API Reference

Persistence Type, Oracle Workflow Developer's Guide

Managing Business Event System Performance

The Business Event System caches event, subscription, and agent definitions to enhance performance during subscription processing. The default maximum size of the cache is 50 records. You can optionally increase the maximum cache size to reduce the database queries performed by the Business Event System, or decrease the maximum cache size to reduce the amount of memory used by the cache. See: Changing the Maximum Cache Size for the Business Event System.

If you use custom PL/SQL functions within the Business Event System, including event data generate functions, event subscription rule functions, and queue handler enqueue and dequeue APIs, Oracle Workflow calls those functions using dynamic SQL by default. However, you can enable Oracle Workflow to call your custom functions statically to enhance performance. See: Enabling Static Function Calls for Custom PL/SQL Functions.