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

Last updated on JUNE 28, 2017

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 "_SYSSMU2268$" 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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms