PeopleSoft developer on retainer: Application Engine GLTRANCOMP updates 22,847 PS_COMPENSATION rows without CommitWork checkpoint — Oracle holds row locks for 3 hours, blocking concurrent HR transactions with ORA-00054 errors — PeopleSoft developer on monthly retainer

PeopleSoft HCM 9.2 • PeopleTools 8.58 • Oracle 19c • Application Engine GLTRANCOMP • PS_COMPENSATION • CheckPoint Frequency 0 • ORA-00054 • State Record restart • retainer billing

At Great Lakes Transit Authority, the quarterly compensation recalculation run produced ORA-00054 errors on 18 to 25 percent of concurrent HR transactions for three hours and fourteen minutes. Benefits administrators updating employee compensation records, HR clerks confirming job data changes, and payroll preparers running pre-calculation audits all reported the same error during the quarterly run window: ORA-00054: resource busy and acquire with NOWAIT specified, or timeout expired. The errors cleared the moment the recalculation finished. The PeopleSoft developer retainer was engaged to find why a single Application Engine program was locking the entire HR team out of PS_COMPENSATION for a full quarter-end afternoon.

Great Lakes Transit ran PeopleSoft HCM 9.2 on PeopleTools 8.58 with Oracle 19c as the database. The recalculation was performed by a custom Application Engine program, GLTRANCOMP (“Transit Compensation Recalculator”), written four years earlier during a compensation structure redesign. GLTRANCOMP ran once per quarter, triggered by the compensation director from a PeopleSoft Process Scheduler run control. Its purpose was to recalculate the COMPRATE field on PS_COMPENSATION for all active employees based on updated pay grade and step tables that HR had refreshed at the start of each quarter. GLTRANCOMP read all active employees from PS_JOB, looked up each employee’s pay grade and step in a custom pay table (PS_GLTRANS_PAYTBL), computed a new COMPRATE, and updated the corresponding PS_COMPENSATION row.

PeopleSoft Application Engine: CheckPoint Frequency and transaction scope

In PeopleSoft Application Engine, every Step has a CheckPoint Frequency property. When CheckPoint Frequency is set to a positive integer N, the AE runtime automatically commits the current database transaction and saves a restart checkpoint to the PS_AERUNCONTROL table every N rows processed by that Step. When CheckPoint Frequency is set to 0, no automatic checkpointing occurs — the entire Step runs in a single database transaction from the first row to the last.

For small batch Steps processing a few hundred rows, CheckPoint Frequency = 0 is harmless: the single transaction commits in seconds, locks are released promptly, and no concurrent user notices. For Steps processing tens of thousands of rows — as employee headcount grows over the years after initial development — the single transaction becomes a long-duration lock event. Every database row modified within the Step is held under an exclusive Oracle row lock (TX lock mode) for the full duration of the Step, from the first UPDATE to the final implicit commit at Step completion. Any other Oracle session that tries to acquire a lock on any of those rows — including any HR transaction that updates PS_COMPENSATION for any employee processed so far in the run — receives ORA-00054 immediately (because PeopleSoft uses SELECT ... FOR UPDATE NOWAIT in many PeopleCode database calls) or waits for a lock timeout (depending on the Oracle session lock wait timeout setting).

How the volume grew and the quarterly disruption pattern developed

When GLTRANCOMP was written in 2021, Great Lakes Transit had 6,112 active employees. The quarterly recalculation ran in 34 minutes. CheckPoint Frequency was left at 0 by the original developer — a common default omission for batch Steps where the volume at development time produces no visible contention.

By Q1 2023, active employee count had grown to 14,290 through two service area expansions. Run time: 1h47m. Several HR clerks reported occasional ORA-00054 errors during the run window, typically when updating recently hired employees whose COMPRATE was being recalculated. The errors were attributed to “system load during processing” and users were told to retry after a few minutes.

