Collection Performance Issue Post Patch 21675556:R12.MSC.C (Doc ID 2087273.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Process Manufacturing Process Planning - Version 12.2.4 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.4 version, APS Collections

ACTUAL BEHAVIOR
---------------
There is a performance issue in the collection routine. This is reported as being within Planning Data Pull Worker and in particular the collection of Resource Availability. This is reported following the application of patch 21675556:R12.MSC.C but it is also the case that the instance where this is now being run has 7 organizations whereas the instance where this patch was tested had 1 organization

Customer had reported that after launching the collections program it spawned several child processes and they are blocking each other. Per a blocking query they see that both session 610 and 505 are blocking each other and they both are running the same DELETE Statement.

select sql_id from V$session where sid=610;

7kq962d7skpdq

select sql_id from V$session where sid=505;

7kq962d7skpdq

select sql_fulltext from v$sql where sql_id='7kq962d7skpdq';

DELETE FROM MSC_ST_NET_RESOURCE_AVAIL A WHERE A.ROWID > ANY (SELECT B.ROWID FROM MSC_ST_NET_RESOURCE_AVAIL B WHERE A.ORGANIZATION_ID = B.ORGANIZATION_ID AND A.DEPARTMENT_ID = B.DEPARTMENT_ID AND A.SR_INSTANCE_ID = B.SR_INSTANCE_ID AND A.RESOURCE_ID = B.RESOURCE_ID AND A.CAPACITY_UNITS = B.CAPACITY_UNITS AND NVL(A.SIMULATION_SET,0) = NVL(B.SIMULATION_SET,0) AND A.FROM_TIME = B.FROM_TIME AND A.TO_TIME = B.TO_TIME AND A.SHIFT_DATE = B.SHIFT_DATE AND A.SHIFT_NUM = B.SHIFT_NUM AND A.SR_INSTANCE_ID = :B1 )

Rajesh did an OWC with the customer in which they modified the delete statement. The customer also ran Gather Statistics for msc_st_net_resource_avail. This resolved the performance problem. They were then asked to try running the standard collection code, and they report that the SQL statemetn has been running for the past 11 hours.

EXPECTED BEHAVIOR
-----------------------
A reasonable collection performance (currently undefined)

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Apply patch 21675556:R12.MSC.C
2. Run the Standard Collection, complete refresh

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot complete collections

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