rollback segment (rbs) reach to max extents

[CHEN WAI CHUAN]

If my rbs tablespace shown reach to max extents, should i increase the max extents value or increase datafile ?? Please help.

[Alexander M. Ermes · Accepted solution]

Hi there.
You should count the number of rollback segs, the number of maxextents for these and the size of the rollback segs and the size of the tablespace for the rollback segs.
if #rollbacksegs x size_rollbacksegs x maxextents of rollbacksegs > tablespace, enhance the tablespace.
Sample :
5 rollback segs
size 10M
extent size 10M
maxextents 10
5 x 10 x 10 = 500M for the tablespace.
If the result is lt than the tablespace size,
try to distribute the rest of the tablespace to the rollbacksegs extents.
If you need more explanation, contact me
at Alexander_Ermes@web.de

Rgds
Alexander M. Ermes

[Volker Borowski]

Hello CHEN,

Alexander is right in most cases. One thing to consider in addition if you have OLTP application, is that you should configure an OPTIMAL size, to shrink the rollback segments automaticly.
If you have a setup like that, you should give NEXT and MAX_EXTENTS values so that one rollback segment can use the entire space if needed.

Example:

2GB RBS tablespace
5 rollback segments
transactions_per_rollback_segment = 10 (init.ora)
average rollback load per transaction i.e. 1MB
-> You should monitor V$ROLLSTAT to get a feeling for this value, but do not choose a too low value

=> This would lead me to an OPTIMAL SIZE of around 10MB for one rollback segment, so for standard load 10 transactions in one RBS will not need to allocate new extents, because 10MB would be enough.

So assuming 5 segments, around 50 MB will be used minimum.

If one user fires a big transaction, the rbs in charge should be able to groe up to 2 GB.
So in this case I would go for the following values:

MINEXTENTS = 3
NEXT = INITIAL = 4MB
MAXEXTENTS = 500
OPTIMAL = 12MB

Very important is to have all extents in this tablespace with the same size, if you configure an optimal value, so the freespace will not be fragmented after shrinks.

Hope this helps
Volker