AutoInvoice Performance Issue: Poor Performance When Inserting into RA_INTERFACE_ERRORS table (Doc ID 1329285.1)

Last updated on SEPTEMBER 06, 2023

Applies to:

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

Symptoms

Poor performance of Import process in AutoInvoice. The issue was traced to many self-joins to RA_INTERFACE_ERRORS and joins to RA_CUST_TRX_TYPES table.

Review of TKPROF shows performance in the following query:

insert into RA_INTERFACE_ERRORS
(INTERFACE_LINE_ID,MESSAGE_TEXT,INVALID_VALUE)
   select L.INTERFACE_LINE_ID ,:b0 ,L.MANDATE_LAST_TRX_FLAG from
   RA_INTERFACE_LINES_GT L where (((L.REQUEST_ID=:b1 and L.CUSTOMER_TRX_ID is

   not null ) and NVL(L.INTERFACE_STATUS,'~')<>'P') and L.CUSTOMER_TRX_ID in
   (select L.CUSTOMER_TRX_ID from RA_INTERFACE_LINES_GT L ,RA_CUST_TRX_TYPES
   TT where ((((L.REQUEST_ID=:b1 and NVL(L.INTERFACE_STATUS,'~')<>'P') and
   TT.CUST_TRX_TYPE_ID=TT.CUST_TRX_TYPE_ID) and TT.TYPE in ('INV','DM')) and
   L.LINE_TYPE in ('LINE','CHARGES')) group by L.CUSTOMER_TRX_ID having
   count(distinct nvl(UPPER(L.MANDATE_LAST_TRX_FLAG),'N'))>1))