AutoInvoice Performance Issue: Poor Performance When Inserting into RA_INTERFACE_ERRORS table
(Doc ID 1329285.1)
Last updated on JANUARY 04, 2021
Oracle Receivables - Version 220.127.116.11 and later Information in this document applies to any platform.
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))
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!