Performance Issue with Aging - 7 Buckets - By Account - Multi-Fund Accounts Receivable

(Doc ID 2339616.1)

Last updated on DECEMBER 18, 2017

Applies to:

Oracle Receivables - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Oracle Receivables Performance Issues


 Performance issue with Aging - 7 Buckets - By Account - Multi-Fund Accounts Receivable.

 The request is taking 10 hours to get completed.

 Tkprof shows following query taking more time.

SELECT /*+leading(age_ps) */ :B3 , PAYMENT_SCHEDULE_ID, CODE_COMBINATION_ID,
CURRENCY_CODE, REC_AMOUNT, 0 REC_AGING_AMOUNT, SUM(REC_AMOUNT) OVER
(PARTITION BY PAYMENT_SCHEDULE_ID) RECEIVABLE_TOTAL, AMT_DUE_REMAINING, :B5
CATEGORY
FROM
(SELECT CODE_COMBINATION_ID, SUM(REC_AMOUNT) REC_AMOUNT, PAYMENT_SCHEDULE_ID,
AMT_DUE_REMAINING, CURRENCY_CODE FROM ( (SELECT /*+ leading(age_ps)
index(XDL XLA_DISTRIBUTION_LINKS_N31) */ AE.CODE_COMBINATION_ID,
SUM(NVL(XDL.UNROUNDED_ENTERED_DR,0)-NVL(XDL.UNROUNDED_ENTERED_CR,0))
REC_AMOUNT, AGE_PS.PAYMENT_SCHEDULE_ID, AGE_PS.AMT_DUE_REMAINING,
AE.CURRENCY_CODE FROM XLA_AE_HEADERS HDR, XLA_AE_LINES AE,
XLA_DISTRIBUTION_LINKS XDL, ( SELECT /*+ leading(ext) index(ra
AR_RECEIVABLE_APPLICATIONS_N3)*/ RA.EVENT_ID, EXT.PAYMENT_SCHEDULE_ID,
EXT.AMT_DUE_REMAINING, ARD.LINE_ID DIST_ID, 'RA_APPLIED_FROM'
SOURCE_IDENTIFIER FROM AR_AGING_EXTRACT EXT, AR_RECEIVABLE_APPLICATIONS RA,
AR_DISTRIBUTIONS ARD WHERE EXT.PARENT_REQUEST_ID = :B3 AND EXT.WORKER_ID =
:B2 AND EXT.PAYMENT_SCHEDULE_ID = RA.PAYMENT_SCHEDULE_ID AND RA.GL_DATE <=
:B1 AND RA.STATUS IN ('APP','ACTIVITY') AND NVL(ARD.SOURCE_TABLE_SECONDARY,
'NULL') NOT IN ('UPMFRAREAR','UPMFCRREAR','UPMFCHMIAR') AND
ARD.REF_CUSTOMER_TRX_LINE_ID IS NOT NULL AND ARD.SOURCE_TABLE = 'RA' AND
ARD.SOURCE_ID = RA.RECEIVABLE_APPLICATION_ID AND RA.EVENT_ID IS NOT NULL
GROUP BY RA.EVENT_ID, EXT.PAYMENT_SCHEDULE_ID, EXT.AMT_DUE_REMAINING,
ARD.LINE_ID UNION ALL SELECT /*+ leading(ext) index(ra
AR_RECEIVABLE_APPLICATIONS_N8)*/ XE.EVENT_ID, EXT.PAYMENT_SCHEDULE_ID,
EXT.AMT_DUE_REMAINING, ARD.LINE_ID DIST_ID, 'RA_APPLIED_TO'
SOURCE_IDENTIFIER FROM AR_AGING_EXTRACT EXT, XLA_EVENTS XE,
AR_RECEIVABLE_APPLICATIONS RA, AR_DISTRIBUTIONS ARD_REV, AR_DISTRIBUTIONS
ARD WHERE EXT.PARENT_REQUEST_ID = :B3 AND EXT.WORKER_ID = :B2 AND
EXT.PAYMENT_SCHEDULE_ID = RA.APPLIED_PAYMENT_SCHEDULE_ID AND RA.GL_DATE <=
:B1 AND RA.STATUS IN ('APP','ACTIVITY') AND RA.EVENT_ID IS NOT NULL AND
RA.EVENT_ID = XE.EVENT_ID AND NVL(ARD.SOURCE_TABLE_SECONDARY,'NULL') NOT IN
('UPMFRAREAR','UPMFCRREAR','UPMFCHMIAR') AND NVL(ARD.SOURCE_TABLE_SECONDARY,
'NULL') = NVL(ARD_REV.SOURCE_TABLE_SECONDARY,'NULL') AND
ARD.REF_CUSTOMER_TRX_LINE_ID IS NOT NULL AND ARD.REF_CUSTOMER_TRX_LINE_ID =
ARD_REV.REF_CUSTOMER_TRX_LINE_ID AND ARD_REV.SOURCE_TABLE = 'RA' AND
XE.APPLICATION_ID = 222 AND ARD.SOURCE_TABLE = 'RA' AND ARD_REV.SOURCE_ID =
RA.RECEIVABLE_APPLICATION_ID AND ARD.SOURCE_ID = DECODE(XE.EVENT_TYPE_CODE,
'RECP_REVERSE',ARD_REV.REVERSED_SOURCE_ID,RA.RECEIVABLE_APPLICATION_ID) AND
(ARD.REF_CUSTOMER_TRX_LINE_ID IS NULL OR ARD.REF_CUSTOMER_TRX_LINE_ID IN
(SELECT CUSTOMER_TRX_LINE_ID FROM RA_CUSTOMER_TRX_LINES WHERE
CUSTOMER_TRX_ID = RA.APPLIED_CUSTOMER_TRX_ID) ) GROUP BY XE.EVENT_ID,
EXT.PAYMENT_SCHEDULE_ID, EXT.AMT_DUE_REMAINING, ARD.LINE_ID UNION ALL
SELECT /*+ leading(ext) index(adj AR_ADJUSTMENTS_N3)*/ ADJ.EVENT_ID,
EXT.PAYMENT_SCHEDULE_ID, EXT.AMT_DUE_REMAINING, ARD.LINE_ID DIST_ID, 'ADJ'
SOURCE_IDENTIFIER FROM AR_AGING_EXTRACT EXT, AR_ADJUSTMENTS ADJ,
AR_DISTRIBUTIONS ARD WHERE EXT.PARENT_REQUEST_ID = :B3 AND EXT.WORKER_ID =
:B2 AND EXT.PAYMENT_SCHEDULE_ID = ADJ.PAYMENT_SCHEDULE_ID AND ADJ.GL_DATE <=
:B1 AND NVL(POSTABLE,'Y') = 'Y' AND NVL(ARD.SOURCE_TABLE_SECONDARY,'NULL')
<> 'UPMFAJREAR' AND ARD.SOURCE_TABLE = 'ADJ' AND ARD.SOURCE_ID =
ADJ.ADJUSTMENT_ID AND ADJ.EVENT_ID IS NOT NULL GROUP BY ADJ.EVENT_ID,
EXT.PAYMENT_SCHEDULE_ID, EXT.AMT_DUE_REMAINING, ARD.LINE_ID UNION ALL
SELECT /*+ leading(ext) index(ctlgd RA_CUST_TRX_LINE_GL_DIST_N6)*/
CTLGD.EVENT_ID, EXT.PAYMENT_SCHEDULE_ID, EXT.AMT_DUE_REMAINING,
CTLGD.CUST_TRX_LINE_GL_DIST_ID DIST_ID, 'CTLGD' SOURCE_IDENTIFIER FROM
AR_AGING_EXTRACT EXT, AR_PAYMENT_SCHEDULES PS, RA_CUST_TRX_LINE_GL_DIST
CTLGD WHERE EXT.PARENT_REQUEST_ID = :B3 AND EXT.WORKER_ID = :B2 AND
EXT.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID AND PS.CUSTOMER_TRX_ID =
CTLGD.CUSTOMER_TRX_ID AND CTLGD.GL_DATE <= :B1 AND CTLGD.EVENT_ID IS NOT
NULL GROUP BY CTLGD.EVENT_ID, EXT.PAYMENT_SCHEDULE_ID,
EXT.AMT_DUE_REMAINING, CTLGD.CUST_TRX_LINE_GL_DIST_ID ) AGE_PS WHERE
HDR.APPLICATION_ID = 222 AND AE.APPLICATION_ID = 222 AND XDL.APPLICATION_ID
= 222 AND HDR.LEDGER_ID = :B4 AND AE.AE_HEADER_ID = HDR.AE_HEADER_ID AND
AE.ACCOUNTING_CLASS_CODE = 'RECEIVABLE' AND
HDR.ACCOUNTING_ENTRY_STATUS_CODE = 'F' AND HDR.EVENT_ID = AGE_PS.EVENT_ID
AND XDL.AE_HEADER_ID = HDR.AE_HEADER_ID AND XDL.EVENT_ID = HDR.EVENT_ID AND
XDL.AE_LINE_NUM = AE.AE_LINE_NUM AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =
AGE_PS.DIST_ID AND ( AGE_PS.SOURCE_IDENTIFIER <> 'RA_APPLIED_TO' OR
XDL.SOURCE_DISTRIBUTION_TYPE = 'AR_DISTRIBUTIONS_ALL') GROUP BY
AE.CODE_COMBINATION_ID, AGE_PS.PAYMENT_SCHEDULE_ID,
AGE_PS.AMT_DUE_REMAINING, AE.CURRENCY_CODE) UNION ALL (SELECT /*+
leading(age_ps)*/ AE.CODE_COMBINATION_ID, SUM(NVL(XDL.UNROUNDED_ENTERED_DR,
0)-NVL(XDL.UNROUNDED_ENTERED_CR,0)) REC_AMOUNT, AGE_PS.PAYMENT_SCHEDULE_ID,
AGE_PS.AMT_DUE_REMAINING, AE.CURRENCY_CODE FROM XLA_AE_HEADERS HDR,
XLA_AE_LINES AE, XLA_DISTRIBUTION_LINKS XDL, ( SELECT /*+ leading(ext)
index(ra AR_RECEIVABLE_APPLICATIONS_N3)*/ NULL EVENT_ID,
EXT.PAYMENT_SCHEDULE_ID, EXT.AMT_DUE_REMAINING, ARD.LINE_ID DIST_ID,
'RA_APPLIED_FROM' SOURCE_IDENTIFIER FROM AR_AGING_EXTRACT EXT,
AR_RECEIVABLE_APPLICATIONS RA, AR_DISTRIBUTIONS ARD WHERE
EXT.PARENT_REQUEST_ID = :B3 AND EXT.WORKER_ID = :B2 AND
EXT.PAYMENT_SCHEDULE_ID = RA.PAYMENT_SCHEDULE_ID AND RA.GL_DATE <= :B1 AND
RA.STATUS IN ('APP','ACTIVITY') AND ARD.SOURCE_TABLE_SECONDARY =
'UPMFRAMIAR' AND ARD.SOURCE_TABLE = 'RA' AND ARD.SOURCE_ID =
RA.RECEIVABLE_APPLICATION_ID AND RA.EVENT_ID IS NULL GROUP BY
EXT.PAYMENT_SCHEDULE_ID, EXT.AMT_DUE_REMAINING, ARD.LINE_ID UNION ALL
SELECT /*+ leading(ext) index(ra AR_RECEIVABLE_APPLICATIONS_N8)*/ NULL
EVENT_ID, EXT.PAYMENT_SCHEDULE_ID, EXT.AMT_DUE_REMAINING, ARD.LINE_ID
DIST_ID, 'RA_APPLIED_TO' SOURCE_IDENTIFIER FROM AR_AGING_EXTRACT EXT,
AR_RECEIVABLE_APPLICATIONS RA, AR_DISTRIBUTIONS ARD WHERE
EXT.PARENT_REQUEST_ID = :B3 AND EXT.WORKER_ID = :B2 AND RA.EVENT_ID IS NULL
AND EXT.PAYMENT_SCHEDULE_ID = RA.APPLIED_PAYMENT_SCHEDULE_ID AND
ARD.SOURCE_TABLE_SECONDARY = 'UPMFRAMIAR' AND RA.GL_DATE <= :B1 AND
RA.STATUS IN ('APP','ACTIVITY') AND ARD.SOURCE_TABLE = 'RA' AND
ARD.SOURCE_ID = RA.RECEIVABLE_APPLICATION_ID AND
(ARD.REF_CUSTOMER_TRX_LINE_ID IS NULL OR ARD.REF_CUSTOMER_TRX_LINE_ID IN
(SELECT CUSTOMER_TRX_LINE_ID FROM RA_CUSTOMER_TRX_LINES WHERE
CUSTOMER_TRX_ID = RA.APPLIED_CUSTOMER_TRX_ID) ) GROUP BY
EXT.PAYMENT_SCHEDULE_ID, EXT.AMT_DUE_REMAINING, ARD.LINE_ID UNION ALL
SELECT /*+ leading(ext) index(adj AR_ADJUSTMENTS_N3)*/ NULL EVENT_ID,
EXT.PAYMENT_SCHEDULE_ID, EXT.AMT_DUE_REMAINING, ARD.LINE_ID DIST_ID, 'ADJ'
SOURCE_IDENTIFIER FROM AR_AGING_EXTRACT EXT, AR_ADJUSTMENTS ADJ,
AR_DISTRIBUTIONS ARD WHERE EXT.PARENT_REQUEST_ID = :B3 AND EXT.WORKER_ID =
:B2 AND EXT.PAYMENT_SCHEDULE_ID = ADJ.PAYMENT_SCHEDULE_ID AND
NVL(ARD.SOURCE_TABLE_SECONDARY,'NULL') = 'UPMFAJMIAR' AND ADJ.GL_DATE <=
:B1 AND NVL(POSTABLE,'Y') = 'Y' AND ARD.SOURCE_TABLE = 'ADJ' AND
ARD.SOURCE_ID = ADJ.ADJUSTMENT_ID AND ADJ.EVENT_ID IS NULL GROUP BY
EXT.PAYMENT_SCHEDULE_ID, EXT.AMT_DUE_REMAINING, ARD.LINE_ID UNION ALL
SELECT /*+ leading(ext) index(ctlgd RA_CUST_TRX_LINE_GL_DIST_N6)*/ NULL
EVENT_ID, EXT.PAYMENT_SCHEDULE_ID, EXT.AMT_DUE_REMAINING,
CTLGD.CUST_TRX_LINE_GL_DIST_ID DIST_ID, 'CTLGD' SOURCE_IDENTIFIER FROM
AR_AGING_EXTRACT EXT, AR_PAYMENT_SCHEDULES PS, RA_CUST_TRX_LINE_GL_DIST
CTLGD WHERE EXT.PARENT_REQUEST_ID = :B3 AND EXT.WORKER_ID = :B2 AND
EXT.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID AND PS.CUSTOMER_TRX_ID =
CTLGD.CUSTOMER_TRX_ID AND CTLGD.GL_DATE <= :B1 AND CTLGD.EVENT_ID IS NULL
GROUP BY EXT.PAYMENT_SCHEDULE_ID, EXT.AMT_DUE_REMAINING,
CTLGD.CUST_TRX_LINE_GL_DIST_ID ) AGE_PS WHERE HDR.APPLICATION_ID = 222 AND
AE.APPLICATION_ID = 222 AND XDL.APPLICATION_ID = 222 AND HDR.LEDGER_ID =
:B4 AND AE.AE_HEADER_ID = HDR.AE_HEADER_ID AND AE.ACCOUNTING_CLASS_CODE =
'RECEIVABLE' AND HDR.ACCOUNTING_ENTRY_STATUS_CODE = 'F' AND
XDL.AE_HEADER_ID = HDR.AE_HEADER_ID AND XDL.AE_LINE_NUM = AE.AE_LINE_NUM
AND XDL.ACCOUNTING_LINE_CODE <> 'TC_UPGRADE' AND
XDL.SOURCE_DISTRIBUTION_TYPE = DECODE (AGE_PS.SOURCE_IDENTIFIER,'CTLGD',
'RA_CUST_TRX_LINE_GL_DIST_ALL','AR_DISTRIBUTIONS_ALL') AND
XDL.SOURCE_DISTRIBUTION_TYPE IN ('AR_DISTRIBUTIONS_ALL',
'RA_CUST_TRX_LINE_GL_DIST_ALL') AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =
AGE_PS.DIST_ID AND ( AGE_PS.SOURCE_IDENTIFIER <> 'RA_APPLIED_TO' OR
XDL.SOURCE_DISTRIBUTION_TYPE = 'AR_DISTRIBUTIONS_ALL') GROUP BY
AE.CODE_COMBINATION_ID, AGE_PS.PAYMENT_SCHEDULE_ID,
AGE_PS.AMT_DUE_REMAINING, AE.CURRENCY_CODE )) GROUP BY CODE_COMBINATION_ID,
PAYMENT_SCHEDULE_ID, AMT_DUE_REMAINING, CURRENCY_CODE ORDER BY
PAYMENT_SCHEDULE_ID )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 1643.91 1810.61 205010 240347458 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 1643.91 1810.61 205010 240347458 0 0

