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

Last updated on MARCH 08, 2017

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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms