My Oracle Support Banner

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.