Poor Performance Against MTL_SYSTEM_ITEMS_B After Upgrade From 11.2.0.4 To 12.1.0.2 (Doc ID 2625210.1)

Last updated on DECEMBER 31, 2019

Applies to:

Symptoms

On : 12.1.3 version,

Customer is reporting Performance Issue with a full table scan against
MTL_SYSTEM_ITEMS_B and MTL_LOT_NUMBERS


select 'x'
from
dual where :1 IN (SELECT wda.delivery_id FROM apps.wsh_delivery_assignments
 wda, apps.wsh_delivery_details wdd, apps.mtl_lot_numbers mln,
 apps.mtl_system_items_b msi, apps.oe_order_headers_all oeh WHERE
 wda.delivery_detail_id = wdd. delivery_detail_id AND wdd.source_code = 'OE'
 AND wdd.source_header_id = oeh.header_id AND wdd.organization_id =
 mln.organization_id AND wdd.lot_number = mln.lot_number AND
 wdd.inventory_item_id = mln.inventory_item_id AND wda.delivery_id = :2 AND
 wdd.organization_id = msi.organization_id AND wdd.inventory_item_id =
 msi.inventory_item_id AND TRUNC(mln.expiration_date) <= TRUNC(SYSDATE) )
 and rownum=1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 3 50.37 309.40 429856 47952952 0 0
Fetch 3 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 50.37 309.40 429856 47952952 0 0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 239 (APPS)
Number of plan statistics captured: 3

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
  0 0 0 COUNT STOPKEY (cr=15984317 pr=143285 pw=0 time=103115364 us)
  0 0 0 FILTER (cr=15984317 pr=143285 pw=0 time=103115362 us)
  0 0 0 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
  0 0 0 FILTER (cr=15984317 pr=143285 pw=0 time=103115356 us)
  0 0 0 NESTED LOOPS SEMI (cr=15984317 pr=143285 pw=0 time=103115355 us cost=36384 size=186 card=2)
  0 0 0 NESTED LOOPS SEMI (cr=15984317 pr=143285 pw=0 time=103115352 us cost=36382 size=174 card=2)
  0 1763285 2644928 NESTED LOOPS SEMI (cr=15984308 pr=143285 pw=0 time=83119898 us cost=36380 size=150 card=2)
  0 1763285 2644928 NESTED LOOPS (cr=15860065 pr=141846 pw=0 time=81838673 us cost=1751 size=11375 card=175)
  0 243544 365316 TABLE ACCESS FULL MTL_LOT_NUMBERS (cr=18129 pr=14983 pw=0 time=839901 us cost=28 size=972439 card=31369)
  
  
  
  
  
  0 1763285 2644928 TABLE ACCESS BY INDEX ROWID BATCHED WSH_DELIVERY_DETAILS (cr=15841937 pr=126863 pw=0 time=100975115 us cost=66 size=10608 card=312)
  0 18998267 28497401 INDEX RANGE SCAN WSH_DELIVERY_DETAILS_N9 (cr=552798 pr=4604 pw=0 time=8801114 us cost=2 size=0 card=101)(object id 384758)
  0 62153 93229 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1 (cr=124243 pr=1439 pw=0 time=906488 us cost=1 size=17360470 card=1736047)(object id 383320)
  0 0 0 TABLE ACCESS BY INDEX ROWID BATCHED WSH_DELIVERY_ASSIGNMENTS (cr=9 pr=0 pw=0 time=4859684 us cost=1 size=61531476 card=5127623)
  0 3526571 5289856 INDEX RANGE SCAN WSH_DELIVERY_ASSIGNMENTS_N1 (cr=9 pr=0 pw=0 time=2545612 us cost=1 size=0 card=1)(object id 384735)
  0 0 0 INDEX UNIQUE SCAN OE_ORDER_HEADERS_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=8797122 card=1466187)(object id 378419)

 



STEPS
-----------------------
The issue can be reproduced at will with the following steps:

1.Responsibility : Oracle Order Management
2.Navigate to Shipping
 


BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot efficiently process order

Changes

Cause

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