SQLT shows shows following execution plan.

Plan Hash Value Source Plan Info

1157100906 [B] GV$SQL_PLAN
sql_profile "coe_4u635dhkhy599_3767888316"

The access/filter predicates for the above operations are these:

Exec Ord Operation More
65 ....+....+ TABLE ACCESS BY LOCAL INDEX ROWID XLA_DISTRIBUTION_LINKS [-]
Filter Predicates
(XDL.EVENT_ID=HDR.EVENT_ID AND
XDL.SOURCE_DISTRIBUTION_ID_NUM_1=AGE_PS.DIST_ID
AND (AGE_PS.SOURCE_IDENTIFIER<>'RA_APPLIED_TO' OR
XDL.SOURCE_DISTRIBUTION_TYPE='AR_DISTRIBUTIONS_ALL'))
Projection
XDL.AE_LINE_NUM, XDL.UNROUNDED_ENTERED_DR, XDL.UNROUNDED_ENTERED_CR
Partition ID [Start] [Stop]
74 [2] [2]
64 ....+....+. INDEX RANGE SCAN XLA_DISTRIBUTION_LINKS_N3 [-]
Search Columns: 1/2
AE_HEADER_ID
AE_LINE_NUM
Access Predicates
XDL.AE_HEADER_ID=HDR.AE_HEADER_ID
Projection
XDL.ROWID, XDL.AE_LINE_NUM
Partition ID [Start] [Stop]
75 [2] [2]

Customer file version

ARAGBKTB.pls 120.0.12010000.39

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms