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 SEPTEMBER 20, 2021
Applies to:
Oracle Shipping Execution - Version 12.1.3 and laterInformation in this document applies to any platform.
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
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 |
Changes |
Cause |
Solution |
References |