Lockbox Performance Issue: Poor Performance of Lockbox, Poorly Performing Statement Uses Index AR_PAYMENT_SCHEDULES_N6 (Doc ID 342925.1)

Last updated on AUGUST 24, 2016

Applies to:

Oracle Receivables - Version 11.5.7 to 11.5.10.3 [Release 11.5.7 to 11.5.10]
Information in this document applies to any platform.
Checked for relevance on 13-May-2013

Symptoms

Lockbox process is suffering a performance issue. The porrly performing statement is shown below:

select ps.payment_schedule_id ,class ,ps.customer_trx_id ,
.....
from
ar_payment_schedules ps ,ra_customer_trx ct ,
ra_cust_trx_types ctt ,ra_terms rt ,ra_cust_trx_line_gl_dist dist
where (((((((((((((customer_id=:b11 and
      ps.selected_for_receipt_batch_id is null )
and ps.reserved_type is null )
and ps.reserved_value is null ) and class not  in ('PMT','GUAR','BR'))
and (amount_due_remaining-DECODE(:b0,:b1,0,:b2, nvl(receivables_charges_remaining,0)))>0)
and ps.invoice_currency_code=:b15)
       and ps.term_id=rt.term_id(+)) and
ps.customer_trx_id=ct.customer_trx_id)
      and ct.cust_trx_type_id=ctt.cust_trx_type_id) and ct.customer_trx_id=
      dist.customer_trx_id) and dist.account_class='REC') and
      dist.latest_rec_flag='Y') and
DECODE(:b16,:b17,decode(nvl(amount_in_dispute,
      0),0,'N','Y'),:b18,'N')='N') order by due_date,payment_schedule_id
   

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.02          0          0          0           0
Execute   1256      0.90       0.81          0          0          0           0
Fetch     1288    135.70    2101.42     292206     630287          0       21405
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2545    136.62    2102.25     292206     630287          0       21405





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