MSRFWOR Refresh Collection Snapshots Performance Database Bug 4196039 ORA-12008 ORA-01555
(Doc ID 369173.1)
Last updated on FEBRUARY 04, 2022
Applies to:
Oracle Advanced Supply Chain Planning - Version 11.5.10 to 12.0.6 [Release 11.5 to 12]Information in this document applies to any platform.
Symptoms
MSRFWOR Refresh Collection Snapshots Performance Database <Bug 4196039>
MSRFWOR Refresh Collection Snapshots poor performance or completes with error:
ORA-12008: error in materialized view refresh path
ORA-01555: snapshot too old: rollback segment number 2268 with name "_<SYSNAME>$" too small
The program runs successfully in the night time and is failing during day time intermittently. Sometimes it completes with long run time during day but not consistent.The refresh snapshot program is failing with Rollback segment too old error.
Trace files show that various hints are being used resulting poor execution plans:
Example:
SELECT /*+NO_MERGE("JV$") */ "JV$"."WIP_ENTITY_ID",
"JV$"."OPERATION_SEQ_NUM","JV$"."RESOURCE_SEQ_NUM",
"JV$"."ORGANIZATION_ID","JV$"."SCHEDULE_SEQ_NUM",
"JV$"."SUBSTITUTE_GROUP_NUM","JV$"."RESOURCE_ID",
"JV$"."SCHEDULED_FLAG","JV$"."START_DATE",
"JV$"."COMPLETION_DATE","JV$"."BASIS_TYPE",
"JV$"."USAGE_RATE_OR_AMOUNT","JV$"."ASSIGNED_UNITS",
"JV$"."APPLIED_RESOURCE_UNITS","JV$"."UOM_CODE",
"JV$"."REPETITIVE_SCHEDULE_ID","JV$"."REPLACEMENT_GROUP_NUM","JV$".
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 |