Long Running Payment Manager - R12 Payables
We have narrowed the slowness (25 - 30 minutes) down to the Record Print Status process, and the following query which is in IBY_DISBURSE_UI_API_PUB_PKG body on line 9417:
UPDATE IBY_PAYMENTS_ALL PMT
SET PMT.COMPLETED_PMTS_GROUP_ID = :B3
WHERE PMT.PAYMENT_INSTRUCTION_ID = :B2
AND PMT.PAYMENTS_COMPLETE_FLAG = 'Y'
AND PAYMENT_STATUS = :B1
AND PMT.PAYMENT_ID IN
(SELECT DOC.PAYMENT_ID FROM IBY_DOCS_PAYABLE_ALL DOC
WHERE DOC.PAYMENT_ID = PMT.PAYMENT_ID AND DOC.CALLING_APP_ID = :B4 )
adding this hint to the sub query speeds it up by ~40 times.
/*+ index(DOC IBY_DOCS_PAYABLE_ALL_N6) */
UPDATE IBY_PAYMENTS_ALL PMT
SET PMT.COMPLETED_PMTS_GROUP_ID = :B3
WHERE PMT.PAYMENT_INSTRUCTION_ID = :B2
AND PMT.PAYMENTS_COMPLETE_FLAG = 'Y'
AND PAYMENT_STATUS = :B1
AND PMT.PAYMENT_ID IN
(SELECT DOC.PAYMENT_ID FROM IBY_DOCS_PAYABLE_ALL DOC
WHERE DOC.PAYMENT_ID = PMT.PAYMENT_ID AND DOC.CALLING_APP_ID = :B4 )
adding this hint to the sub query speeds it up by ~40 times.
/*+ index(DOC IBY_DOCS_PAYABLE_ALL_N6) */
Tagged:
0