Mike Ault's thoughts on various topics, Oracle related and not. Note: I reserve the right to delete comments that are not contributing to the overall theme of the BLOG or are insulting or demeaning to anyone. The posts on this blog are provided “as is” with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer.

Wednesday, July 06, 2005

Automatic UNDO...almost!

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 segments).

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 TRANSACTIONS_PER_ROLLBACK_SEGMENT above 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 calculated one.

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.


Daniel Fink said...

The number of undo segments in a database is a function of the maxiumum number of concurrent sessions and the space available within the undo tablespace. AUM attempts to bind each transaction to its own undo segment. This is why the number of segments can increase dramatically. If an undo segment has not been used in a certain amount of time (12 hours, IIRC), SMON will offline the segment until it is needed again. This can be seen in alert log entries.

Of course, once an undo segment is created, there is not a mechanism for dropping it, other than creating a new undo_tablespace and using it.

It has been awhile since I researched undo, but I recall that the starting number of undo segments is 11 (10 for user access, 1 in system tablespace). When the 11th concurrent transaction is started, a new undo segment is created.

'Thought & Humor' said...

We work like a horse.
We eat like a pig.
We like to play chicken.
You can get someone's goat.
We can be as slippery as a snake.
We get dog tired.
We can be as quiet as a mouse.
We can be as quick as a cat.
Some of us are as strong as an ox.
People try to buffalo others.
Some are as ugly as a toad.
We can be as gentle as a lamb.
Sometimes we are as happy as a lark.
Some of us drink like a fish.
We can be as proud as a peacock.
A few of us are as hairy as a gorilla.
You can get a frog in your throat.
We can be a lone wolf.
But I'm having a whale of a time!

You have a riveting web log
and undoubtedly must have
atypical & quiescent potential
for your intended readership.
May I suggest that you do
everything in your power to
honor your encyclopedic/omniscient
Designer/Architect as well
as your revering audience.

Please remember to never
restrict anyone's opportunities
for ascertaining uninterrupted
existence for their quintessence.

There is a time for everything,
a season for every activity
under heaven. A time to be
born and a time to die. A
time to plant and a time to
harvest. A time to kill and
a time to heal. A time to
tear down and a time to
rebuild. A time to cry and
a time to laugh. A time to
grieve and a time to dance.
A time to scatter stones
and a time to gather stones.
A time to embrace and a
time to turn away. A time to
search and a time to lose.
A time to keep and a time to
throw away. A time to tear
and a time to mend. A time
to be quiet and a time to
speak up. A time to love
and a time to hate. A time
for war and a time for peace.

Best wishes for continued ascendancy,

'Thought & Humor'

P.S. One thing of which I am sure is
that the common culture of my youth
is gone for good. It was hollowed out
by the rise of ethnic "identity politics,"
then splintered beyond hope of repair
by the emergence of the web-based
technologies that so maximized and
facilitated cultural choice as to make
the broad-based offerings of the old
mass media look bland and unchallenging
by comparison."