My Oracle Support Banner

The 'Generate Accruals Master - Streams' Program has a Performance Issue after Applying the SEP 2019 UP (Doc ID 2699850.1)

Last updated on AUGUST 27, 2021

Applies to:

Oracle Lease and Finance Management - Version 12.2 and later
Information in this document applies to any platform.

Symptoms

Find that there is a performance problem when running the "Generate Accruals Master - Streams" program after applying the Sep 2019 Unified Patch. It takes anywhere between 10 to 17 hours.

The worst performing sql is:

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 UNION SELECT
  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
  RACTRL.INTERFACE_LINE_ATTRIBUTE14 = (SELECT TO_CHAR(TXD.ID) FROM
  OKL_TXD_AR_LN_DTLS_B A WHERE A.ID = TXD.ID) 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 then

SELECT STY.ID, STYTL.NAME, STM.ID STREAM_ID, STE.ID STREAM_ELEMENT_ID,
  STE.AMOUNT, STM.KLE_ID
FROM
 OKL_STRM_TYPE_B STY, OKL_STRM_TYPE_TL STYTL, OKL_STREAMS_REP_V STM,
  OKL_STRM_ELEMENTS STE, OKL_PROD_STRM_TYPES PSTY, OKL_K_HEADERS KHR WHERE
  STM.KHR_ID = :B5 AND KHR.ID = STM.KHR_ID AND STM.ACTIVE_YN = 'Y' AND
  STM.SAY_CODE = 'CURR' AND (STM.PURPOSE_CODE IS NULL OR STM.PURPOSE_CODE=
  'REPORT') AND STM.STY_ID = STY.ID AND STY.ID = STYTL.ID AND STYTL.LANGUAGE =
  USERENV('LANG') AND STY.ACCRUAL_YN = :B4 AND STY.ID = PSTY.STY_ID AND
  PSTY.PDT_ID = :B3 AND PSTY.ACCRUAL_YN = 'Y' AND STM.ID = STE.STM_ID AND
  TRUNC(STE.STREAM_ELEMENT_DATE) BETWEEN TRUNC(:B2 ) AND TRUNC(:B1 ) AND
  STE.AMOUNT <> 0 AND STE.ACCRUED_YN IS NULL


STEPS
1. Run Generate Accruals Master - Streams wide open.

Changes

Applied the Sep 2019 Unified Patch.

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


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