Performance can be impacted by many things. One of the more common causes is database space. When your database starts to fill up with data there will be some impact on performance time. Workflow plays a huge part in many functional modules. As such, the corresponding amount of data and indexes stored will increase the size of these tables and can adversely affect performance in a short period of time.
This practice will help avoid data backlogs and build up that can cause performance issues. The following shows a list of tables that would be impacted by growth and would be selected for purging through the below Oracle purge process:
The “Purge Obsolete Workflow Runtime Data” concurrent request is designed specifically to keep your workflow data clean and at a manageable size - and it should be scheduled regularly.
Below are the recommended purging routines that should be scheduled in Oracle. Before scheduling these requests, you should be aware of your company’s policy on history retention. You need to keep these restrictions in mind when selecting not only what data to purge, but the age of the data as well. For example, if you need to keep the data for Audit purposes for 3 years, of course you do not want to set your purge routine with Age 0.
Here are the Parameter values to define the 'Purge Obsolete Workflow Runtime Data' Concurrent Program:
- Item Type: The item type to purge. Leaving this field blank defaults to purging the runtime data for all item types. Be aware of this regarding your company’s policy for data retention.
- Item Key: The item key to purge. You can select specific data to purge within an item type. Leaving this field blank defaults to purging the runtime data for all item keys.
- Age: Minimum age of data to purge, in days. This is important in terms of data retention as well.
- Persistence Type: The persistence type to be purged, either for Temporary or for Permanent. The default is Temporary.
- Note: When you define an item type, you must also specify its persistence type. The persistence type controls how long a status audit trail is maintained for each instance of the item type. If you set Persistence to Permanent, the runtime status information is maintained indefinitely until you specifically purge the information by calling the procedure WF_PURGE.TotalPerm().
- Core Workflow Only: Enter 'Y' to purge only obsolete runtime data associated with work items, or 'N' to purge all obsolete runtime data as well as obsolete design data. The default is 'N'.
- Commit Frequency: Enter the number of records to purge before the program
commits data. To reduce rollback size and improve performance, set this parameter
to commit data after a smaller number of records. The default is 500 records.
- Note: After performing a commit, the program resumes purging work items with the next subsequent begin date. In some cases, if additional items have the same begin date as the last item that was purged before a commit; the program may not purge all eligible items. To purge these remaining work items, simply rerun the program.
- Signed Notifications: Enter 'N' to preserve signature evidence, including notifications that required electronic signatures and their associated signature information. Enter 'Y' to purge signature-related information. The default is 'N'.
The table below shows some recommended purges that are not application specific and should not impact your data retention:
Deciding to purge the data that is 30 days old allows you the opportunity review details on any specific Workflow that has been completed within 30 days of completion. This may sometimes be necessary when working on a specific issue.
If your business has policies regarding data retention (age of data), then additional requests should be scheduled for select Oracle Applications in the areas which retention is important. Below is an example of the Purge Request with specific Item Type. Here, only the Workflow data for Accounts Payables Invoices, older than one year, would be purged. Anything inside of 365 days would be retained. Remember, this request is ONLY purging the WORKFLOW attached to the data – not the data itself. The data is still accessible unless a data purge is run.
Purge Request with Specific Item Type:
If data retention for workflow is not an issue, then the following parameters would be a good guideline to follow:
By following these above guidelines you will help to maintain a healthy workflow database and avoid adverse performance issues in this area.
Other Related Resources from TriCore:
- Executing Custom Code Using Oracle Workflow Business Event System
- A Day with a DBA
- Webinar: Leveraging Your Oracle Database Investment
- CIOReviews Names TriCore Solutions One of the "20 Most Promising Database Technology Solutions Providers 2015"
- Need additional help? View our Database Services
Additional information on these guidelines can be found in My Oracle Support:
References:NOTE:750497.1 - Workflow Purge Data Collection Script
NOTE:1320509.1 - Information Center: E-Business Suite Oracle Workflow (WF)
NOTE:132254.1 - Speeding Up And Purging Workflow
NOTE:144806.1 - A Detailed Approach To Purging Oracle Workflow Runtime Data
NOTE:165316.1 - bde_wf_data.sql - Query Workflow Runtime Data That Is Eligible For Purging