Payroll Exception Report Performance Degradation
(Doc ID 2432085.1)
Last updated on SEPTEMBER 06, 2019
Applies to:
Oracle HRMS (UK) - Version 12.1 HRMS RUP10 and laterInformation in this document applies to any platform.
Symptoms
Payroll Exception Report that has been run several times a month for at least 2 years, has suddenly begun to take much longer to complete, than it did previously. It used to complete in 2 minutes, and now consistently completes in 40 minutes.
SELECT /*+ LEADING(ppa_gen ppa_run ptp asg act_run) */
MAX(ACT_RUN.ASSIGNMENT_ACTION_ID), ASG.ASSIGNMENT_ID
FROM
PER_ASSIGNMENTS_F ASG, PAY_PAYROLL_ACTIONS PPA_RUN, PAY_ASSIGNMENT_ACTIONS
ACT_RUN, PAY_PAYROLL_ACTIONS PPA_GEN, PER_TIME_PERIODS PTP WHERE
PPA_GEN.PAYROLL_ACTION_ID = :B7 AND PTP.PAYROLL_ID = :B5 AND
(PPA_RUN.EFFECTIVE_DATE BETWEEN PTP.START_DATE AND PTP.END_DATE OR
PPA_RUN.EFFECTIVE_DATE = PTP.REGULAR_PAYMENT_DATE ) AND PTP.END_DATE
BETWEEN PPA_GEN.START_DATE AND PPA_GEN.EFFECTIVE_DATE AND
PPA_RUN.ACTION_TYPE IN ('R','Q','V') AND PPA_RUN.ACTION_STATUS = 'C' AND
PPA_RUN.CONSOLIDATION_SET_ID = :B6 AND PPA_RUN.PAYROLL_ID = :B5 AND
PPA_RUN.PAYROLL_ACTION_ID = ACT_RUN.PAYROLL_ACTION_ID AND
ACT_RUN.ACTION_STATUS = 'C' AND ASG.ASSIGNMENT_ID = ACT_RUN.ASSIGNMENT_ID
AND PPA_RUN.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE AND
ASG.EFFECTIVE_END_DATE AND ASG.BUSINESS_GROUP_ID =
PPA_GEN.BUSINESS_GROUP_ID AND ( ASG.SOFT_CODING_KEYFLEX_ID IN (SELECT
HSCK.SOFT_CODING_KEYFLEX_ID FROM HR_SOFT_CODING_KEYFLEX HSCK WHERE
HSCK.SEGMENT1 = TO_CHAR(:B4 ) OR HSCK.SEGMENT11 = TO_CHAR(:B4 ) OR
HSCK.SEGMENT12 = TO_CHAR(:B4 ) ) OR :B4 IS NULL) AND (:B3 IS NULL OR :B3 IN
(SELECT JURISDICTION_CODE FROM PAY_US_EMP_STATE_TAX_RULES_F PUEST WHERE
PUEST.ASSIGNMENT_ID=ASG.ASSIGNMENT_ID AND PPA_RUN.EFFECTIVE_DATE BETWEEN
PUEST.EFFECTIVE_START_DATE AND PUEST.EFFECTIVE_END_DATE) ) AND
ASG.PERSON_ID BETWEEN :B2 AND :B1 GROUP BY ASG.ASSIGNMENT_ID
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 541 0.39 0.42 0 0 0 0
Fetch 5787 20008.16 20039.65 33913446 48069471 0 5246
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6329 20008.55 20040.08 33913446 48069471 0 5246
MAX(ACT_RUN.ASSIGNMENT_ACTION_ID), ASG.ASSIGNMENT_ID
FROM
PER_ASSIGNMENTS_F ASG, PAY_PAYROLL_ACTIONS PPA_RUN, PAY_ASSIGNMENT_ACTIONS
ACT_RUN, PAY_PAYROLL_ACTIONS PPA_GEN, PER_TIME_PERIODS PTP WHERE
PPA_GEN.PAYROLL_ACTION_ID = :B7 AND PTP.PAYROLL_ID = :B5 AND
(PPA_RUN.EFFECTIVE_DATE BETWEEN PTP.START_DATE AND PTP.END_DATE OR
PPA_RUN.EFFECTIVE_DATE = PTP.REGULAR_PAYMENT_DATE ) AND PTP.END_DATE
BETWEEN PPA_GEN.START_DATE AND PPA_GEN.EFFECTIVE_DATE AND
PPA_RUN.ACTION_TYPE IN ('R','Q','V') AND PPA_RUN.ACTION_STATUS = 'C' AND
PPA_RUN.CONSOLIDATION_SET_ID = :B6 AND PPA_RUN.PAYROLL_ID = :B5 AND
PPA_RUN.PAYROLL_ACTION_ID = ACT_RUN.PAYROLL_ACTION_ID AND
ACT_RUN.ACTION_STATUS = 'C' AND ASG.ASSIGNMENT_ID = ACT_RUN.ASSIGNMENT_ID
AND PPA_RUN.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE AND
ASG.EFFECTIVE_END_DATE AND ASG.BUSINESS_GROUP_ID =
PPA_GEN.BUSINESS_GROUP_ID AND ( ASG.SOFT_CODING_KEYFLEX_ID IN (SELECT
HSCK.SOFT_CODING_KEYFLEX_ID FROM HR_SOFT_CODING_KEYFLEX HSCK WHERE
HSCK.SEGMENT1 = TO_CHAR(:B4 ) OR HSCK.SEGMENT11 = TO_CHAR(:B4 ) OR
HSCK.SEGMENT12 = TO_CHAR(:B4 ) ) OR :B4 IS NULL) AND (:B3 IS NULL OR :B3 IN
(SELECT JURISDICTION_CODE FROM PAY_US_EMP_STATE_TAX_RULES_F PUEST WHERE
PUEST.ASSIGNMENT_ID=ASG.ASSIGNMENT_ID AND PPA_RUN.EFFECTIVE_DATE BETWEEN
PUEST.EFFECTIVE_START_DATE AND PUEST.EFFECTIVE_END_DATE) ) AND
ASG.PERSON_ID BETWEEN :B2 AND :B1 GROUP BY ASG.ASSIGNMENT_ID
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 541 0.39 0.42 0 0 0 0
Fetch 5787 20008.16 20039.65 33913446 48069471 0 5246
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6329 20008.55 20040.08 33913446 48069471 0 5246
Steps to Reproduce:
The issue can be reproduced at will with the following steps:
1. Submit the Payroll Exception Report
2. Observe long wait time
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 |
| Cause |
| Solution |
| References |