My Oracle Support Banner

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

Last updated on JULY 12, 2020

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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
 1. Bug Summary
 2. Fixed Files
 3. Recommended Patches
 4. Solution Steps
 Still Have Questions?
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.