By Q3 2024, active employee count reached 22,847 following a merger with a regional bus operator. Run time: 3h14m. The full afternoon shift of the HR team (21 users) overlapped with the recalculation window. ORA-00054 errors affected 18 to 25 percent of all HR transactions during those three hours. Benefits administrators could not save compensation changes; payroll audits failed mid-run; managers confirming job data for new hires received errors. The compensation director escalated to the PeopleSoft developer retainer.

Diagnosis: Oracle v$lock and v$session during the run

The PeopleSoft developer retainer queried Oracle lock views during a reproduction run on the test environment with a copy of the Q3 2024 employee data. Ninety minutes into the run, the following query against v$lock and v$session confirmed the root cause:

SELECT s.sid,
       s.username,
       s.program,
       l.type,
       l.lmode,
       l.request,
       l.ctime AS lock_hold_seconds
FROM   v$lock   l
JOIN   v$session s ON s.sid = l.sid
WHERE  l.type  = 'TX'
ORDER BY l.ctime DESC;

Result: one session with SID 447 (username PSAPPS, program PSAE.GLTRANCOMP) holding 22,847 TX row locks in mode 6 (exclusive), with ctime = 5,412 seconds (90 minutes and 12 seconds into the run). A second query against v$session with wait_class = 'Application' showed 31 blocked HR sessions, all waiting on enq: TX - row lock contention with wait times between 0.4 seconds and 182 seconds. The 31 blocked sessions were PeopleSoft HCM component save operations and PeopleCode SQLExec calls updating PS_COMPENSATION rows that GLTRANCOMP had already locked in its single transaction.

The AE program design review confirmed CheckPoint Frequency = 0 on Section MAIN, Step02 (the PeopleCode step containing the employee loop):

/* GLTRANCOMP Section MAIN, Step02 — original PeopleCode (no CommitWork) */
Local Record &compRec;
Local SQL    &emplSQL;
Local string &EMPLID;
Local number &newCOMPRATE;

