My Oracle Support Banner

Incorrect Index Used in Runtime For Partitioned Tables in ASCP (Doc ID 1516361.1)

Last updated on FEBRUARY 03, 2019

Applies to:

Oracle Advanced Supply Chain Planning - Version 12.1.3 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.
***Checked for relevance on 02-JUN-2014***

Symptoms

On : 12.1.3 version, Memory Based Planner

ACTUAL BEHAVIOR
---------------
MSCNSP and MSONEW take a long time to complete. The issue is due to the fact that in runtime the database uses MSC_SYSTEM_ITEMS_N5 whereas the explain plan shows MSC_SYSTEM_U1.

EXPECTED BEHAVIOR
-----------------------
Code should be using MSC_SYSTEM_ITEMS_U1

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
Advanced supply chain planner
1. launch plan

Trace shows

UPDATE MSC_SYSTEM_ITEMS SET
 ITEM_NAME = :B6 , DESCRIPTION = :B5 , BUYER_NAME = :B4 , PLANNER_CODE = :B3
 , PLANNING_EXCEPTION_SET = :B2 , REVISION = :B1
WHERE
SR_INSTANCE_ID = :B9 AND INVENTORY_ITEM_ID = :B8 AND ORGANIZATION_ID = :B7
 AND PLAN_ID = :B10


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1  14529.18   14555.44          0 2233719530    3351704      127472
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2  14529.18   14555.44          0 2233719530    3351704      127472

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 173  (APPS)   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        0          0          0  UPDATE  MSC_SYSTEM_ITEMS (cr=2233719530 pr=0 pw=0 time=1665455403 us)
   127472     127472     127472   PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=2233230303 pr=0 pw=0 time=1624920755 us cost=2 size=310 card=1)
   127472     127472     127472    TABLE ACCESS BY LOCAL INDEX ROWID MSC_SYSTEM_ITEMS PARTITION: KEY KEY (cr=2233230303 pr=0 pw=0 time=1622862124 us cost=2 size=310 card=1)
3364208896 3364208896 3364208896     INDEX RANGE SCAN MSC_SYSTEM_ITEMS_N5 PARTITION: KEY KEY (cr=119768184 pr=0 pw=0 time=2121509758 us cost=2 size=0 card=14119)(object id 1119063)


Rows     Execution Plan
-------  ---------------------------------------------------
     0  UPDATE STATEMENT   MODE: ALL_ROWS
     0   UPDATE OF 'MSC_SYSTEM_ITEMS'
127472    PARTITION RANGE (SINGLE) PARTITION:KEYKEY
127472     INDEX   MODE: ANALYZED (RANGE SCAN) OF 'MSC_SYSTEM_ITEMS_U1'
               (INDEX (UNIQUE)) PARTITION:KEYKEY

 

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


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.