My Oracle Support Banner

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


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