Net Change Data Collections Intermittent Performance Issue (Doc ID 2292747.1)

Last updated on AUGUST 01, 2017

Applies to:

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

Symptoms

On : 12.2.4 version, Data Collections in Production and Test

Users are on 12.2.4 and have applied VCP Cum Patch 12.2.4.1 - 19549533 in the ASCP destination instance. They have EBS source instance on 12.1.3 and have applied VCP 12.1.3.9.1 Patch 17350769.

They run complete refresh of data collections on Sundays. From Monday through Friday they run Net Change Collections several times during the day as follows:

8:15 - Net change collections for US Orgs only - No issue
10:15 - Net change collections for All Orgs - They have performance issue here only
12:30 - Net change collections for All Orgs - No Issue
14:30 - Net change collections for All Orgs - No Issue
16:30 - Net change collections for All Orgs - No Issue
18:30 - Net change collections for All Orgs - No Issue
23:30 - Full Data Collections - No Issue

The issue appears to be specifically in Planning Data Pull in log file MSCPDPW: Planning Data Pull Worker

26-JUN 10:55:18 : Task Number is 12
26-JUN 10:55:18 : Global RN (msc_cl_pull.v_lrn): 176242530
26-JUN 10:55:18 : Procedure MSC_CL_BOM_PULL.LOAD_BOM started.
26-JUN 15:11:01 : WSM_COPRODUCT_SPLIT_PERC Exists
26-JUN 15:11:02 : 255.8 minutes elapsed.

So the issue appears to be gathering BOM data from the source EBS instance. They had what they thought was similar issue through SR 3-13855029961 however that had to do with Refresh Collection Snapshots performance and the issue here is with Planning Data Pull or it's Worker(s)

From AWR reports, the culprit is on the EBS Source instance with only 1 execution for

SQL ID = 52cvu5x2thd7c took 15,282 seconds = 4.25 hrs - This was only executed 1 time ONLY

