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
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
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
OPTIMAL parameter is used to cause rollback
segments to shrink back to an optimal size after they have dynamically extended.
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 –
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: Brian Cryer 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.