Performance Issue With Placing Orders From APS Planning System to EBS source system
Last updated on JUNE 28, 2017
Applies to:Oracle Advanced Supply Chain Planning - Version 12.1.3 and later
Information in this document applies to any platform.
VCP 18.104.22.168 Patch 16974659
After applying 22.214.171.124 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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms