PYUGEN: Performance Issues With Payroll
(Doc ID 341686.1)
Last updated on JULY 17, 2024
Applies to:
Oracle Payroll - Version 11.5.10.2 and laterInformation in this document applies to any platform.
Symptoms
The PAYROLL PROCESS has slowed down -
Most expensive query appears to be the following:
SELECT /*+ ORDERED */
fnd_number.number_to_canonical(nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0))
FROM /* Person within Government Reporting Entity */
pay_assignment_actions BAL_ASSACT
, pay_payroll_actions BACT
, per_all_assignments_f ASS
, pay_assignment_actions ASSACT
, pay_payroll_actions PACT
, pay_run_results RR
, pay_run_result_values TARGET
, pay_balance_feeds_f FEED
where BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID
and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
and FEED.balance_type_id = :U1 + decode(TARGET.INPUT_VALUE_ID, null, 0, 0)
and FEED.input_value_id = TARGET.input_value_id
and nvl(TARGET.result_value,'0') != '0'
and TARGET.run_result_id = RR.run_result_id
and RR.assignment_action_id = ASSACT.assignment_action_id
and ASSACT.payroll_action_id = PACT.payroll_action_id
and PACT.effective_date between FEED.effective_start_date
and FEED.effective_end_date
and RR.status in ('P','PA')
and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
and ASSACT.assignment_id = ASS.assignment_id
/* This may break under the cost-based optimizer, since the result depends on
the correct plan! */
and ASS.person_id =
(select person_id
from per_all_assignments_f START_ASS
where START_ASS.assignment_id = BAL_ASSACT.assignment_id
and rownum = 1)
and PACT.effective_date between ASS.effective_start_date
and ASS.effective_end_date
and ASSACT.tax_unit_id = :TAX_UNIT_ID
/* Year To Date */
and PACT.effective_date >=trunc(BACT.effective_date,'Y')Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |