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
