My Oracle Support Banner

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 later
Information 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


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


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.