R12:IBY:Performance Issues When trying to Launch Payment Manager (Doc ID 1918712.1)

Last updated on APRIL 04, 2025

Applies to:

Symptoms

When click the Payment Manager link, it takes more than 60 secs to launch.
When launch the Payment Manager to process PPRs, it takes a long time to load the page and errors out on several occasions.

A trace files identified that the following code is taking along time.
 

SELECT COUNT(*)
FROM
IBY_PAY_SERVICE_REQUESTS IBY WHERE IBY.CALLING_APP_ID = 200 AND
 IBY.PROCESS_TYPE = 'STANDARD' AND ( IBY.PAYMENT_SERVICE_REQUEST_STATUS IN (
 'INFORMATION_REQUIRED', 'PENDING_REVIEW_DOC_VAL_ERRORS',
 'PENDING_REVIEW_PMT_VAL_ERRORS', 'PENDING_REVIEW') OR (
 IBY.PAYMENT_SERVICE_REQUEST_STATUS = 'PAYMENTS_CREATED' AND EXISTS ( SELECT
 'NEED_ACTION_BY_ME' FROM IBY_PAYMENTS_ALL PMT, IBY_PAY_INSTRUCTIONS_ALL
 INSTR WHERE IBY.PAYMENT_SERVICE_REQUEST_ID = PMT.PAYMENT_SERVICE_REQUEST_ID
 AND INSTR.PAYMENT_INSTRUCTION_ID = PMT.PAYMENT_INSTRUCTION_ID AND
 (INSTR.PAYMENT_INSTRUCTION_STATUS IN ('CREATION_ERROR',
 'FORMATTED_READY_TO_TRANSMIT', 'TRANSMISSION_FAILED',
 'FORMATTED_READY_FOR_PRINTING', 'SUBMITTED_FOR_PRINTING',
 'CREATED_READY_FOR_PRINTING', 'CREATED_READY_FOR_FORMATTING', 'FORMATTED',
 'CREATED') OR (INSTR.PAYMENT_INSTRUCTION_STATUS = 'TRANSMITTED' AND
 IBY_FD_USER_API_PUB.IS_TRANSMITTED_PMT_INST_COMPL(INSTR.PAYMENT_INSTRUCTION
 _ID) = 'N')) AND IBY_UTILITY_PVT.CHECK_USER_ACCESS(INSTR.PAYMENT_INSTRUCTIO
 N_ID) = 'Y' ) ) )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.05       0.10          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1    256.19     734.06     183184    1659857          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3    256.24     734.17     183184    1659857          0           1


SELECT 'N'
FROM
DUAL WHERE EXISTS ( SELECT 'Inaccessible org' FROM IBY_PAYMENTS_ALL WHERE
 PAYMENT_INSTRUCTION_ID = :B1 AND MO_GLOBAL.CHECK_ACCESS(ORG_ID) = 'N')


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  11944    155.33     632.20     182872     705501          0           0
Fetch    11944      0.06       0.05          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    23889    155.40     632.25     182872     705501          0           0

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.