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:
Oracle Warehouse Management - Version 12.2 and laterInformation in this document applies to any platform.
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
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 |
Changes |
Cause |
Solution |
References |