Cry about...
Oracle How To ...

Tuning rollback segments

To identify contention for rollback segments first find out the number of times that processes had to wait for the rollback segment header and blocks. The V$WAITSTAT view contains this information:

select class, count from v$waitstat
where class in ('system undo header', 'system undo block', 'undo header', 'undo block');

The number of waits for any class should be compared with the number of logical reads over the same period of time. This information can be found in V$SYSSTAT:

select sum(value) from v$sysstat
where name in ('db block gets', 'consistent gets');

If the number of waits for any class of waits is greater than 1% of the total number of logical reads then add more rollback segments.

The following query gives the percentage of times that a request for data resulted in a wait for a rollback segment:

select round(sum(waits)/sum(gets),2) from v$rollstat;

If the percentage is greater than 1% then create more rollback segments.

Rollback segments should be isolated as much as possible by placing them in their own tablespace, preferably on a separate disk from other active tablespaces. The OPTIMAL parameter is used to cause rollback segments to shrink back to an optimal size after they have dynamically extended. The V$ROLLSTAT table can help in determining proper sizing of rollback segments:

Select segment_name, shrinks, aveshrink, aveactive "Avg.Active"
from v$rollstat v, dba_rollback_segs d
where v.usn = d.segment_id;

The following table shows how to interpret these results:

Cumulative number of shrinks Average size of shrink Recommendation
Low Low If the value for “Avg.Active” is close to OPTIMAL, the settings are correct. If not, then OPTIMAL is too large.

(Note: Be aware that it is sometimes better to have a larger optimal value - depending on the nature of the applications running, reducing it towards “Avg.Active” may cause some applications to start experiencing ORA-01555.)

Low High Excellent – few, large shrinks.
High Low Too many shrinks – OPTIMAL is too small.
High High Increase OPTIMAL until the number of shrinks is lower.

This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.

About the author: is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.