My Oracle Support Banner

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

Last updated on JANUARY 16, 2018

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

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
  Changes
  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.