Collection Performance Issue Post Patch 21675556:R12.MSC.C
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.
On : 12.2.4 version, APS Collections
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;
select sql_id from V$session where sid=505;
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.
A reasonable collection performance (currently undefined)
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
The issue has the following business impact:
Due to this issue, users cannot complete collections
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