Re: Sizing of Rollback Segment

In article <86bat6$bqr8_at_imsp212.netvigator.com>,   "Ken" <kenchowhk_at_yahoo.com> wrote:
> Hi there,
>
> I want to learn from you how to do the sizing estimation for rollback
> segment. e.g. what should be the size for the rollback segment
> tablespace ? what's the dependency for calculating the sizing ?
> How to determine how many rollback segment should be created
> for a new database ? what information should be collected first
> from user ?
>
> currently, I will put a database with totally 100GB to production
> where with 200 concurrent OLTP users and 10 daemons running
> on top of the database, 20 nightly batch job. Based on this
> scenario, what's the proper estimation for the rollback segment ?
> I have skim thru several books but without any hints on that.
>
> thanks
> Ken
>

I attempted to reply to this before but deja news gave me the signon screen again when I hit send and would not return me to my post so if this shows up twice my apologies.

First the reason you have not been able to find much about this is because every system is different and what is excessive at one site is way too little for another even when things lile number of users and total size of the data are the same.

Here goes:
First I prefer only one set of identically defined rbs segments to support the database. If you create special segments for batch you run into problems making sure the batch process is distributed correctly over the batch segments and that jobs assigned to the same segment that are normally ran well apart are not ran at the same time creating an extent problem. Plus you will probably need a means of bringing the segments on and off-line to prevent regular transaction from using the segments which you have no way of doing while they are on-line.

So based on current Oracle/Expert thinging: initial = next and minextents is at least 10 to 20 optimal = minextents X next

Each segment needs to be able to handle the largest normal transaction that your design rules allow without taking too many additional extents as you do not want excessive number of extends or shrinks to take place on your system. (Entends can be realitively costly and shrinks can cause snapshot too old conditions.)

Now 200 concurrent users will probably only result in a small number of active transactions at any one time. You can query v$transactions during peak usage hours and get a good idea.

I have found that the total amount of pre-allocated rbs segment area needs to equal at least 5% of your daily redo log generation or you get too many snapshot too old errors.

So based on the above I recommend you consider defining 8 rbs segments with initial and next extent sizes of 2 - 4M and minextents of 20 which will give you 320 - 640M of preallocated space to hold old copies of data. I also recommend that you preallocate a large, 1G, rbs segment for maintenance and other special use that you keep offline until needed.

To support expansion and defining additional rbs segments as may prove necessary your rbs tablespace should have at least as much space as you pre-allocate and probably want twice that much. This means your rbs tablespce should be 2 - 3G.

Your system needs may vary from what I have seen with the 4/5 production systems that I support but I do one system very similiar to what you described.

Note - beware of processes that are designed to read and update the same table with a commit after every row as these will overlay the rbs segment data they need to create consistent reads if the tables involved are of any size.
--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Jan 22 2000 - 16:35:45 CST