Refresh Order Management Materialized Views Running Long
(Doc ID 2461581.1)
Last updated on APRIL 04, 2025
Applies to:
Oracle Order Management - Version 12.1.3 and laterInformation in this document applies to any platform.
Symptoms
On : 12.1.3 version, Item Usage
ACTUAL BEHAVIOR
---------------
Refresh Order Management Materialized Views running long
The program was usually completing in 3-4 hours usually but now it's running for the past 24 hours
EXPECTED BEHAVIOR
-----------------------
Expect program to completes in 3-4 hours
STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Login as Order Management responsibility
2. Run Refresh Order Management Materialized View
==
AWR Report shows expensive query:
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "APPS"."OE_ITEMS_MV" SELECT citems.customer_item_number ITEM , citems.customer_item_id ITEM_ID , nvl(citems.customer_item_desc, T.description) ITEM_DESCRIPTION , B.inventory_item_id INVENTORY_ITEM_ID , CAST(B.concatenated_segments as VARCHAR2(1000)) INVENTORY_ITEM , 'CUST' ITEM_IDENTIFIER_TYPE , citems.customer_id SOLD_TO_ORG_ID , B.organization_id ORGANIZATION_ID , decode(citems.inactive_flag,'Y','INACTIVE','N','ACTIVE') ITEM_STATUS , decode(cxref.inactive_flag ,'Y', 'INACTIVE' ,'N','ACTIVE') CROSS_REF_STATUS , LOC.ADDRESS1 || LOC.ADDRESS2 Address , decode(flv.lookup_code,3, LOC.CITY || loc.state || loc.postal_code,2,arl.meaning,1,null) CUST_ADDRESS , flv.meaning ITEM_DEFINITION_LEVEL , cxref.preference_number RANK , SITE.CUST_ACCT_SITE_ID SITE_USE_ID , flv.lookup_code ITEM_DEFINITION_LEVEL_CODE , T.Language LANGUAGE , IC.CATEGORY_ID , 'Y' ORG_INDEPENDENT_FLAG , B.organization_id CROSS_REF_ORG_ID FROM mtl_customer_items citems , mtl_customer_item_xrefs cxref , MTL_SYSTEM_ITEMS_TL T , MTL_SYSTEM_ITEMS_B_KFV B , HZ_CUST_SITE_USES_ALL SITE , HZ_PARTY_SITES PARTY_SITE , HZ_LOCATIONS LOC , HZ_CUST_ACCT_SITES_ALL ACCT_SITE , AR_LOOKUPS ARL , fnd_lookup_values_vl flv , mtl_parameters mp , MTL_ITEM_CATEGORIES IC , MTL_DEFAULT_CATEGORY_SETS CS WHERE NVL(SITE.status, 'A') = 'A' AND NVL(SITE.site_use_code, 'SHIP_TO') = 'SHIP_TO' AND SITE.cust_acct_site_id (+) = citems.address_id AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID (+) AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID (+) AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID (+) AND citems.customer_item_id = cxref.customer_item_id AND cxref.inventory_item_id = B.inventory_item_id AND B.customer_order_enabled_flag = 'Y' AND B.bom_item_type in (1,4) AND B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID AND B.ORGANIZATION_ID = T.ORGANIZATION_ID AND citems.CUSTOMER_CATEGORY_CODE = ARL.LOOKUP_CODE(+) AND ARL.LOOKUP_TYPE(+) = 'ADDRESS_CATEGORY' AND flv.lookup_type(+) = 'INV_ITEM_DEFINITION_LEVEL' AND citems.item_definition_level = flv.lookup_code(+) AND mp.organization_id = B.organization_id AND cxref.master_organization_id = mp.master_organization_id AND IC.ORGANIZATION_ID = B.organization_id AND IC.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID AND IC.CATEGORY_SET_ID = CS.CATEGORY_SET_ID AND CS.functional_area_id = 7 UNION ALL SELECT CAST(B.concatenated_segments as VARCHAR2(1000)) ITEM , B.inventory_item_id ITEM_ID , T.description ITEM_DESCRIPTION , B.inventory_item_id INVENTORY_ITEM_ID , CAST(B.concatenated_segments as VARCHAR2(1000)) INVENTORY_ITEM , 'INT' ITEM_IDENTIFIER_TYPE , cast(NULL as NUMBER) SOLD_TO_ORG_ID , B.organization_id ORGANIZATION_ID , cast(NULL as VARCHAR2(10)) ITEM_STATUS , cast(NULL as VARCHAR2(10)) CROSS_REF_STATUS , cast(NULL as VARCHAR2(1000)) ADDRESS , cast(NULL as VARCHAR2(1000)) CUST_ADDRESS , cast(NULL as VARCHAR2(100)) ITEM_DEFINITION_LEVEL , cast(NULL as NUMBER) RANK , cast(NULL as NUMBER) SITE_USE_ID , cast(NULL as VARCHAR2(100)) ITEM_DEFINITION_LEVEL_CODE , T.Language LANGUAGE , IC.CATEGORY_ID , 'Y' ORG_INDEPENDENT_FLAG , B.organization_id CROSS_REF_ORG_ID FROM MTL_SYSTEM_ITEMS_TL T , MTL_SYSTEM_ITEMS_B_KFV B, MTL_ITEM_CATEGORIES IC, MTL_DEFAULT_CATEGORY_SETS CS WHERE B.customer_order_enabled_flag = 'Y' AND bom_item_type in (1,4) AND B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID AND B.ORGANIZATION_ID = T.ORGANIZATION_ID AND IC.ORGANIZATION_ID = B.organization_id AND IC.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID AND IC.CATEGORY_SET_ID = CS.CATEGORY_SET_ID AND CS.functional_area_id = 7 UNION ALL SELECT items.cross_reference ITEM , cast(NULL as NUMBER) ITEM_ID , nvl(items_tl.description, T.description) ITEM_DESCRIPTION , B.inventory_item_id INVENTORY_ITEM_ID , CAST(B.concatenated_segments as VARCHAR2(1000)) INVENTORY_ITEM , items.cross_reference_type ITEM_IDENTIFIER_TYPE , cast(NULL as NUMBER) SOLD_TO_ORG_ID , B.organization_id ORGANIZATION_ID , cast(NULL as VARCHAR2(10)) ITEM_STATUS , decode(sign(nvl(types.disable_date,sysdate)- sysdate),-1,'INACTIVE','ACTIVE') CROSS_REF_STATUS , cast(NULL as VARCHAR2(1000)) ADDRESS , cast(NULL as VARCHAR2(1000)) CUST_ADDRESS , cast(NULL as VARCHAR2(100)) ITEM_DEFINITION_LEVEL , cast(NULL as NUMBER) RANK , cast(NULL as NUMBER) SITE_USE_ID , cast(NULL as VARCHAR2(100)) ITEM_DEFINITION_LEVEL_CODE , T.Language LANGUAGE , IC.CATEGORY_ID , ITEMS.ORG_INDEPENDENT_FLAG , ITEMS.ORGANIZATION_ID CROSS_REF_ORG_ID FROM mtl_cross_reference_types types , mtl_cross_references_B items , mtl_cross_references_TL items_tl , MTL_SYSTEM_ITEMS_B_KFV B , MTL_SYSTEM_ITEMS_TL T , MTL_ITEM_CATEGORIES IC , MTL_DEFAULT_CATEGORY_SETS CS WHERE items.cross_reference_type = types.cross_reference_type AND items_tl.CROSS_REFERENCE_ID = items.CROSS_REFERENCE_ID AND b.inventory_item_id = items.inventory_item_id AND B.customer_order_enabled_flag = 'Y' and b.bom_item_type in (1,4) AND T.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID AND T.ORGANIZATION_ID = B.ORGANIZATION_ID AND T.language = items_tl.language AND IC.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID AND IC.ORGANIZATION_ID = B.organization_id AND IC.CATEGORY_SET_ID = CS.CATEGORY_SET_ID AND CS.functional_area_id = 7
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 |