Oracle Payroll Performance Improvement

Oracle Diagnostics used to assist with Performance Issue

• HRMS Technical Diagnostics – Oracle Doc ID 360833.1
• Run Balance Diagnostics (if issue is associated with a Payroll report or process)
• BDE_CHK_CBO script output - Oracle Doc ID 174605.1

HRMS Technical Diagnostics

• Review patching levels to ensure that the latest mandatory patching has been applied.Utilize Oracle E-Business Suite HCM Information Center - Consolidated HRMS Mandatory Patch List Oracle Doc ID 1160507.1 to assist in confirmation.
• Review the table PAY_ACTION_PARAMETERS, specifically those associated with performance:

o  'THREADS'  - this parameter should be set to 1.5 to 2 times the number of processors on machine. Oracle Doc ID 359354.1 (How to Determine the Best Setting for the THREADS Parameter in the PAY_ACTION_PARAMETERS table)

o  'CHUNK SIZE' - Size of the chunks (commit units).  This defaults to 20.  Increasing this amount can assist with performance, in that the system is not performing commits as often.  Acceptable values (1-16000)

o   'TRACE' - make sure that this parameter is either not on the table or set to the value of N (unless you have intentionally set this parameter to trace the process)

• The following parameters default to 20, although you can consider setting that at 100 to assist with performance

o   BAL BUFFER SIZE - Size of the Balance Buffer in the Payroll Run

o   EE_BUFFER_SIZE - Element entry buffer size for payroll run

o   RR BUFFER SIZE - Run result buffer size for the payroll run

o   RRV BUFFER SIZE - Run value buffer size for the payroll run

• Review Table settings / Last Analyzed date to ensure that the ‘Hot’ Payroll tables have been analyzed within the last month.  Tables should be analyzed with the Payroll cycle (i.e. If the Payroll runs monthly, then analyze the tables monthly) as there are a large amount of inserts which occur to these tables during the payroll process.

o   If necessary, Gather Schema Statistics should be executed at 20%

• If US Legislation:  Check the table size of the PAY_US_RPT_TOTALS table.  This table temporarily holds report data while the process is executing and then removes it unless an issue occurs.  If there is a large amount of data on this table it can cause report performance issues.  To assist in cleaning up this table, see: How to remove obsolete data on PAY_US_RPT_TOTALS table.  Oracle Doc ID 256143.1
• Review Max_dump_file_size – the value of this will need to be set to ‘UNLIMITED’ if it is determined that a performance trace needs to be executed to ensure that the trace output file is not truncated.

Run Balance Diagnostics -these should be reviewed if the performance issue is associated with any Payroll process or report

• Verify that all Run Balances are in a ‘Valid’ status. If Run Balances are INVALID, then the Generate Run Balances process will need to be executed to clean up balances.
• Verify that all Run Balances contain a load date of 01/01/XXXX - where XXXX is less than or equal to the year that the process is retrieving data for. If Load dates need to be address, utilize the Adjust Run Balance Process.

For more information, see Oracle Doc ID 344914.1  Useful Information about the Payroll Run Balance

BDE_CHK_CBO Health check – Oracle Doc ID 174605.1

• Verify the Init.ora settings, based on the notes identified in the Health check document associated with the database the issue is seen on.
• Verify the database compatibility settings, this should be set to the same version of the database that the performance issue is seen on.

If the above steps does not resolve the issue, you can raise a ticket with Oracle Support Team and investigate further. You can also refer the Oracle Doc ID 419075.1  for more details on payroll performance improvement steps.