Re: rollback segments

In article <7g9hk3$ka4$1_at_zonnetje.NL.net>,   "nieuws net" <gkor_at_rdw.nl> wrote:
> Hi everybody
>
> Who can tell me about rollback sizing
>
> suppose i have a tablespace rollback 100M with 4 rollback segments
>
> how to size the default storage from both tablespace and segments
>
> now i have 400k initial and 40 k next in the tablespace
>
> and in the segments 2048 initial and 2048 next with optimum 16384 and 10
> extents
> it may be that those values don't make any sense
> who can help me out ?
> thanks
> gkor_at_rdw.nl
>

There is a relatively recent Oracle whilte paper on this subject by Millsap that recommends that your initial extent and next extent sizes equal and that you have at least 10 - 20 minextents and that optimal be set to next extent size times minextents. Ideally all rollback segments should be defined the same. You then watch your number of extends and shrinks to determine if you need more or larger rollback segments. I would reference the paper but I am not sure where I have it as I may have read it on-line.

The following two questions can help you size your rollback segments:

What is the size of the largest transaction the rollback segments must be able to handle (how many bytes of data between commits) on a normal basis?

What is the total amount of redo the system will generate in a day? I have found that our relatively large OLTP system (120G with 225 concurrent users) requires 10% of the daily redo worth of space be pre-allocated in rollback segments to eliminate an excessive number of snapshot too old errors. Plus you need at 30% to 100% of the amount of allocated space for additional extents.

I recommend you allocate a special large rollback segment to support maintenance and you keep this segment offline except when you use it.

Rollback usage varies greatly from application to application so you will need to fit them to your environment. Four segments is too few for most production systems but I use four available segments in all my test systems.

I hope this helps.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Apr 29 1999 - 08:16:36 CDT