Workflow Related Scheduled Concurrent Program for DBAs

Purge Obsolete Workflow Runtime Data

Oracle Workflow accesses several tables that can grow quite large with obsolete workflow information that is stored for all completed workflow processes. The size of these tables and indexes can adversely affect performance. You should purge these tables on a regular basis using FNDWFPR.
This program calls database package WF_Purge.Total.  This program deletes old Workflow runtime data for completed Workflows. It is recommended that this program or package is run regularly as required.

From Workflow Administrator's Guide PDF Document:
The Oracle Applications Manager console helps you easily maintain the Oracle Workflow and Oracle XML Gateway database tables. Oracle Workflow and Oracle XML Gateway access several tables that can grow quite large with obsolete workflow information that is stored for all completed workflow processes, as well as obsolete information for XML transactions. The size of these tables and indexes can adversely affect performance. These tables should be purged on a regular basis, using the Purge Obsolete Workflow Runtime Data concurrent program.

Parameter Explanation:
Item Type: The item type to purge. Leaving this field blank defaults to purging the runtime data for all item types.
Item Key: The item key to purge. Leaving is field blank defaults to purging the runtime data for all item keys.
Age: Minimum age of data to purge, in days.
Persistence Type: The persistence type to be purged, either for Temporary or for Permanent. The default is temp.
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 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.
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'.
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.

Workflow Control Queue Cleanup

Parameters: NONE
Schedule:
Frequency:    Run every 12 Hours
Responsibility:  System Administrator
Please tick the checkbox “From the start of the prior run”

Workflow Background Process

This program needs to be submitted in various permutation and combination.

Type-1: which Runs Every 3 Mins for Parameters:
Process Deferred: Yes
Process Timeout:  No
Process Stuck:  No

Type-2: which Runs Every 30 Mins for Parameters:
Process Deferred:  No
Process Timeout:  Yes
Process Stuck:  No

Type-3: which Runs Every 1 Hours for Parameters:
Process Deferred:  No
"Process Timeout:  No
Process Stuck:  Yes"

Type-4: which Runs Every 2 Hours for Parameters:
Process Deferred:  Yes
"Process Timeout:  Yes
Process Stuck:  Yes"

How to Submit a Workflow Background Process Engine [ID 182936.1], Follow the Note mentioned at the End of this Metalink Note

Workflow Mailer Statistics Concurrent Program

Parameters: NONE
Schedule:
Frequency:    Run every 1 Day
Responsibility:  System Administrator
Please tick the checkbox “Increment Date Parameters Each Run”
Please tick the checkbox “From the Completion of the Prior Run”

Workflow Work Items Statistics Concurrent Program

Parameters: NONE
Schedule:
Frequency:    Run every 1 Day
Responsibility:  System Administrator
Please tick the checkbox “Increment Date Parameters Each Run”
Please tick the checkbox “From the Completion of the Prior Run”

Workflow Agent Activity Statistics Concurrent Program

Parameters: NONE
Schedule:
Frequency:    Run every 1 Day
Responsibility:  System Administrator
Please tick the checkbox “Increment Date Parameters Each Run”
Please tick the checkbox “From the Completion of the Prior Run”

Gather Schema Statistics Schedule:

Make sure Gather Shema Stats is running on periodical basis, for XXXXX we have scheduled to run every 15 Days.
Following points needs to be noted for the Gather Schema Statistics:
●● Do not gather statistics excessively on entire schemas or the entire database such as nightly or weekly.
●● Do not gather statistics on permanent objects during peak intervals.
●● Gathering statistics invalidates cursors .Unless you use the ‘No Invalidate’ option.
●● Gathering statistics requires dictionary and object level locks.
●● The option 'GATHER_AUTO' can be used, to gather statistics only on objects that have changes above the specified 'Modification Threshold' (percentage of DML compared to the number of rows of the table).
●● Plans are not likely to change if the data distribution has not changed.
●● Use only FND_STATS or the Gather Schema and Gather Table Statistics Concurrent Programs.
●● Do NOT USE the analyze or dbms_stats command directly. It is not supported, and results in sub-optimal plans.

Following CONCSUB Command can be used to schedule the Gather Schema Statistics concurrent Program:
"CONCSUB apps/${xx_appspassword} SYSADMIN 'System Administrator' SYSADMIN CONCURRENT FND FNDGSCST START='""01-MAR-2012 02:00:00""'
REPEAT_DAYS=10 'ALL','10','18','NOBACKUP','','LASTRUN','GATHER','','Y'"

To Submit the Program Manually from SQLPlus following commands can be used:
For One Schema:
SQL> exec fnd_stats.gather_schema_statistics('MRP');
For All Schema:
SQL> exec fnd_stats.gather_schema_statistics('ALL');
For One Table:
SQL> exec fnd_stats.gather_table_stats('MRP','MRP_FORECAST_DATES');

To Submit the Gather Schema Statistics from EBS Front End:
●● Make sure you are logged in as SYSADMIN User
●● Use the System Administrator Responsibility to Submit the Concurrent Program.