ETL9.2: Peoplesoft delete statement is running for days without completion.
(Doc ID 3027381.1)
Last updated on JUNE 06, 2024
Applies to:
PeopleSoft Enterprise HCM Time and Labor - Version 9 to 9.2 [Release 9]Information in this document applies to any platform.
Symptoms
Peoplesoft delete statement is running for days without completion, getting a performance issue on the query below:
DELETE FROM PS_AUDIT_TLRPTTIME WHERE EXISTS (SELECT 'X' FROM PS_AUDIT_TLRPT_HST WHERE PS_AUDIT_TLRPT_HST.PSARCH_ID = 'AUDT_TLR' AND PSARCH_BATCHNUM = 38 AND PS_AUDIT_TLRPT_HST.AUDIT_OPRID = PS_AUDIT_TLRPTTIME.AUDIT_OPRID AND ( (PS_AUDIT_TLRPT_HST.AUDIT_STAMP IS NULL AND PS_AUDIT_TLRPTTIME.AUDIT_STAMP IS NULL) OR (PS_AUDIT_TLRPT_HST.AUDIT_STAMP = PS_AUDIT_TLRPTTIME.AUDIT_STAMP) ) AND PS_AUDIT_TLRPT_HST.AUDIT_ACTN = PS_AUDIT_TLRPTTIME.AUDIT_ACTN AND PS_AUDIT_TLRPT_HST.EMPLID = PS_AUDIT_TLRPTTIME.EMPLID AND PS_AUDIT_TLRPT_HST.EMPL_RCD = PS_AUDIT_TLRPTTIME.EMPL_RCD AND PS_AUDIT_TLRPT_HST.DUR = PS_AUDIT_TLRPTTIME.DUR AND PS_AUDIT_TLRPT_HST.SEQ_NBR = PS_AUDIT_TLRPTTIME.SEQ_NBR )
that running for more than 7000 mins without completion.
Steps to replicate:
1. Run the archive process via application engine process(PSARCHIVE)
2. Other PSARCHIVE process to delete other T&L tables are running without any issues.
3. Noticed a performance issue with only PS_AUDIT_TLRPTTIME table delete archival process
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 |