RE: Sizing rollback segments
Date: Fri, 9 May 2008 11:17:07 -0400
Mark J. Bobak
Senior Database Administrator, System & Product Technologies ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346 Ann Arbor MI 48106-1346
+1.734.997.4059 or +1.800.521.0600 x 4059 mark.bobak_at_proquest.com
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Schauss, Peter Sent: Tuesday, May 06, 2008 5:03 PM
Subject: Sizing rollback segments
This is for Oracle 18.104.22.168.
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.
http://www.freelists.org/webpage/oracle-l Received on Fri May 09 2008 - 10:17:07 CDT