Oracle question...

swhitmire

Reputable Poster
How much space does everyone else have dedicated to rollback segments?
We've had to increase the size of ours to 10GB, and we haven't
even gone live yet. Is this normal? Is there something we could
do to make things work in a smaller space without getting errors?

Thanks!
--Scotti Whitmire
DeRoyal Industries

Xe, SP14.2, AIX4.3.3, Oracle 8.1.6
 

Carl_Fisher

Well Known Member
What is blowing your rollbacks? Day to day running should be quite happy
with small rollbacks. We have run ours with 2Gbyte Rollback Tablespace with
30 small rollbacks - all have autoextend set, and this works for normal
processing.

We are going through an upgrade to Xe and the Table Conversions do require 4
bigger rollbacks - and for this we have a 10 Gbyte Tablespace, but we have
been running for over two years with the small rollbacks for everyday.

Do you have autoextend set on the Rollbacks? If not this could be your
problem, ours all start out at about 1 meg, but where required they are
allowed to grow. The Big rollbacks start at 200 meg and have been seen to
grow to 3.5 Gb for a large table conversion.

Get an Oracle DBA to look at it, and check how it is set up.



OW B732.1 SP11.3.1 (live)
OW733.3 Xe SP 13.1 (dev)
Enterprise Server - Intel NT + Oracle 8.0.6
Client - Citrix TSE + Some 95 and NT PC's
 

Larry_Jones

Legendary Poster
Scotti,

You're not configured right if you're using 10GB of rollback space. You're errors are probably the result of a poorly configured db instance.

The total size of all your rollback segments means nothing. A given transaction will be assigned to only 1 rollback segment. Under OW we can't control which Rollback segment will be used by a given transaction so its important to use uniformly sized segments. Extent size within segments should be uniform also.

Since OW is a good citizen when it comes to keeping interactive user transactions small and short-lived you don't need very many segments to service even a relatively large user population. If however you have a lot of batch jobs running concurrently you would need more segments.

As far as segment sizing goes we've found that 100MB segments easily handles the largest normal batch job for us (even if it is way oversized for user transactions). For a population of approximately 100 users with a low number of batch jobs during the day and most evening batches single threaded we easily get by with 4 roll-back segments.

The only exceptions we've found to this is during OW upgrades when you need to temporarily create and use a single 500 - 1000MB roll back segment.

Here's the SQL that created our rollback tablespace and segments:
REM ******************************************************************
REM * Create a tablespace for rollback segments.
REM * Rollback segment configuration guidelines:
REM * 1 rollback segments for every 4 concurrent xactions.
REM * No more than 50 rollback segments.
REM * All rollback segments the same size.
REM * Homogeneously-sized extents in each rollback segment.
REM ******************************************************************
create tablespace rbs
datafile
'/u02/oradata/prod/rbs01.dbf' size 550M
default storage
(
initial 5M
next 5M
minextents 20
maxextents 200
);

REM ******************************************************************
REM * Create rollback segments.
REM ******************************************************************
create rollback segment r01 tablespace rbs
STORAGE (INITIAL 5M
NEXT 5M
MINEXTENTS 20
MAXEXTENTS 100
OPTIMAL 100M);
create rollback segment r02 tablespace rbs
STORAGE (INITIAL 5M
NEXT 5M
MINEXTENTS 20
MAXEXTENTS 100
OPTIMAL 100M);
create rollback segment r03 tablespace rbs
STORAGE (INITIAL 5M
NEXT 5M
MINEXTENTS 20
MAXEXTENTS 100
OPTIMAL 100M);
create rollback segment r04 tablespace rbs
STORAGE (INITIAL 5M
NEXT 5M
MINEXTENTS 20
MAXEXTENTS 100
OPTIMAL 100M);


===============================================

Note that though the Optimal size of the sements are 100MB they can temporarily grow to 500MB (MaxExtents * ExtentSize).


The following PL/SQL may be used to check the number of transactions per segment and maximum number of waits (execute when system is busy to get a more accurate picture).

set echo off;
set pagesize 50;
set linesize 200;
rem Show Rollback Segments Stats
select n.name, s.extents, s.rssize as "Curr Size", s.optsize as "Optimal",
s.hwmsize as "High Water", s.xacts as "Curr Trans", s.waits as "Waits", s.gets as "Gets", s.aveactive as "AvgSizeExtents", s.status
from v$rollname n, v$rollstat s
where n.usn = s.usn;
======================================================


Cheers,



Larry Jones
[email protected]
OneWorld B733.1, SP 11.3
HPUX 11, Oracle SE 8.1.6
SandBox: OneWorld XE SP15
 
Top