Understanding Rollback Segment Sizes

From: steven_nospam at Yahoo! Canada <steven_nospam_at_yahoo.ca>
Date: 17 Jan 2007 15:00:01 -0800
Message-ID: <1169074801.878417.101490@38g2000cwa.googlegroups.com>

Hi All,

I have what I think is probably a very basic question. I'm having a bit of a problem understanding how to size my rollback segments, if I have reserved too much or too little space, and whether I have enough of them.

I am setting up a new Oracle 9.2.0 database and our company has four SQLs for creating the databases - one SQL each for sizing based on small, medium, large, and extra-large databases. I am not too familiar with all the items in there and I want to understand what it is doing instead of just blindly trusting what someone else set up years ago for an Oracle 8i system...things change but these scripts may not have.

So here is where I stand:
- I am on an AIX 5L system

I have a database containing roughly 10GB of actual data.
The largest table contains 686643 rows for a total segment size of 721 MB.
The database is used to store sales, inventory, and accounting data.
Normal daily activities are adding orders and shipping material

The database creation SQL indicates I should make four (4) data files for the RBS (3 X 1GB, 1X 500MB).

create tablespace esindl01rbs
datafile '/oradata01/l01/rbs/esindl01rbs01' size 1000M autoextend OFF DEFAULT STORAGE(INITIAL 999M) ONLINE PERMANENT; alter tablespace esindl01rbs
add datafile '/oradata01/l01/rbs/esindl01rbs02' size 500M autoextend OFF;

alter tablespace esindl01rbs
add datafile '/oradata01/l01/rbs/esindl01rbs03' size 1000M autoextend OFF;

alter tablespace esindl01rbs
add datafile '/oradata01/l01/rbs/esindl01rbs04' size 1000M autoextend OFF;

I should then create four rollback segments that each appear exactly the same as below:

create rollback segment esindl01_rbs01 tablespace esindl01rbs storage(initial 50M next 50M minextents 2 maxextents 10 optimal 300M);

So my questions here are:

Why was the second data file in the RBS made smaller? I am pretty sure that it was just an error by whoever set it up, and they can all be the same size.
When I look at the existing RBS area (same DB on older system), it is only 30% used. Am I wasting a lot of space here? I assume the RBS expand if any undo information is stored there and requires more space (up to max of 10 extents).
What is the maximum size each rollback segment might grow based on these settings? I am thinking minimum 100MB (minextents) and maximum 500MB (maxextents), so I believe that even if we reach maxextents on all four RBS, each one would only be 500MB max?

I checked the V$ROLLSTAT and there are almost now extends or shrinks, and a book I am reviewing on tuning indicates all four are 99.9% hitting the mark (waits vs gets). As I said, I'm not really trying to tune this database, but rather understand why something is set a certain way and whether I am actually wasting space, which I think I am.

Thanks in advance.

Steve Received on Wed Jan 17 2007 - 17:00:01 CST