SELECT "COMPONENT_SEQUENCE_ID", "INVENTORY_ITEM_ID", "BILL_SEQUENCE_ID", "OPERATION_SEQ_NUM", "USAGE_QUANTITY", "COMPONENT_YIELD_FACTOR", "EFFECTIVITY_DATE", "DISABLE_DATE", "OPERATION_OFFSET_PERCENT", "OPTIONAL_COMPONENT", "WIP_SUPPLY_TYPE", "PLANNING_FACTOR", "REVISED_ITEM_SEQUENCE_ID", "ATP_FLAG", "FROM_UNIT_NUMBER", "TO_UNIT_NUMBER", "STATUS_TYPE", "USE_UP_CODE", "DRIVING_ITEM_ID", "CHANGE_NOTICE", "ORGANIZATION_ID", "USING_ASSEMBLY_ID", "ROUNDING_DIRECTION", "RN3", "BASIS_TYPE", "OLD_COMPONENT_SEQUENCE_ID" FROM "MRP_AN_BOM_COMPONENTS_V" "X" WHERE ("ORGANIZATION_ID"=99 OR "ORGANIZATION_ID"=116 OR "ORGANIZATION_ID"=117 OR "ORGANIZATION_ID"=118 OR "ORGANIZATION_ID "=119 OR "ORGANIZATION_ID"=120 OR "ORGANIZATION_ID"=121 OR "ORGANIZATION_ID"=137 OR "ORGANIZATION_ID"=138 OR "ORGANIZATION_ID"=139 OR "ORGANIZATION_ID"=140 OR "ORGANIZATION_ID"=141 OR "ORGANIZATION_ID"=142 OR "ORGANIZATION_ID"=156 OR "ORGANIZATION_ID"=157 OR "ORGANIZATION_ID"=158 OR "ORGANIZATION_ID"=159 OR "ORGANIZATION_ID"=160 OR "ORGANIZATION_ID"=161 OR "ORGANIZATION_ID"=162 OR "ORGANIZATION_ID"=163 OR "ORGANIZATION_ID"=164 OR "ORGANIZATION_ID"=166 OR "ORGANIZATION_ID"=176 OR "ORGANIZATION_ID"=177 OR "ORGANIZATION_ID"=178 OR "ORGANIZATION_ID"=179 OR "ORGANIZATION_ID"=180 OR "ORGANIZATION_ID"=181 OR "ORGANIZATION_ID"=182 OR "ORGANIZATION_ID"=185 OR "ORGANIZATION_ID"=187 OR "ORGANIZATION_ID"=188 OR " ORGANIZATION_ID"=190 OR "ORGANIZATION_ID"=191 OR "ORGANIZATION_ID"=192 OR "ORGANIZATION_ID"=199 OR "ORGANIZATION_ID"=207 OR "ORGANIZATION_ID"=208 OR "ORGANIZATION_ID"=209 OR "ORGANIZATION_ID"=210 OR "ORGANIZATION_ID"=211 OR "ORGANIZATION_ID"=212 OR "ORGANIZATION_ID"=213 OR "ORGANIZATION_ID"=214 OR "ORGANIZATION_ID"=248 OR "ORGANIZATION_ID"=257 OR "ORGANIZATION_ID"=316 OR "ORGANIZATION_ID"=336 OR "ORGANIZATION_ID"=576 OR "ORGANIZATION_ID"=577 OR "ORGANIZATION_ID"=578 OR "ORGANIZATION_ID"=579 OR "ORGANIZATION_ID"=580 OR "ORGANIZATION_ID"=581 OR "ORGANIZATION_ID"=582 OR "ORGANIZATION_ID"=583 OR "ORGANIZATION_ID"=584 OR "ORGANIZATION_ID"=585 OR "ORGANIZATION_ID"=586 OR "ORGANIZATION_ID"=587 OR "ORGANIZATION_ID "=588 OR "ORGANIZATION_ID"=589 OR "ORGANIZATION_ID"=590 OR "ORGANIZATION_ID"=591 OR "ORGANIZATION_ID"=592 OR "ORGANIZATION_ID"=594 OR "ORGANIZATION_ID"=595 OR "ORGANIZATION_ID"=596 OR "ORGANIZATION_ID"=597 OR "ORGANIZATION_ID"=598 OR "ORGANIZATION_ID"=599 OR "ORGANIZATION_ID"=600 OR "ORGANIZATION_ID"=601 OR "ORGANIZATION_ID"=602 OR "ORGANIZATION_ID"=603 OR "ORGANIZATION_ID"=604 OR "ORGANIZATION_ID"=605 OR "ORGANIZATION_ID"=606 OR "ORGANIZATION_ID"=607 OR "ORGANIZATION_ID"=608 OR "ORGANIZATION_ID"=610 OR "ORGANIZATION_ID"=611 OR "ORGANIZATION_ID"=612 OR "ORGANIZATION_ID"=613 OR "ORGANIZATION_ID"=614 OR "ORGANIZATION_ID"=615 OR "ORGANIZATION_ID"=616 OR "ORGANIZATION_ID"=617 OR "ORGANIZATION_ID"=618 OR " ORGANIZATION_ID"=619 OR "ORGANIZATION_ID"=620 OR "ORGANIZATION_ID"=621 OR "ORGANIZATION_ID"=622 OR "ORGANIZATION_ID"=623 OR "ORGANIZATION_ID"=636 OR "ORGANIZATION_ID"=637 OR "ORGANIZATION_ID"=638 OR "ORGANIZATION_ID"=639 OR "ORGANIZATION_ID"=640 OR "ORGANIZATION_ID"=716 OR "ORGANIZATION_ID"=717 OR "ORGANIZATION_ID"=720 OR "ORGANIZATION_ID"=721 OR "ORGANIZATION_ID"=722 OR "ORGANIZATION_ID"=723 OR "ORGANIZATION_ID"=724 OR "ORGANIZATION_ID"=725 OR "ORGANIZATION_ID"=726 OR "ORGANIZATION_ID"=727 OR "ORGANIZATION_ID"=729 OR "ORGANIZATION_ID"=731 OR "ORGANIZATION_ID"=758 OR "ORGANIZATION_ID"=759 OR "ORGANIZATION_ID"=760 OR "ORGANIZATION_ID"=761 OR "ORGANIZATION_ID"=762 OR "ORGANIZATION_ID"=763 OR "ORGANIZATION_ID "=764 OR "ORGANIZATION_ID"=765 OR "ORGANIZATION_ID"=766 OR "ORGANIZATION_ID"=767 OR "ORGANIZATION_ID"=768 OR "ORGANIZATION_ID"=769 OR "ORGANIZATION_ID"=770 OR "ORGANIZATION_ID"=771 OR "ORGANIZATION_ID"=772 OR "ORGANIZATION_ID"=773 OR "ORGANIZATION_ID"=774 OR "ORGANIZATION_ID"=799 OR "ORGANIZATION_ID"=800 OR "ORGANIZATION_ID"=801 OR "ORGANIZATION_ID"=802 OR "ORGANIZATION_ID"=817 OR "ORGANIZATION_ID"=837 OR "ORGANIZATION_ID"=838 OR "ORGANIZATION_ID"=856 OR "ORGANIZATION_ID"=937 OR "ORGANIZATION_ID"=938 OR "ORGANIZATION_ID"=939 OR "ORGANIZATION_ID"=956 OR "ORGANIZATION_ID"=957 OR "ORGANIZATION_ID"=958 OR "ORGANIZATION_ID"=1316 OR "ORGANIZATION_ID"=1616 OR "ORGANIZATION_ID"=1736 OR "ORGANIZATION_ID"=1756 OR "ORGANIZATION_ID"=1776 OR "ORGANIZATION_ID"=1796 OR "ORGANIZATION_ID"=1816 OR "ORGANIZATION_ID"=2219 OR "ORGANIZATION_ID"=2237 OR "ORGANIZATION_ID"=2238 OR "ORGANIZATION_ID"=2239 OR "ORGANIZATION_ID"=2256 OR "ORGANIZATION_ID"=2276 OR "ORGANIZATION_ID"=2296 OR "ORGANIZATION_ID"=2316 OR "ORGANIZATION_ID"=2336 OR "ORGANIZATION_ID"=2356 OR "ORGANIZATION_ID"=2376 OR "ORGANIZATION_ID"=2396 OR "ORGANIZATION_ID"=2397 OR "ORGANIZATION_ID"=2416 OR "ORGANIZATION_ID"=2436 OR "ORGANIZATION_ID"=2456 OR "ORGANIZATION_ID"=2476 OR "ORGANIZATION_ID"=2496 OR "ORGANIZATION_ID"=2516 OR "ORGANIZATION_ID"=2517 OR "ORGANIZATION_ID"=2537 OR "ORGANIZATION_ID"=2538 OR "ORGANIZATION_ID"=2539 OR "ORGANIZATION_ID"=2540 OR "ORGANIZATION_ID"= 2542 OR "ORGANIZATION_ID"=2556 OR "ORGANIZATION_ID"=2576 OR "ORGANIZATION_ID"=2577 OR "ORGANIZATION_ID"=2596 OR "ORGANIZATION_ID"=2597 OR "ORGANIZATION_ID"=2599 OR "ORGANIZATION_ID"=2600 OR "ORGANIZATION_ID"=2616 OR "ORGANIZATION_ID"=2656 OR "ORGANIZATION_ID"=2657 OR "ORGANIZATION_ID"=2678 OR "ORGANIZATION_ID"=2680 OR "ORGANIZATION_ID"=2696 OR "ORGANIZATION_ID"=2698 OR "ORGANIZATION_ID"=2699 OR "ORGANIZATION_ID"=2701 OR "ORGANIZATION_ID"=2702 OR "ORGANIZATION_ID"=2716 OR "ORGANIZATION_ID"=2816 OR "ORGANIZATION_ID"=2818 OR "ORGANIZATION_ID"=2916 OR "ORGANIZATION_ID"=2936 OR "ORGANIZATION_ID"=3036 OR "ORGANIZATION_ID"=3056 OR "ORGANIZATION_ID"=3076 OR "ORGANIZATION_ID"=3156 OR "ORGANIZATION_ID"=3159 OR "ORGANIZATION_I D"=3376 OR "ORGANIZATION_ID"=3396 OR "ORGANIZATION_ID"=3438 OR "ORGANIZATION_ID"=3457 OR "ORGANIZATION_ID"=3458 OR "ORGANIZATION_ID"=3576 OR "ORGANIZATION_ID"=3596 OR "ORGANIZATION_ID"=3838 OR "ORGANIZATION_ID"=3839 OR "ORGANIZATION_ID"=3840 OR "ORGANIZATION_ID"=3856 OR "ORGANIZATION_ID"=3857 OR "ORGANIZATION_ID"=3876 OR "ORGANIZATION_ID"=3916 OR "ORGANIZATION_ID"=3936 OR "ORGANIZATION_ID"=3937 OR "ORGANIZATION_ID"=3938 OR "ORGANIZATION_ID"=3939 OR "ORGANIZATION_ID"=3940 OR "ORGANIZATION_ID"=3941 OR "ORGANIZATION_ID"=3942 OR "ORGANIZATION_ID"=3943 OR "ORGANIZATION_ID"=4016 OR "ORGANIZATION_ID"=4036 OR "ORGANIZATION_ID"=4056 OR "ORGANIZATION_ID"=4096 OR "ORGANIZATION_ID"=4298 OR "ORGANIZATION_ID"=4336 OR "ORGANI ZATION_ID"=4358 OR "ORGANIZATION_ID"=4376 OR "ORGANIZATION_ID"=4716 OR "ORGANIZATION_ID"=4736 OR "ORGANIZATION_ID"=4756 OR "ORGANIZATION_ID"=4776) AND "RN3">:1


EXPECTED BEHAVIOR
-----------------------
Collection should run without performance issue

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Advanced Supply Chain Planner
2. Collection
3. Oracle System
4. Standard Collection

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