The AR to GL Reconciliation Report Has Poor Performance - ORA-01555: Snapshot Too Old: Rollback Segment Number (Doc ID 1921950.1)

Last updated on SEPTEMBER 18, 2017

Applies to:

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

Symptoms

Attempting to run the AR to GL Reconciliation Report and it is running for a long time.

In 11i it took 6 minutes vs 2-3 hours in R12.2.3.

The AR to GL Reconciliation Report errors out with the following performance error:

java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number 1042 with name "_SYSSMU1042_2111396481$" too small
ORA-06512: at "APPS.ARP_RECON_REP", line 4396
ORA-06512: at line 1

It runs for almost 21 hours and finally ends up with this Error

poor performance for

  insert into ar_gl_recon_gt(code_combination_id,
  receivables_dr,receivables_cr,
  account_type, account_type_code,account,company)
  (select dat.code_combination_id,
  sum(nvl(acctd_amount_dr,0)) receivables_debit,
  sum(nvl(acctd_amount_cr,0)) receivables_credit,
  lookup.description account_type,
  gc.account_type account_type_code, gc.SEGMENT1||'.'||gc.SEGMENT2||'.'||gc.SEGMENT3||'.'||gc.SEGMENT4||'.'||gc.SEGMENT5||'.'||gc.SEGMENT6||'.'||gc.SEGMENT7 account, gc.SEGMENT3 company from (
-- Bug 6943555 [https://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=6943555]
..................
  gl_code_combinations gc,
  gl_lookups lookup
  where dat.code_combination_id = gc.code_combination_id
  and lookup.lookup_code = gc.account_type
  and lookup.lookup_type = 'ACCOUNT TYPE'
  group by dat.code_combination_id,lookup.description, gc.code_combination_id,gc.account_type, gc.SEGMENT1||'.'||gc.SEGMENT2||'.'||gc.SEGMENT3||'.'||gc.SEGMENT4||'.'||gc.SEGMENT5||'.'||gc.SEGMENT6||'.'||gc.SEGMENT7 , gc.SEGMENT3 )
 
the statement spent a lot of time to execute query because of large volume of information handled

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 6370.53 62669.92 67321576 54719693 5852 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 6370.53 62669.93 67321576 54719693 5852 0

Changes

Upgrade from 11.5.10.2 to R12.2.3

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