Oracle Automatic undo management and transactions_per_rollback_segmentOracle tips by Burleson
I've been doing a lot of database health checks
on 9i and now 10g databases recently. Most are using the automatic
undo management feature and on the whole it does a pretty good job
of managing the undo segments (for you other old timers, rollback
However, I have been noticing, shall I say, some rather retro
behavior as a result of the automatic undo management. In the bad
old days when we managed the undo segments manually we would tune to
reduce extends and the subsequent shrinks which resulted. The
shrinks would cause waits on the next session to use the segment as
the segment shrank back to the "optimal" setting.
The usual method to set the initial, next and optimal was to examine
the rollback segment views and determine such values as average
transaction size, max transaction size and also, determine the
number of active DML and DDL statements (SELECT didn't and doesn't
really count for much in rollback/undo activity, generally
speaking). From these values we could set initial, next and optimal
to reduce over extending the segments and reduce the subsequent
shrinks and waits as well as the needed number of segments.
What seems to be happening is that Oracle looks at two basic
parameters, TRANSACTIONS (based on 1.1*SESSIONS) and
TRANSACTIONS_PER_ROLLBACK_SEGMENT, and then uses an internal
algorithm to determine the number of undo segments to create in the
undo tablespace. The size seems to be determined by the number
created and the overall size of the tablespace. So, if you set up
for 300 SESSIONS this usually means about 330 TRANSACTIONS, the
TRANSACTIONS_PER_ROLLBACK_SEGMENT defaults to 5 so Oracle right from
the gate assumes you will ultimately need 66 undo segments. Seems
they forgot that generally speaking, only about 1 in 10
"transactions" in most databases actually do DML/DDL and that 90%
are usually SELECT. I have seen in almost all Oracle databases with
automatic undo used, that reach near the setting of SESSIONS number
of actual connected users, that Oracle over allocates the number of
undo segments leaving sometimes dozens offline and never used.
The other thing I see a great deal of is the old extends, shrinks
and waits we used to spend so much time tuning away. In many cases I
also see the old ORA-01555 (snapshot too old) errors coming back. If
the undo segment tablespace is too small and Oracle creates too many
small segments, then it is quite easy to see why.
So, am I saying don't use automatic undo? No, not at all. I say use
the automatic undo, but understand how to use it wisely.
Essentially, utilize the TRANSACTIONS_PER_ROLLBACK_SEGMENT to
control the number of segments created, and size the undo tablespace
large enough that the segments are sized appropriately. In addition,
if you are not going to use 300 sessions, don't set the SESSIONS to
300! Make sure to align the SESSIONS parameter to the proper number
of expected sessions.
If you need to change the undo segment configuration in your
environment (look at the v$rollstat view to see if you have
excessive waits, shrinks and extends) you will need to alter the
parameters, configure a second undo segment tablespace, and then
restart the database (if you changed SESSIONS or
TRANSACTIONS_PER_ROLLBACK_SEGMENT) to utilize the new settings.
What seems to be happening, is that as a start the Oracle algorithm
will create 10 active undo segments and sets the
MAX_ROLLBACK_SEGMENTS parameter equal to the value
TRANSACTIONS/TRANSACTIONS_PER_ROLLBACK_SEGMENT, as the number of
session increases, Oracle adds a new segment at each increment of
10*TRANSACTIONS_PER_ROLLBACK_SEGMENT that your user count reaches.
It doesn't seem to care if the session is doing anything, it just
has to be present. Oracle leaves the new segment offline, just
taking up space, unless the user does DML or DDL. The minimum
setting Oracle seems to utilize is 30 for MAX_ROLLBACK_SEGMENTS. For
example, with a SESSIONS setting of 300, this resulted in a
TRANSACTIONS setting of 330, with a default
TRANSACTIONS_PER_ROLLBACK_SEGMENT of 5, the MAX_ROLLBACK_SEGMENTS
parameter was set to 66. With a setting of 20, instead of a new
setting of 17 (330/20 rounded up) we get a setting of 30. If we set
it to 10, we get a setting of 33. Note that even with manually
setting the parameter MAX_ROLLBACK_SEGMENTS, if automatic UNDO
management is turned on, your setting will be overridden with the
So watch the settings of SESSIONS, TRANSACTIONS_PER_ROLLBACK_SEGMENT
and the size of the undo tablespace to properly use the automatic
undo feature in Oracle9i and 10g.