My Oracle Support Banner

MSRFWOR Refresh Collection Snapshots Performance Database Bug 4196039 ORA-12008 ORA-01555 (Doc ID 369173.1)

Last updated on SEPTEMBER 06, 2020

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:

/* MV_REFRESH (INS) */ INSERT INTO "WIP"."WIP_WOPR_RESS_SN"
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

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