ETL9.2: TL_TA000900.DD000.Step020 Performance Issue (Doc ID 1927003.1)

Last updated on JULY 13, 2016

Applies to:

PeopleSoft Enterprise HCM Time and Labor - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

We installed Bug number 18182952 - TL - EXCESSIVE SQL PARSING TIME OBSERVED DURING MULTI-USER APPLY RULES TESTING to try to resolve some of the time admin performance issues we were having in our 9.2 test environment and found that it installed a bunch of other patches. One of these patches updated TL_TA000900.DD000.Step020 with a fix.
The fix added the AND EXISTS for TL_IPT1 to the bottom of the SQL.
This fix makes the performance in our environment for this step terrible, this step alone ran for over 17 hours before we cancelled it. Can someone see if this fix should even go into 9.2?

UPDATE PS_TL_COMP_DAY_BAL
  SET END_BAL=0 ,TOTAL_PAID=0, TAKEN_TOTAL =0
 WHERE ((DUR >=(
 SELECT MIN(START_DT)
  FROM %Table(TL_TA_BATCH) I
 WHERE I.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
  AND I.EMPLID=PS_TL_COMP_DAY_BAL.EMPLID
  AND I.EMPL_RCD=PS_TL_COMP_DAY_BAL.EMPL_RCD)
  AND ACTION IN ('E','T'))
  OR (DATE_DUE >=(
 SELECT MIN(START_DT)
  FROM %Table(TL_TA_BATCH) I
 WHERE I.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
  AND I.EMPLID=PS_TL_COMP_DAY_BAL.EMPLID
  AND I.EMPL_RCD=PS_TL_COMP_DAY_BAL.EMPL_RCD)
  AND ACTION='X'))
  AND EXISTS (
 SELECT 1
  FROM %Table(TL_TA_BATCH) I
 WHERE I.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
  AND I.EMPLID=PS_TL_COMP_DAY_BAL.EMPLID
  AND I.EMPL_RCD=PS_TL_COMP_DAY_BAL.EMPL_RCD)
  AND EXISTS (
 SELECT 1
  FROM %Table(TL_IPT1) A
 WHERE A.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
  AND A.EMPLID = PS_TL_COMP_DAY_BAL.EMPLID
  AND A.EMPL_RCD = PS_TL_COMP_DAY_BAL.EMPL_RCD
  AND A.TRC IN (
 SELECT B.TRC
  FROM PS_TL_TRC_TBL B
 WHERE B.COMP_LEAV_IND IN ('CERN', 'CTKN')
  AND B.EFFDT = (
 SELECT MAX(T1.EFFDT)
  FROM PS_TL_TRC_TBL T1
 WHERE T1.TRC = B.TRC
  AND T1.EFFDT <= A.DUR)))

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