Pin_ledger_reports crashes with "snap shot too old".

(Doc ID 407428.1)

Last updated on SEPTEMBER 26, 2016

Applies to:

Oracle Communications Billing and Revenue Management - Version 6.0.3.0.0 and later
Oracle Solaris on SPARC (32-bit)
Solaris Operating System (SPARC)
***Checked for relevance on 26-Jan-2009***
Checked for relevance on 20-Oct-2011


Symptoms

The pin_ledger_report errored out after a number of hours (12 to 24 hrs) with the following error message in dm_oracle.log:

E Tue Feb 19 06:14:08 2002  xena  dm:1242  dm_subr.c(29.6.1.2):1161
        ORACLE error: do_sql_insert: OCIStmtExecute: code 1555, op 
-13166724=ORA-01555: snapshot too old: rollback segment number 38 
with name "RBS37" too small
ORA-06512: at "PIN.SUM_GL_BILLED", line 96
ORA-06512: at "PIN.LEDGER_REPORT_TRIGGER", line 40
ORA-04088: error during execution of trigger 'PIN.LEDGER_REPORT_TRIGGER'



The problem is possibly that pin_ledger_report is taking too long to process that it runs out of rollback segments in the database and therefore the snapshot it uses is out-dated.

Increasing the number of rollback segments resulted with no success.

One potential solution is to control which rollback segment the pin_ledger_report's select statement uses. 
That way we could direct ledger_report to use a large rollback segment. Today we have no control over 
which segment the select uses. Is this a possibility?

Some size info on our database:
Number of accounts: 175 000
Total number of events: 63 000 000
Number of events per month: 9 000 000
Server: Sun E4500, memory 10GB, 6 cpu 400 Mhz
Infranet version: 6.0.3 service pack3

In the production system there is a lot of traffic all the time with loading CDRs and other activities.

Is 24 hrs a normal processing time for one report for our size of database size and server? Is there any way 
the processing of pin_ledger_report can be performance tuned?

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