Performance: Purge WMS History Tables Program Takes A Long Time When Run For All Purge Types And Completes With Error ORA-01555: Snapshot Too Old (Doc ID 2935702.1)

Last updated on MARCH 16, 2023

Applies to:

Symptoms

Purge WMS History Tables takes a long time to run and eventually errors out.

ERROR
-----------------------
FDPSTP failed due to ORA-01555: snapshot too old: rollback segment number 1291 with name "_SYSSMU1291_922703219$" too small
ORA-06512: at "APPS.WMS_PURGE_PVT", line 532
ORA-06512: at line 1.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Warehouse management > Other > Purge WMS History tables > Purge type <Blank> (All)
2. Submit program > See it completes with error.

Reviewing trace confirms the performance issue when executing below query:

This shows enormously high disk accesses and query gets, though the total number of records is not that huge.

Changes

Cause

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