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

Last updated on AUGUST 03, 2017

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 the following:

  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))
 
 
 
  call     count       cpu    elapsed       disk      query    current        rows
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  Parse        1      0.00       0.00          0          0          0           0
  Execute      1   6024.73    6034.45          0 1318979184          0           0
  Fetch        0      0.00       0.00          0          0          0           0
  ------- ------  -------- ---------- ---------- ---------- ---------- -----------
  total        2   6024.73    6034.46          0 1318979184          0           0
 
  Misses in library cache during parse: 1
  Misses in library cache during execute: 1
  Optimizer mode: ALL_ROWS
  Parsing user id: 210  (APPS)
 
 
  Rows     Execution Plan
  -------  ---------------------------------------------------
       0_ROWS
       0   LOAD TABLE CONVENTIONAL OF 'RA_INTERFACE_ERRORS_ALL'
       0    FILTER
   48838     SORT (GROUP BY)
  9606490      FILTER
  1921298       HASH JOIN
  1192575122        TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
                    'RA_INTERFACE_LINES_ALL' (TABLE)
   24419         INDEX   MODE: ANALYZED (RANGE SCAN) OF
                     'RA_INTERFACE_LINES_N1' (INDEX)
   48838        MERGE JOIN (CARTESIAN)
  1192575122         TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
                     'RA_INTERFACE_LINES_ALL' (TABLE)
  1921298          INDEX   MODE: ANALYZED (RANGE SCAN) OF
                      'RA_INTERFACE_LINES_N1' (INDEX)
  9606490         BUFFER (SORT)
       5          TABLE ACCESS   MODE: ANALYZED (FULL) OF
                      'RA_CUST_TRX_TYPES_ALL' (TABLE)

 

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