Sizing rollback segments

From: Schauss, Peter <peter.schauss_at_ngc.com>
Date: Tue, 6 May 2008 16:03:09 -0500
Message-ID: <CD9150D80CFCFB42BC73C40791C1E01901CD71B0@XMBIL112.northgrum.com>


This is for Oracle 8.1.7.4.

I have a data warehouse database which I have inherited on which we run an ETL every hour. It currently has 14 rollback sized:

  • initial_extent = 5 mb
  • next extent 5 mb
  • min extents 11

After the database has been up for a day the number of extents on each segment ranges from 34 to 295. Right after the database was restarted a few days ago several of the users' reports returned with ORA-01555 errors. The problem seems to have gone away now that the rollback segments have increased in size.

So my question is should I increase min_extents to 34 in hopes of minimizing the ORA-01555 errors on startup? I note that Oracle recommends setting this parameter no higher than 20, making some vague reference to their own testing. Does this mean that I should think about rebuilding the rollback segments at a larger size, 10 mb for example?

Given that we run ETL's every hour I have not seen the segments shrink appreciably so I suspect that the optimal parameter is probably not going to be relevant in my situation.

Any suggestions welcome.

Thanks,
Peter Schauss

--
http://www.freelists.org/webpage/oracle-l
Received on Tue May 06 2008 - 16:03:09 CDT

Original text of this message