Planning Data Pull Takes A Long Time To Complete (around 9 Hrs) After R12.2.4 Upgade (Doc ID 2080199.1)

Last updated on JUNE 28, 2017

Applies to:

Oracle Advanced Supply Chain Planning - Version 12.2.4 and later
Information in this document applies to any platform.

Symptoms

On a 12.2.4 Instance With VCP Cum Patch 12.2.4.1 - 19549533 - MANDATORY PATCH applied

ACTUAL BEHAVIOR  
---------------
When running Standard Data Collections, Planning Data Pull takes a long time to complete (around 9 hrs) after R12.2.4 upgrade  

Refresh snapshot completed in an acceptable amount of time. After that the planning data pull is hanging on the following SQL
-----------------------------------------------------------------------------------------------------
select wsc.co_product_group_id, wsc.bill_Sequence_id*2, wsc.co_product_id, bom.common_bill_Sequence_id, wsc.usage_rate, perc.split, perc.effectivity_date,
-- Added for BUG 14133049/13530488
decode(nvl(wsc.primary_flag,'N'),'N',2,1) primary_flag, bom.rowid
from wsm_Co_products@APPS_ERP wsc, wsm_coproduct_split_perc@APPS_ERP perc, msc_st_boms bom
where wsc.bill_sequence_id is not null and wsc.bill_Sequence_id*2 = bom.bill_Sequence_id and perc.co_product_group_id = wsc.co_product_group_id and perc.co_product_id = wsc.co_product_id and sysdate < nvl(perc.disable_date,sysdate + 1) and perc.split>0
-----------------------------------------------------------------------------------------------------

Running Gather Table Statistics on the tables involved in the query did not help.

If we have records in MSC_ST_BOMS and then run table statistics, then run the query manually, the query will run fast.

The bad explain plan occurs when MSC_ST_BOMS is empty, and the query starts with that table and performs a CARTESIAN JOIN, which seems to cause the performance issue.

When we add a leading hint to the query that tells the CBO to start with the WSM_COPRODUCTS_SPLIT_PERCENT table, the query runs in less than 2 seconds.

We have organizations that use co-products, so we cannot just truncate the WSM_ tables as mentioned in:

Bug 9124885 : R12 PLANNING DATA PULL IS TAKING 10+ HOURS

EXPECTED BEHAVIOR
-----------------------
Expect Planning Data Pull to complete in a reasonable amount of time

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run Standard Data Collections
2. Note the performance issue in Planning Data Pull

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