&emplSQL = CreateSQL("SELECT EMPLID FROM PS_JOB WHERE EMPL_STATUS = 'A'
                     AND EFFDT = (SELECT MAX(EFFDT) FROM PS_JOB J2
                                  WHERE J2.EMPLID = PS_JOB.EMPLID
                                    AND J2.EFFDT <= %DateIn(:1))
                     ORDER BY EMPLID", %Date);

While &emplSQL.Fetch(&EMPLID)
   /* Look up new COMPRATE from pay table */
   &newCOMPRATE = GetNewCompRate(&EMPLID);   /* custom function */

   /* Update PS_COMPENSATION */
   &compRec = CreateRecord(Record.PS_COMPENSATION);
   &compRec.EMPLID.Value    = &EMPLID;
   &compRec.EFFDT.Value     = %Date;
   &compRec.COMPRATE.Value  = &newCOMPRATE;
   &compRec.Update();
   /* No CommitWork here — entire loop in one transaction */
End-While;

Every &compRec.Update() call added one Oracle row lock to the session’s transaction. 22,847 employees = 22,847 exclusive Oracle TX locks accumulating over 3h14m. PeopleSoft’s component save operations and many PeopleCode database operations use SELECT ... FOR UPDATE NOWAIT internally, which returns ORA-00054 immediately rather than waiting, making the error visible to users in real time rather than as a silent timeout.

The two-part fix: CheckPoint Frequency and State Record restart logic

Part 1 — CheckPoint Frequency: In Application Designer, the CheckPoint Frequency on Section MAIN, Step02 was changed from 0 to 500. This instructs the PeopleSoft AE runtime to issue a database COMMIT and save a restart checkpoint to PS_AERUNCONTROL every 500 rows processed by the Step. The runtime manages this automatically — the PeopleCode in Step02 does not need to call CommitWork() explicitly when using CheckPoint Frequency. At any point during the run, the maximum number of Oracle TX locks held simultaneously is 500.

Part 2 — State Record restart logic: When an AE program with CheckPoint Frequency > 0 is restarted after a failure (via PeopleSoft Process Scheduler, using the same Run Control ID and Process Instance), the AE runtime resumes from the last saved checkpoint rather than from the beginning. For this to work correctly for GLTRANCOMP, the PeopleCode needed to record the last-committed EMPLID and skip already-processed employees on restart. A State Record was added:

/* State Record: GLTRANCOMP_AER
   Fields: PROCESS_INSTANCE (key), LAST_EMPLID CHAR(11), ROWS_COMMITTED NUMBER */

/* GLTRANCOMP Section MAIN, Step02 — fixed PeopleCode */
Local Record &compRec;
Local SQL    &emplSQL;
Local string &EMPLID;
Local number &newCOMPRATE;
Local string &lastCommitted;
Local number &rowCount;

/* Read last committed EMPLID from State Record (blank if first run) */
&lastCommitted = GLTRANCOMP_AER.LAST_EMPLID.Value;
&rowCount      = 0;

/* Skip already-committed employees by filtering EMPLID > last committed */
If &lastCommitted = "" Then
   &emplSQL = CreateSQL("SELECT EMPLID FROM PS_JOB WHERE EMPL_STATUS = 'A'
                         AND EFFDT = (SELECT MAX(EFFDT) FROM PS_JOB J2
                                      WHERE J2.EMPLID = PS_JOB.EMPLID
                                        AND J2.EFFDT <= %DateIn(:1))
                         ORDER BY EMPLID", %Date);
Else
   &emplSQL = CreateSQL("SELECT EMPLID FROM PS_JOB WHERE EMPL_STATUS = 'A'
                         AND EMPLID > :1
                         AND EFFDT = (SELECT MAX(EFFDT) FROM PS_JOB J2
                                      WHERE J2.EMPLID = PS_JOB.EMPLID
                                        AND J2.EFFDT <= %DateIn(:2))
                         ORDER BY EMPLID", &lastCommitted, %Date);
End-If;

While &emplSQL.Fetch(&EMPLID)
   &newCOMPRATE = GetNewCompRate(&EMPLID);

   &compRec = CreateRecord(Record.PS_COMPENSATION);
   &compRec.EMPLID.Value   = &EMPLID;
   &compRec.EFFDT.Value    = %Date;
   &compRec.COMPRATE.Value = &newCOMPRATE;
   &compRec.Update();

   /* Update State Record with last processed EMPLID */
   /* (AE runtime commits and saves checkpoint every 500 rows via CheckPoint Frequency) */
   GLTRANCOMP_AER.LAST_EMPLID.Value     = &EMPLID;
   GLTRANCOMP_AER.ROWS_COMMITTED.Value  = &rowCount + 1;
   GLTRANCOMP_AER.Save();

   &rowCount = &rowCount + 1;
End-While;

The GLTRANCOMP_AER.Save() call updates the State Record in the same transaction batch as the PS_COMPENSATION update. When the AE runtime fires a checkpoint (every 500 rows), it commits the accumulated updates and reads the current State Record values — so the last-committed EMPLID is always accurate for restart purposes. If the process is killed or fails between checkpoints, the restart begins at the last checkpointed EMPLID (at most 499 rows before the failure point), not at employee 1.

Results after the fix

After applying CheckPoint Frequency = 500 and the State Record restart logic, the next quarterly run on 22,847 employees completed in 22 minutes. Peak Oracle TX locks during the run: 500. The Oracle v$lock query during the fixed run showed at most 500 TX locks from the GLTRANCOMP session at any checkpoint interval, cycling down to 0 at each commit and rebuilding to 500 for the next batch. ORA-00054 errors from the HR team during the run: zero. The HR team no longer had any awareness of the quarterly compensation recalculation running — it had become an invisible background operation.

Run time improvement (3h14m to 22 minutes) was larger than the lock reduction alone would explain. The reduction was partly from removing lock management overhead: when Oracle holds 22,847 row locks in one transaction, internal Oracle lock table bookkeeping consumes CPU and memory proportional to the lock count. With locks cycling at 500 at a time, that overhead is eliminated, and the per-row UPDATE operations run faster.

Why CheckPoint Frequency = 0 is a recurring PeopleSoft retainer issue

The PeopleSoft Application Engine developer guide documents CheckPoint Frequency and explains the commit and restart behavior. But the default value in Application Designer when creating a new Step is 0 — no automatic checkpointing. Developers writing batch programs for the first time, or writing programs that initially process small volumes, leave the default in place without observing any issue. When the same program is applied to larger data volumes after headcount growth, an acquisition, or a scope expansion, the lock contention emerges gradually: first as sporadic ORA-00054 errors that users attribute to “PeopleSoft being slow,” then as a reproducible quarterly disruption affecting the entire HR team.

The fix pattern is well-defined: audit AE Steps with CheckPoint Frequency = 0 that process > 1,000 rows, set CheckPoint Frequency to 500–1,000, add a State Record field for the last-committed primary key, and update the PeopleCode loop to populate that field and use it for restart filtering. A retainer developer who has applied this pattern once can execute the full fix in 5 hours.

How HourTab helps PeopleSoft developer retainers

The Great Lakes Transit engagement totaled 5.0 hours: Oracle lock analysis (1.0h, querying v$lock and v$session during a reproduction run, confirming 22,847 TX locks held by GLTRANCOMP SID 447 and 31 blocked HR sessions with wait class enq: TX - row lock contention), AE program design review (1.0h, confirming CheckPoint Frequency = 0 on Section MAIN Step02, tracing the While loop over 22,847 PS_JOB rows, confirming &compRec.Update() with no CommitWork() or checkpoint between iterations), fix implementation (2.0h, setting CheckPoint Frequency to 500 in Application Designer, designing and adding State Record GLTRANCOMP_AER with LAST_EMPLID and ROWS_COMMITTED fields, updating the PeopleCode loop to populate State Record fields and add EMPLID > restart filter), and testing (1.0h, confirming peak Oracle TX lock count at 500, run time at 22 minutes on full Q3 employee set, zero ORA-00054 errors from concurrent HR test sessions). Ongoing retainer monitoring: 0.15h per quarter to confirm the run completed within the expected 15–25 minute window and that no new high-volume AE Steps have been added with CheckPoint Frequency = 0.

In a HourTab retainer log, the investigation session reads: “GLTRANCOMP CheckPoint Frequency = 0: quarterly run 3h14m, ORA-00054 on 18-25% of concurrent HR transactions; Oracle v$lock SID 447 (PSAE.GLTRANCOMP): 22,847 TX locks mode 6 at 90-minute mark; 31 blocked HR sessions wait enq: TX - row lock contention; AE review: Section MAIN Step02 CheckPoint Frequency = 0; While loop over 22,847 active employees; compRec.Update() per employee, no CommitWork; fix: CheckPoint Frequency = 500; State Record GLTRANCOMP_AER added (LAST_EMPLID, ROWS_COMMITTED); PeopleCode updated to populate State Record per row and filter EMPLID > LAST_EMPLID on restart; test Q3 data: peak Oracle TX locks = 500; run time = 22 min; ORA-00054 = 0; 5.0h.” Great Lakes Transit’s compensation director can see the 3-hour quarterly disruption, the Oracle lock root cause, and the checkpoint fix that converted a 3h14m lock storm into a 22-minute invisible background run.

HourTab turns a time-tracker CSV into a public retainer-hours URL your client can bookmark — no login, no portal, no “how many hours do I have left?” emails. Free for one retainer. $9/mo for up to ten.

FAQ: PeopleSoft developer retainers

See also: Oracle Forms developer retainer · JD Edwards developer retainer · SAP ABAP developer retainer