Performance Issue With Placing Orders From APS Planning System to EBS source system
(Doc ID 1674804.1)
Last updated on JUNE 28, 2017
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
PROBLEM ------------- 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.
Original Query: SELECT PVSA.VENDOR_SITE_ID, PRI.ROWID, PRI.ITEM_ID, PRI.DESTINATION_ORGANIZATION_ID, PRI.CHARGE_ACCOUNT_ID, PRI.PROJECT_ID 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;
EXPECTED BEHAVIOR ---------------------------- 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.
BUSINESS IMPACT ---------------------- 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!