Re: Understanding Rollback Segment Sizes

joel garry wrote:
> steven_nospam at Yahoo! Canada wrote:
> > 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:
> >
> > 1) 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.
> >
> > 2) 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).
> >
> > 3) 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
>
> 1. Space is cheap. If you have it, anyways.
> 2. Yes, things have changed and this script is obsolete for 9.2.
> 3. Use proper terminology. In Oracle, a database has many schemata
> (singular, schema).
> 4. See the Concepts manual (tahiti.oracle.com, among other places).
> 5. It's called undo now. Read about it in the Database Administrators
> Guide.
> 6. Use Automatic Undo Management and give it plenty of space.
> 7. Some situations do still require RBS management. If you still have
> to use it, don't use the optimal parameter. Search asktom.oracle.com
> (or his books) to understand ORA-155x errors.
> 8. It is important to understand that undo is used to create
> read-consistent views of the db. This means that it's not just the
> size of transactions that matter, but how long they last, and how long
> the other transactions that need to create those views last.
> 9. undo_retention also has impacts on undo.
> 10. If you have 9.2 OEM, it has some pretty pictures for sizing undo
> given the undo retention. If you don't have it, you can check sysdba
> views that have names like '%ADVICE%'. Once things are running, that
> is.
>
> My OEM advisors tell me I need more undo than the rest of the db, but I
> ignore them. But I'm old and crotchety about it, and have a cron job
> that kills obviously senile sessions. YMMV.
>
> jg
> --

Yes, it is time to update the scripts to reflect the Oracle recommended use of UNDO tablespaces to manage rollbacks segments automatically.

However I will disagree with Joel about using optimal on manually managed rollback segments. If you need to manage RBS segments manually you more than likely only need a few of them and the segments need to be large. So that you do not have to manually resize the segments so that no segment fails to expand while another is holding large amounts of unneeded space use the optimal setting. Just be sure to set it large so that segments rarely shrink.

• Mark D Powell --