After applying Patch 32293979, Performance Problem is seen when Running Generate Accruals Master - Streams program Wide Open (Doc ID 2749942.1)

Last updated on MARCH 12, 2021

Applies to:

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

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