My Oracle Support Banner

Refresh Order Management Materialized Views Is Now Taking 6+ Hours And Failing (Doc ID 2801719.1)

Last updated on AUGUST 24, 2021

Applies to:

Oracle Order Management - Version 12.2 and later
Information in this document applies to any platform.

Symptoms

When attempting to Run > “Refresh Order Management Materialized Views”
users are observing a severe performance which is upwards of 4-6 hours
when previously it was only taking 1.5 to 2 hours to complete successfully
and the following error occurs.

ERROR
-----------------------
Refresh Order Management Materialized Views gets the following error:
Cause: FDPSTP failed due to ORA-12008: error in materialized view refresh path
ORA-01555: snapshot too old: rollback segment number nn1 with name "_SYS_123$" too small
ORA-06512: at "SYS.DBMS_SNAPSHOT", line nnn1

STEPS
-----------------------
1. Responsibility: Order Management Super User
2. Run concurrent request "Refresh Order Management Materialized Views"
3. Observe major performance.

TKPROF
---------
INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND */ INTO "APPS"."OE_ITEMS_MV"
(SELECT "A25"."CUSTOMER_ITEM_NUMBER" "ITEM","A25"."CUSTOMER_ITEM_ID"
"ITEM_ID",NVL("A25"."CUSTOMER_ITEM_DESC","A23"."DESCRIPTION")
"ITEM_DESCRIPTION","A22"."INVENTORY_ITEM_ID" "INVENTORY_ITEM_ID",
CAST("A22"."CONCATENATED_SEGMENTS" AS VARCHAR2(1000)) "INVENTORY_ITEM",
'CUST' "ITEM_IDENTIFIER_TYPE","A25"."CUSTOMER_ID" "SOLD_TO_ORG_ID",
"A22"."ORGANIZATION_ID" "ORGANIZATION_ID",DECODE("A25"."INACTIVE_FLAG",'Y',
'INACTIVE','N','ACTIVE') "ITEM_STATUS",DECODE("A24"."INACTIVE_FLAG",'Y',
'INACTIVE','N','ACTIVE') "CROSS_REF_STATUS",
"A19"."ADDRESS1"||"A19"."ADDRESS2" "ADDRESS",DECODE("A16"."LOOKUP_CODE",3,
"A19"."CITY"||"A19"."STATE"||"A19"."POSTAL_CODE",2,"A17"."MEANING",1,NULL)
"CUST_ADDRESS","A16"."MEANING" "ITEM_DEFINITION_LEVEL",
"A24"."PREFERENCE_NUMBER" "RANK","A21"."CUST_ACCT_SITE_ID" "SITE_USE_ID",
"A16"."LOOKUP_CODE" "ITEM_DEFINITION_LEVEL_CODE","A23"."LANGUAGE"
"LANGUAGE","A14"."CATEGORY_ID" "CATEGORY_ID",'Y' "ORG_INDEPENDENT_FLAG",
"A22"."ORGANIZATION_ID" "CROSS_REF_ORG_ID" FROM "INV"."MTL_CUSTOMER_ITEMS"
"A25","INV"."MTL_CUSTOMER_ITEM_XREFS" "A24","INV"."MTL_SYSTEM_ITEMS_TL"
"A23", (SELECT "A34".ROWID "ROW_ID","A34"."INVENTORY_ITEM_ID" etc

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.15 0.17 9 39 0 0
Execute 1 2068.64 4619.42 21309508 41371926 131482133 308651237
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 2068.79 4619.60 21309517 41371965 131482133 308651237

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.