State Periodic Wage Listing SPWL Performance is Slow (Doc ID 2115507.1)

Last updated on MARCH 10, 2016

Applies to:

Oracle HRMS (US) - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

The State Periodic Wage Listing SPWL performance is slow.

The Tkprof trace indicates the most expensive query below:

SELECT DECODE(NVL(SUM(MONTH1_COUNT),0),0,0,1), DECODE(NVL(SUM(MONTH2_COUNT),0)
  ,0,0,1), DECODE(NVL(SUM(MONTH3_COUNT),0),0,0,1)
FROM
 ( SELECT CASE WHEN :B3 BETWEEN START_DATE AND END_DATE THEN 1 ELSE 0 END AS
  MONTH1_COUNT, CASE WHEN :B5 BETWEEN START_DATE AND END_DATE THEN 1 ELSE 0
  END AS MONTH2_COUNT, CASE WHEN :B4 BETWEEN START_DATE AND END_DATE THEN 1
  ELSE 0 END AS MONTH3_COUNT FROM ( SELECT PTP.START_DATE START_DATE ,
  PTP.END_DATE END_DATE FROM PER_TIME_PERIODS PTP ,PER_ALL_ASSIGNMENTS_F PAF ,
  PER_ALL_ASSIGNMENTS_F PAF1 ,PAY_ASSIGNMENT_ACTIONS PAA ,PAY_PAYROLL_ACTIONS
  PPA WHERE PTP.START_DATE <= :B4 AND PTP.END_DATE >= :B3 AND
  PPA.TIME_PERIOD_ID = PTP.TIME_PERIOD_ID AND PPA.PAYROLL_ACTION_ID =
  PAA.PAYROLL_ACTION_ID AND PAF1.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND
  PAF.PERSON_ID = PAF1.PERSON_ID AND PPA.ACTION_TYPE IN ('R','Q') AND
  PAF.ASSIGNMENT_ID = :B2 AND PAA.RUN_TYPE_ID IS NULL AND PAA.TAX_UNIT_ID =
  :B1 AND PAA.ACTION_STATUS = 'C' GROUP BY PTP.START_DATE,PTP.END_DATE ) )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    192      0.02       0.02          0          0          0           0
Fetch      192  11271.25   11320.58        106   12155898       1152         192
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      385  11271.28   11320.60        106   12155898       1152         192


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms