The AR to GL Reconciliation Report Has Poor Performance - ORA-01555: Snapshot Too Old: Rollback Segment Number
(Doc ID 1921950.1)
Last updated on OCTOBER 15, 2024
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] .................. 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
