Rollbaclk Segments

[Mariel Gonzales Faylona]

I have 5 datafiles for tablespace rbs(rollback segment). I wanted to ask why is my datafiles are getting full since I have created 20 rollback segment? Rollback segemnts are being flushed when transactions are commited, right. What are stored in the datafiles, since rollback segs are flushed when transaction are being commited.

Regards
Maye

[Yogeeraj_1 · Accepted solution]

hi maye!

I don't think that the rollback segments are flushed after commit! It just rolls around!

Normally, you need rbs to be sufficient large such that it does not roll around and reuse
its storage during the period of time your longest running query is running.

how did you create your rbs?

regards
yogeeraj

[Eric Antunes]

Hi Maye,

What are the rollback segment sizes? You should try to minimize the fragmentation in the tablespace (in my databases I use just 9 rollback segments: the system segment of 56K, 3 of 48Mb and 5 with 128Mb). After that try to reduce the number of datafiles of the rollback tablespace...

Check also this query:

select n.name
, s.extents
, round(s.rssize / 1024) rb_kbytes
, round(s.writes / 1024) kbwrites
, s.gets /1000 gets
, round(100 * s.waits / s.gets, 2) "Content Pct. < 5%"
from v$rollname n
, v$rollstat s
where n.usn = s.usn
and s.gets > 0

Best Regards,

Eric Antunes

[Volker Borowski]

Hi,

if you do not configure a OPTIMAL storage value, your segments will never shrink, unless you do a
alter rollback segment shrink;

If you have configured an optimal size, and your segment has grown above this OPTIMAL value, it will shrink automaticly, when the write-pointer inside this specific segment jumps to the next extent and there are unused extents in this segment.

You should use automatic UNDO for several reasons.

Volker

[Indira Aramandla]

Hi Maye,

The managements of rollback segments is done in the following manner when you set the optimal option.

Optimal specifies the optimal size of a rollback segment in bytes. It can also be specified in kilobytes or megabytes. The RDBMS tries to keep the segment at its specified optimal size. The size is rounded up to the extent boundary, which means that the RDBMS tries to have the fewest number of extents such that the total size is greater than or equal to the size specified as OPTIMAL. If additional space is needed beyond the optimal size, it will eventually deallocate extents to shrink back to this size.

The process of deallocating extents is performed when the head moves from one extent to the next. At this time, the segment size is checked and the RDBMS determines if the next extent should be deallocated. The extent can only be deallocated if there are no active transaction in it. If necessary, the RDBMS will deallocate multiple extents at one time until the segment has shrunk back to its optimal size. The RDBMS always deallocates the oldest inactive extents as they are the least likely to be used for read consistency.

Automatic Undo Management. From Oracle9i onwards databases are capable of managing their own undo (rollback) segments. No longer will administrators need to carefully plan and tune the number and sizes of rollback segments or decide how to strategically assign transactions to a particular rollback segment. Oracle9i also allows administrators to allocate their undo space in a single undo tablespace with the database taking care of issues such as undo block contention, consistent read retention, and space utilization. With this design, you allocate undo space in a single undo tablespace, instead of maintaining a set of statically allocated rollback segments.

Attached is a document form Metalink Note Note:135090.1


Indira A

[Yogeeraj_1]

hi maye,

below a few more guidelines:

Place individual rollback segments into their own tablespaces with INITIAL = NEXT = 1M.

Also, you like to start with 15 or so rollback segments -- each with minextents=25 and maxextents=100. That would create 15 25MB rollback segments that can grow to 100MB each.

Then, periodically, typically before backups, manually offline and then drop each rollback segment, shrink the datafile associated with its tablespace back to the right size for 20 extents and recreate the rollback segment.
(I never use optimial, prefering to resize them manually before a backup or something). This allows for the infrequent "large" transaction to dynamically grow the rollback segment without having to interject a "set transaction use rollback segment BIG_RBS" into it.

The typical ddl statements would be like:

alter rollback segment rbs_&&1 offline;
drop rollback segment rbs_&&1;
alter database datafile '/d&&1/dbs/rbs_ts_&&1..dbf' resize 24968;
create rollback segment rbs_&&1
storage ( initial 1024k next 1024k minextents 25 ) tablespace rbs_ts_&&1;
alter rollback segment rbs_&&1 online;


In general, the rules of thumb to be a good starting point for databases are:
1. rollback segments must have equi-sized extents.
2. rollback segments should be allocated large to avoid 1555's
3. rollback segments should be plentiful for the amount of transactions you do.


hope this helps too!

regards
yogeeraj