Performance Issue With Placing Orders From APS Planning System to EBS source system
(Doc ID 1674804.1)
Last updated on FEBRUARY 26, 2019
Applies to:Oracle Advanced Supply Chain Planning - Version 12.1.3 and later
Information in this document applies to any platform.
VCP 22.214.171.124 Patch 16974659
After applying 126.96.36.199 on EBS and APS (was on base 12.1.3 release)
Plus many patches on EBS source - we find that release new purchase req is taking two mins.
Used to take just seconds.
Below query is the culprit. It is running in R12 EBS
PO RUP patch has modified the PO_VENDOR_SITES_ALL view which is restricting it from merging to PO_REQUISITIONS_INTERFACE_ALL or pushing the predicate and doing full table scans on AP_SUPPLIER_SITES_ALL & HZ_PARTY_SITES tables.
I tried to force some hints but the OUTER JOINS are not allowing it(seems like a restriction).
If this is a custom code we could have modified it as shown below(which is completing in But since it is a seeded form, please open SR and work with ORACLE.
FROM PO_VENDOR_SITES_ALL PVSA, PO_REQUISITIONS_INTERFACE_ALL PRI
WHERE PRI.SUGGESTED_VENDOR_ID = PVSA.VENDOR_ID(+)
AND PRI.SUGGESTED_VENDOR_SITE = PVSA.VENDOR_SITE_CODE(+)
AND NVL (PRI.ORG_ID, -99) = NVL (PVSA.ORG_ID(+), -99)
AND PRI.INTERFACE_SOURCE_CODE = 'MSC'
AND PRI.BATCH_ID = :B1;
It will take just a few seconds for the screen to return showing results screen with Req Import request ID and number of reqs released.
Rolled out in production and now it takes unacceptable time to complete.
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