To partition workflow tables for RAC affinity:If you installed Oracle E-Business Suite in an Oracle Real Application Clusters (Oracle RAC) environment, then you can optionally use workflow RAC affinity to increase scalability and performance for some high volume workflows.
To enable workflow RAC affinity, you must first partition certain workflow runtime tables by RAC instance ID. You must also create a Virtual Private Database (VPD) policy for those tables that allows access only to the records marked with the RAC instance ID that is specified in the current session context. When you complete the partitioning, Oracle Workflow enables a special type of background engine, called the Workflow Background Process for RAC, that you can use to process RAC-enabled workflows. The background engines that you run using the Workflow Background Process for RAC concurrent program each access only one partition, corresponding to one RAC instance ID, within the workflow tables.
When an application launches a RAC-enabled workflow process, the Workflow Engine checks which RAC instance the application is connected to, sets that instance ID into the session context, and marks all runtime records for the workflow process with the same instance ID. As the Workflow Engine continues executing the workflow process, the engine accesses and manipulates only the workflow runtime records marked with that instance ID, until the workflow is completed or deferred. Similarly, when you run the Workflow Background Process for RAC program specifying a particular RAC instance partition, the background engine sets that instance ID into the session context, marks any workflow runtime records that it creates with that instance ID, and continues processing only workflow runtime records marked with that instance ID. Because this type of background engine accesses only one partition within the workflow tables, other background engines run using the Workflow Background Process for RAC can access other partitions at the same time. In this way, workflow RAC affinity helps avoid contention on the workflow tables, provides faster access to workflow runtime data, and increases throughput for the RAC-enabled workflow processes.
To set up workflow RAC affinity, perform the following steps:
To partition workflow tables for RAC affinity:Partitioning addresses key issues in supporting very large tables and indexes by dividing 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.
Run the script called wfracprt.sql to partition certain Oracle Workflow tables that store runtime status data.
The script partitions four Workflow tables and recreates the associated indexes. The tables are partitioned by RAC instance ID and hash subpartitioned by item type and item key. The following table shows the workflow tables and indexes on which the script runs.
Partitioned Tables and Associated Indexes
| Table | Indexes |
|---|---|
| WF_ITEM_ACTIVITY_STATUSES | WF_ITEM_ACTIVITY_STATUSES_PK, WF_ITEM_ACTIVITY_STATUSES_N1, and WF_ITEM_ACTIVITY_STATUSES_N2 |
| WF_ITEM_ACTIVITY_STATUSES_H | WF_ITEM_ACTIVITY_STATUSES_H_N1 and WF_ITEM_ACTIVITY_STATUSES_H_N2 |
| WF_ITEM_ATTRIBUTE_VALUES | WF_ITEM_ATTRIBUTE_VALUES_PK |
| WF_ITEMS | WF_ITEMS_PK, WF_ITEMS_N1, WF_ITEMS_N2, and WF_ITEMS_N3 |
Before running the partitioning script, you should back up these four tables so that you can restore them in case the script fails.
To run the script, you must have sufficient free space on the table and index tablespaces. During the creation of the partitioned tables, the script requires slightly more diskspace than the underlying tables, in the same tablespace where the underlying tables are located. Similarly, sufficient free space is required for the index tablespace.
Additionally, you should allow sufficient time for the script to run. The amount of time needed depends on the amount of data in the tables. When the tables already contain existing data, such as after an upgrade from a previous release, the script requires more time than it does when the tables are empty, such as after a fresh installation of Oracle E-Business Suite. To minimize the time required, plan to run the wfeitrac.sql script as early as possible in your setup process, and perform a purge of obsolete workflow runtime data before you run the script. See: Purge Obsolete Workflow Runtime Data.
First, stop any Workflow Background Process concurrent requests as well as all application tier services.
The wfracprt.sql script is located in the $FND_TOP/patch/115/sql directory. Use the script as follows:
sqlplus <apps_user> @wfracprt <transaction_table_tablespace> <index_tablespace> Enter password: <password>
Replace <apps_user> with the user name for the APPS user. The user name is usually apps. Replace <transaction_table_tablespace> with the name of the tablespace used for transaction tables in your Oracle E-Business Suite database. Replace <index_tablespace> with the tablespace used for indexes. Then enter the password for the APPS user at the prompt.
For example:
sqlplus apps @wfracprt APPS_TS_TX_DATA APPS_TS_TX_IDX Enter password: <password>
After the script finishes, restart the application tier services. However, do not restart your background engines until you have completed the other setup steps for workflow RAC affinity.
The first time that you run the script, it creates a number of partitions equal to the number of active RAC instances in your environment, as indicated by the number of records in the gv$instance view. When the script completes successfully, the Workflow Background Process for RAC concurrent program becomes available for use.
If you add another RAC instance to your environment after you set up workflow RAC affinity, then run the wfracprt.sql script again to add a partition for the new RAC instance within the Oracle Workflow tables.
Note: The script adds new partitions as needed but does not remove existing partitions.
To create a VPD policy for RAC affinity:After you partition the Oracle Workflow runtime tables, you must create a VPD policy for those tables that allows workflow background engines to access and manipulate only the records marked with the RAC instance ID that is specified in the current session context. This VPD policy restricts the engines to access only the partition corresponding to the given RAC instance in each table.
To create the VPD policy, use the wfracvpd.sql script.
The wfracvpd.sql script is located in the $FND_TOP/patch/115/sql directory. Use the script as follows:
sqlplus <apps_user> @wfracvpd Enter password: <password>
Replace <apps_user> with the user name for the APPS user. The user name is usually apps. Then enter the password for the APPS user at the prompt.
For example:
sqlplus apps @wfracvpd Enter password: <password>
To configure workflow processes for RAC affinity:Identify the workflow processes that you want to execute using RAC affinity. High volume batch processing workflows are usually good candidates to consider, because they can often gain a significant performance increase by using this feature. To be eligible for RAC affinity, a workflow must either be launched and completed in the same session, or the workflow must be completed using only a background engine, without requiring any other intervention. Consequently, the following restrictions apply:
The workflow must not contain any response-required notifications.
The workflow must not be progressed or modified by any other workflow processes, including master/detail processes.
The workflow must not contain any event activities that are loosely coupled, meaning a response to the event or event subscription processing will later progress or modify the workflow. Consequently, any workflow that contains a Receive event activity is ineligible for RAC affinity. A workflow that contains a Raise event activity or a Send event activity is eligible only if the workflow will not be affected by any subsequent response to the event or event subscription action. Review such events carefully to ensure that the workflow can be properly handled by RAC affinity.
To enable RAC affinity for a workflow process, first define a lookup code corresponding to that workflow process in the "Item types using RAC affinity" lookup type. Then run the script called wfeitrac.sql to consolidate the records for any existing active instances of that workflow process into a single partition within each of the Oracle Workflow runtime tables.
Navigate to the Lookup Types page in the Functional Administrator responsibility.
Search for the "Item types using RAC affinity" lookup type with the code WF_RAC_ENABLED_TYPES in the Application Object Library application.
Define a new lookup code for this lookup type that corresponds to your workflow process. Specify the lookup code in the following format:
<ITEM_TYPE>:<PROCESS_NAME>
where <ITEM_TYPE> is the internal name of the item type to which the workflow process belongs, followed by a colon, and <PROCESS_NAME> is the internal name of the process. For example, if you choose to enable RAC affinity for the STANDARD_LINE process within the OEOL item type, define the lookup code as follows:
OEOL:STANDARD_LINE
Ensure that you enter the internal names of the item type and process in the Code field exactly as the names are defined in your database. See: Application Utilities Lookups and Application Object Library Lookups.
Run the wfeitrac.sql script to consolidate the records for any existing active instances of your workflow process into a single partition within each of the Oracle Workflow runtime tables.
You should allow sufficient time for the script to run. The amount of time needed depends on the amount of data in the tables. To minimize the time required, perform a purge of obsolete workflow runtime data before you run the wfeitrac.sql script. See: Purge Obsolete Workflow Runtime Data.
The wfeitrac.sql script is located in the $FND_TOP/patch/115/sql directory. Use the script as follows:
sqlplus <apps_user> @wfeitrac <item_type> <proc_name> <part_no> <proc_no> Enter password: <password>
Replace <apps_user> with the user name for the APPS user. The user name is usually apps. Replace <item_type> with the internal name of the item type to which the workflow process belongs. Replace <proc_name> with the internal name of the process. Replace <part_no> with the number for the partition in which you want to consolidate the runtime data for this workflow process. Replace <proc_no> with the number of parallel workers that you want to perform this database operation. Then enter the password for the APPS user at the prompt.
Note: You can also disable RAC affinity for a previously RAC-enabled workflow. To do so, remove the corresponding lookup code from the WF_RAC_ENABLED_TYPES lookup type.
To run background engines with workflow RAC affinity:After you configure your workflow processes for RAC affinity, set up workflow background engines to run for both RAC-enabled and non-RAC workflows.
Run the Workflow Background Process for RAC concurrent program to handle RAC-enabled workflow processes.
Run the Workflow Background Process concurrent program to handle non-RAC workflow processes.
See: Setting Up Background Workflow Engines.
Note: If you disable RAC affinity for a previously RAC-enabled workflow, you should stop the Workflow Background Process for RAC request for that item type and run the Workflow Background Process for that item type instead.