RE: Sizing rollback segments

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2667656313360#2670389321836

--

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
www.proquest.com
www.csa.com

ProQuest...Start here.

-----Original Message-----

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
To: oracle-l_at_freelists.org
Subject: Sizing rollback segments

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

--

http://www.freelists.org/webpage/oracle-l Received on Fri May 09 2008 - 10:17:07 CDT