Reports Performance Issue: Poor Performance of Incomplete Invoices Report (Doc ID 1056244.1)

Last updated on AUGUST 24, 2016

Applies to:

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

Symptoms

Poor Performance encountered when running Incomplete Invoices Report. Review of the TKPROF file shows the following select statement has an issue:

select trx_number Number_A , ctt . name Type , ct . trx_date Date_A , ctlgld
   . gl_date GL_Date , substrb ( party . party_name , 1 , 50 ) Name , cust .
   account_number Number_B
 from
  ra_customer_trx ct , hz_cust_accounts cust , hz_parties party ,
   ra_cust_trx_types ctt , ra_cust_trx_line_gl_dist ctlgld where ct .
   bill_to_customer_id = cust . cust_account_id (+) and cust . party_id =
   party . party_id (+) and ct . cust_trx_type_id = ctt . cust_trx_type_id and
    ct . complete_flag = 'N' and ctlgld . latest_rec_flag (+) = 'Y' and ctlgld
   . customer_trx_id (+) = ct . customer_trx_id and nvl ( ctlgld .
   account_class , 'REC' ) = 'REC'        order by decode ( : p_order_by ,
   'Invoice' , ct . trx_number , party . party_name )
 
 
 call     count       cpu    elapsed       disk      query    current         rows
 ------- ------  -------- ---------- ---------- ---------- ----------   ----------
 Parse        1      0.00       0.00          0          0          0            0
 Execute      1      0.01       0.01          0          0          0            0
 Fetch        1     32.76    1716.01     878302     890732          0            3
 ------- ------  -------- ---------- ---------- ---------- ----------   ----------
 total        3     32.78    1716.02     878302     890732          0            3
 
 Misses in library cache during parse: 1
 Misses in library cache during execute: 1
 Optimizer mode: CHOOSE
 Parsing user id: 34 
 
 Rows     Row Source Operation
 -------  ---------------------------------------------------
       3  SORT ORDER BY (cr=890732 pr=878302 pw=0 time=292783037 us)
       3   NESTED LOOPS OUTER (cr=890732 pr=878302 pw=0 time=282311145 us)
       3    NESTED LOOPS OUTER (cr=890720 pr=878300 pw=0 time=282268461 us)
       3     FILTER  (cr=890708 pr=878296 pw=0 time=282198799 us)
       3      NESTED LOOPS OUTER (cr=890708 pr=878296 pw=0 time=282198702 us)
       3       HASH JOIN  (cr=890693 pr=878292 pw=0 time=282129158 us)
     133        TABLE ACCESS FULL RA_CUST_TRX_TYPES_ALL (cr=92 pr=0 pw=0 time=253 us)
       3        TABLE ACCESS FULL RA_CUSTOMER_TRX_ALL (cr=890601 pr=878292 pw=0 time=282129120 us)
       3       TABLE ACCESS BY INDEX ROWID RA_CUST_TRX_LINE_GL_DIST_ALL (cr=15 pr=4 pw=0 time=146251 us)
       3        INDEX RANGE SCAN RA_CUST_TRX_LINE_GL_DIST_N6 (cr=12 pr=2 pw=0 time=58934 us)(object id 150704)
       3     TABLE ACCESS BY INDEX ROWID HZ_CUST_ACCOUNTS (cr=12 pr=4 pw=0 time=124895 us)
       3      INDEX UNIQUE SCAN HZ_CUST_ACCOUNTS_U1 (cr=9 pr=2 pw=0 time=51683 us)(object id 39669)
       3    TABLE ACCESS BY INDEX ROWID HZ_PARTIES (cr=12 pr=2 pw=0 time=42692 us)
       3     INDEX UNIQUE SCAN HZ_PARTIES_U1 (cr=9 pr=1 pw=0 time=24152 us)(object id 39757)

 

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