|
| Home |
| Oracle Tuning |
| Oracle How To |
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. |