After applying Patch 32293979, Performance Problem is seen when Running Generate Accruals Master - Streams program Wide Open
(Doc ID 2749942.1)
Last updated on AUGUST 16, 2021
Applies to:
Oracle Lease and Finance Management - Version 12.2.5 and laterInformation in this document applies to any platform.
Symptoms
On : 12.2.5 version, Generate Accruals,
ACTUAL BEHAVIOR
---------------
Find that after applying patch 32293979, performance problem is seen when running the Generate Accruals Master - Streams program wide open.
SQL with performance issue:
SELECT MIN(DUE_DATE) MIN_DUE_DATE, COUNT(CUSTOMER_TRX_ID)TOTAL_OS
FROM
( SELECT PS.CUSTOMER_TRX_ID, PS.DUE_DATE DUE_DATE FROM
AR_PAYMENT_SCHEDULES_ALL PS, OKL_CNSLD_AR_STRMS_B ST , OKL_CNSLD_AR_LINES_B
LN , OKL_CNSLD_AR_HDRS_B HD , OKL_STRM_TYPE_B SP , OKC_K_HEADERS_ALL_B CN
WHERE PS.CLASS IN ('INV') AND ST.RECEIVABLES_INVOICE_ID =
PS.CUSTOMER_TRX_ID AND LN.ID = ST.LLN_ID AND HD.ID = LN.CNR_ID AND SP.ID =
ST.STY_ID AND CN.ID = ST.KHR_ID AND ST.KHR_ID = :B1 AND SP.ACCRUAL_YN NOT
IN('CASH_RECEIPT', 'CASH_WITHOUT_CONTINGENCY') AND STATUS = 'OP' AND
CN.CUST_ACCT_ID = PS.CUSTOMER_ID AND PS.DUE_DATE <= SYSDATE AND EXISTS
(SELECT 1 FROM OKL_PROD_STRM_TYPES WHERE SP.ID = STY_ID AND PDT_ID =
(SELECT PDT_ID FROM OKL_K_HEADERS_FULL_V WHERE ID = :B1 ) AND EVALUATE_YN =
'Y') UNION SELECT /*+ index(TXD OKL_TXD_AR_LN_DTLS_B_U1) */
RACTRX.CUSTOMER_TRX_ID, APS.DUE_DATE DUE_DATE FROM
RA_CUSTOMER_TRX_LINES_ALL RACTRL, OKL_TXD_AR_LN_DTLS_B TXD ,
RA_CUSTOMER_TRX_ALL RACTRX , OKL_STRM_TYPE_B SM , OKL_TXL_AR_INV_LNS_B TIL ,
AR_PAYMENT_SCHEDULES_ALL APS , OKC_K_HEADERS_ALL_B KHR WHERE
TO_CHAR(TXD.ID) = RACTRL.INTERFACE_LINE_ATTRIBUTE14 AND
RACTRL.CUSTOMER_TRX_ID = RACTRX.CUSTOMER_TRX_ID AND SM.ID = TXD.STY_ID AND
TXD.TIL_ID_DETAILS = TIL.ID AND RACTRX.CUSTOMER_TRX_ID =
APS.CUSTOMER_TRX_ID AND APS.CLASS IN ('INV') AND TXD.KHR_ID = :B1 AND
SM.ACCRUAL_YN NOT IN('CASH_RECEIPT', 'CASH_WITHOUT_CONTINGENCY') AND STATUS
= 'OP' AND KHR.ID = TXD.KHR_ID AND KHR.CUST_ACCT_ID = APS.CUSTOMER_ID AND
APS.DUE_DATE <= SYSDATE AND EXISTS (SELECT 1 FROM OKL_PROD_STRM_TYPES WHERE
SM.ID = STY_ID AND PDT_ID = (SELECT PDT_ID FROM OKL_K_HEADERS_FULL_V WHERE
ID = :B1 ) AND EVALUATE_YN = 'Y') )
EXPECTED BEHAVIOR
-----------------------
Expect the process to complete in short amount of time.
STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run Generate Accruals Master - Streams wide open.
Changes
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 |