ETL9.2 Performance Issue On The "Payable Time Approvals" Pagelet (Doc ID 2082522.1)

Last updated on AUGUST 28, 2023

Applies to:

Symptoms

DESCRIPTION
===========
Performance issue on TL Dashboard when clicked on Approve payable time

REPLICATION STEPS
=================
1) Navigate to TL Dashboard
2) Use 50+ Concurrent users to access same page
3) Page keep clocking
4) In some cases in crashes App Server.

ENVIRONMENT INFORMATION
=======================
Peoplesoft HCM 9.2 PUM 12
PeopleTools 8.54

DISCUSSION
==========
After analyzing AWR report & SQLT following Query is causing performance
issue:

SELECT DISTINCT J.EMPLID FROM PS_JOBCODE_TBL JC, PS_JOB J, PS_TL_EMPL_DATA
@ ED, PS_TL_GROUP_DTL F , PS_TL_GRP_SECURITY S , PS_PERSON_NAME A , PS_DEPT_TBL
D, PS_LOCATION_TBL L , PS_TL_AP_PYTIME_VW TL WHERE S.TL_GROUP_ID =
F.TL_GROUP_ID AND F.EMPLID = J.EMPLID AND F.EMPL_RCD = J.EMPL_RCD AND
F.EMPLID = A.EMPLID AND F.EMPLID = ED.EMPLID AND F.EMPL_RCD = ED.EMPL_RCD AND
S.ROWSECCLASS = :1 AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE
J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND J1.EFFDT <= TO_DATE(:2,
'YYYY-MM-DD')) AND J.EFFSEQ = ( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE
J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT = J.EFFDT )
AND ED.EFFDT = ( SELECT MAX(ED1.EFFDT) FROM PS_TL_EMPL_DATA ED1 WHERE
ED1.EMPLID = ED.EMPLID AND ED1.EMPL_RCD = ED.EMPL_RCD AND ED1.EFFDT <=
TO_DATE(:3, 'YYYY-MM-DD')) AND JC.SETID = J.SETID_JOBCODE AND JC.JOBCODE =
J.JOBCODE AND JC.EFFDT = ( SELECT MAX(JC1.EFFDT) FROM PS_JOBCODE_TBL JC1
WHERE JC1.SETID = J.SETID_ JOBCODE AND JC1.JOBCODE = J.JOBCODE AND JC1.EFFDT
<= TO_DATE(:4, 'YYYY-MM-DD')) AND D.SETID = J.SETID_DEPT AND D.DEPTID =
J.DEPTID AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_DEPT_TBL D1 WHERE
D1.SETID = J.SETID_DEPT AND D1.DEPTID = J.DEPTID AND D1.EFFDT <= TO_DATE(:5,
'YYYY-MM-DD')) AND J.SETID_LOCATION = L.SETID AND J.LOCATION=L.LOCATION AND
L.EFFDT = ( SELECT MAX(L1.EFFDT) FROM PS_LOCATION_TBL L1 WHERE L1.SETID =
L.SETID AND L1.LOCATION = L.LOCATION AND L1.EFFDT <= TO_DATE(:6,
'YYYY-MM-DD')) AND A.EMPLID <> '10242454' AND EXISTS (SELECT 1 FROM
PS_TL_PAYABLE_TIME PT WHERE PT.EMPLID=J.EMPLID AND PT.EMPL_RCD=J.EMPL_RCD AND
PT.DUR BETWEEN TO_DATE('1990-01-01', 'YYYY-MM-DD') AND TO_DATE('2025-09-12',
'YYYY-MM-DD') AND PT.PAYABLE_STATUS = 'NA' AND PT.SEQ_NBR IN (SELECT
AP.SEQ_NBR FROM PS_TL_APP_PAY_VW AP WHERE J.EMPLID=AP.EMPLID AND
J.EMPL_RCD=AP.EMPL_RCD AND AP.OPRID = 'JTHOMAS' )) AND TL.EMPLID <>
'10242454' AND TL.DUR BETWEEN TO_DATE('1990-01-01', 'YYYY-MM-DD') AN D
TO_DATE('2025-09-12', 'YYYY-MM-DD')

Cause

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