Last updated on JUNE 22, 2016

Applies to:

Symptoms

ARLPLB module: Process Lockboxes is having performance issues

The following query is the one that is performing poor:

update ar_payments_interface pi set status='AR_PLB_DUP_PMT'
where
(((transmission_id=:b0 and record_type=:b1) and status='AR_PLB_GL_DATE_OK')
and ((exists (select 'duplicate interim receipt' from
ar_interim_cash_receipts ir ,fnd_currencies fc1 where ((((ir.receipt_number=
pi.check_number and ir.amount=decode(:b2,'Y',
round((pi.remittance_amount/power(10,fc1.precision)),fc1.precision),
pi.remittance_amount)) and nvl(ir.pay_from_customer,(-999))=
nvl(pi.customer_id,(-999))) and ir.receipt_date=pi.receipt_date) and
fc1.currency_code=pi.currency_code)) or exists (select 'duplicate interface
receipt' from ar_payments_interface pi2 where (((((pi2.check_number=
pi.check_number and pi2.remittance_amount=pi.remittance_amount) and
nvl(pi2.customer_id,(-999))=nvl(pi.customer_id,(-999))) and
pi2.receipt_date=pi.receipt_date) and pi2.transmission_id=:b0) and
pi2.transmission_record_id<>pi.transmission_record_id))) or exists (select
'duplicate final receipt' from ar_cash_receipts cr ,fnd_currencies fc2 ,
ar_cash_receipt_history crh where (((((((cr.receipt_number=pi.check_number
and crh.cash_receipt_id=cr.cash_receipt_id) and
crh.first_posted_record_flag='Y') and crh.amount=decode(:b2,'Y',
round((pi.remittance_amount/power(10,fc2.precision)),fc2.precision),
pi.remittance_amount)) and nvl(cr.pay_from_customer,(-999))=
nvl(pi.customer_id,(-999))) and cr.receipt_date=pi.receipt_date) and
cr.status<>'REV') and cr.currency_code=fc2.currency_code))))

Review of TKPROF shows:

call    count  cpu      elapsed    disk       query      current    rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse   1      0.07     0.09       4          12         0          0
Execute 1      1098.03  1375.78    6179425    6461645    0          0
Fetch   0      0.00     0.00       0          0          0          0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total   2      1098.10  1375.87    6179429    6461657    0          0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 469 (APPS)

Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE AR_PAYMENTS_INTERFACE_ALL (cr=6461621 pr=6179419 pw=0 time=1375698460 us)
0 FILTER (cr=6461621 pr=6179419 pw=0 time=1375698197 us)
1839 TABLE ACCESS BY INDEX ROWID AR_PAYMENTS_INTERFACE_ALL (cr=595 pr=237 pw=0 time=358543 us)
1839 INDEX RANGE SCAN AR_PAYMENTS_INTERFACE_N6 (cr=12 pr=3 pw=0 time=29472 us)(object id 547841)
0 NESTED LOOPS (cr=7847 pr=453 pw=0 time=1002909 us)
1839 TABLE ACCESS BY INDEX ROWID FND_CURRENCIES (cr=3678 pr=0 pw=0 time=122094 us)
1839 INDEX UNIQUE SCAN FND_CURRENCIES_U1 (cr=1839 pr=0 pw=0 time=59177 us)(object id 436679)
0 TABLE ACCESS BY INDEX ROWID AR_INTERIM_CASH_RECEIPTS_ALL (cr=4169 pr=453 pw=0 time=869041 us)
0 INDEX RANGE SCAN AR_INTERIM_CASH_RECEIPTS_N1 (cr=4169 pr=453 pw=0 time=858629 us)(object id 533447)
0 TABLE ACCESS BY INDEX ROWID AR_PAYMENTS_INTERFACE_ALL (cr=6783 pr=2396 pw=0 time=2400360 us)
1850 INDEX RANGE SCAN AR_PAYMENTS_INTERFACE_N3 (cr=4689 pr=2385 pw=0 time=2254672 us)(object id 533522)
0 TABLE ACCESS BY INDEX ROWID AR_CASH_RECEIPT_HISTORY_ALL (cr=6446396 pr=6176333 pw=0 time=1371796948 us)
1839 NESTED LOOPS (cr=6446396 pr=6176333 pw=0 time=1371763941 us)
0 NESTED LOOPS (cr=6446396 pr=6176333 pw=0 time=1371746187 us)
0 TABLE ACCESS BY INDEX ROWID AR_CASH_RECEIPTS_ALL (cr=6446396 pr=6176333 pw=0 time=1371737649 us)
12106777 INDEX RANGE SCAN AR_CASH_RECEIPTS_N5 (cr=67146 pr=63291 pw=0 time=59994918 us)(object id 533355)
0 TABLE ACCESS BY INDEX ROWID FND_CURRENCIES (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN FND_CURRENCIES_U1 (cr=0 pr=0 pw=0 time=0 us)(object id 436679)
0 INDEX RANGE SCAN AR_CASH_RECEIPT_HISTORY_N1 (cr=0 pr=0 pw=0 time=0 us)(object id 533364)

Cause