Performance issue with Autoselect (APXPBASL) / APXPBSEL (Doc ID 1253784.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Payables - Version: 11.5.10.2 to 11.5.10.2 - Release: 11.5.10 to 11.5.10
Information in this document applies to any platform.
Performance issue with Autoselect (APXPBASL) / APXPBSEL

Symptoms

APXPBASL(autoselect) - high amount of time spent on event gc_buffer_busy

Following SQL statement is performing poorly:

UPDATE AP_PAYMENT_SCHEDULES SET AP_PAYMENT_SCHEDULES.CHECKRUN_ID=:b1 WHERE AP_PAYMENT_SCHEDULES.CHECKRUN_ID IS NULL AND AP_PAYMENT_SCHEDULES.ROWID IN (SELECT /*+ no_expand */PS.ROWID FROM PO_VENDOR_SITES SITES, AP_INVOICES INV, AP_PAYMENT_SCHEDULES PS WHERE (TRUNC(DUE_DATE) <= TO_DATE(:b2, 'MM/DD/RRRR') OR DECODE(NVL(SITES.PAY_DATE_BASIS_LOOKUP_CODE, 'DISCOUNT'), 'DISCOUNT', DECODE(SITES.ALWAYS_TAKE_DISC_FLAG, 'Y', PS.DISCOUNT_DATE, DECODE(SIGN(TO_DATE(:b3) - NVL(PS.DISCOUNT_DATE, TO_DA TE(:b3) + 1 ) - 1 ), -1, PS.DISCOUNT_DATE, DECODE(SIGN(TO_DATE(:b3) - NVL(PS.SECOND_DISCOUNT_DATE, TO_DATE(:b3) + 1 ) - 1 ), -1, PS.SECOND_DISCOUNT_DATE, DECODE(SIGN(TO_DATE(:b3) - NVL(PS.THIRD_DISCOUNT_DATE, TO_DATE(:b3) + 1 ) - 1 ), -1, PS.THIRD_DISCOUNT_DATE, TRUNC(PS.DUE_DATE))))), TRUNC(DUE_DATE)) BETWEEN DECODE(SITES.ALWAYS_TAKE_DISC_FLAG, 'Y', TO_DATE('1901', 'YYYY'), TO_DATE(:b3)) AND TO_DATE(:b10, 'MM/DD/RRRR') ) AND NVL(INV.PAY_GROUP_LOOKUP_CODE, '%') LIKE NVL(:b11, '%') AND ((:b1 2 IS NOT NULL AND INV.BATCH_ID = :b12 ) OR :b12 IS NULL ) AND NVL(PS.PAYMENT_PRIORITY, 99) BETWEEN :b15 AND :b16 AND PS.PAYMENT_STATUS_FLAG BETWEEN 'N' AND 'P' AND INV.PAYMENT_STATUS_FLAG BETWEEN 'N' AND 'P' AND INV.CANCELLED_DATE IS NULL AND ((:b17 = 'N' AND PS.AMOUNT_REMAINING != 0 ) OR :b17 = 'Y' ) AND NVL(PS.HOLD_FLAG, 'N') = 'N' AND NVL(SITES.HOLD_ALL_PAYMENTS_FLAG, 'N') = 'N' AND INV.SET_OF_BOOKS_ID = :b19 AND INV.INVOICE_ID = PS.INVOICE_ID AND NVL(SITES.VENDOR_ID, -1) = NVL(INV .VENDOR_ID, -1) AND SITES.VENDOR_SITE_ID = INV.VENDOR_SITE_ID AND INV.PAYMENT_CURRENCY_CODE = :b20 AND NVL(PS.PAYMENT_METHOD_LOOKUP_CODE, 'CHECK') = :b21 AND INV.WFAPPROVAL_STATUS IN ( 'NOT REQUIRED', 'WFAPPROVED', 'MANUALLY APPROVED' ) AND NOT EXISTS (SELECT 'Unreleased holds exist' FROM AP_HOLDS H WHERE H.INVOICE_ID = INV.INVOICE_ID AND H.RELEASE_LOOKUP_CODE IS NULL ) AND NOT EXISTS (SELECT 'Invoice is not fully approved' FROM AP_INVOICE_DISTRIBUTIONS D2 WHERE D2.INVOICE_ID = I NV.INVOICE_ID AND NVL(D2.MATCH_STATUS_FLAG, 'N') IN ( 'N', 'S' )) AND EXISTS (SELECT 'Distributions exist' FROM AP_INVOICE_DISTRIBUTIONS D4 WHERE D4.INVOICE_ID = INV.INVOICE_ID ) )

Changes

None

Cause

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