Re: question about automatic undo management

"Ryan Gaffuri" <rgaffuri_at_cox.net> wrote in message news:1efdad5b.0301200603.31108cce_at_posting.google.com...
> any rules of thumb on the undo_retention parameter? or do you play it
> by ear depending on your system and if you have long batches?
>

Well, it might be unscientific, but I'd recommend buying the biggest hard disk you can get, and calling it your undo tablespace (or the biggest RAIDed partition). Then set undo_retention to whatever setting causes all of that space to be used. Point is, retaining undo is good for all sorts of reasons... lack of 1555s is one of them, but flashback is another.

More scientifically, if you query v$undostat, you'll see the largest amount of undo generated in each 10 minute period over the past 24 hours. You'll also see the longest-running query performed in each 10 minute period. Your should retain as much undo as needed for the maximum of either, probably the maximum of both.

> also, if Im using automatic undo management, does this totally
> eliminate the chance for snapshot too old? or does it restrict the
> odds of it happening? If your undo tablespace fills up, Im assuming it
> can happen? or will it automaticly increase the size of your
> tablespace?

Undo retention is a promise, not a guarantee. If you ask to retain 60 minutes of undo, and then use a 10MB undo tablespace, we aren't going to be actually able to retain what you've asked us to. And at that point, if we need the space, then undo within the retention time (what is called 'unexpired undo') can indeed be over-written. And if you over-write undo required for a consistent read, then yes, you'll still get an ORA-1555 Snapshot too Old.

Oracle goes to quite some lengths to avoid that, though. If an undo segment runs out of space, but a transaction still needs to place further undo within it, then obviously we try to grow that undo segment, just as we used to grow rollback segments when they looped back ontop of themselves. But if we've run out of space in the tablespace, and cannot grow a segment any further, we can 'steal' unused extents from other undo segments (otherwise known as 'dynamic extent transfer'). And when we steal extents from another segment like that, we try and steal totally unused extents first, but if they're not available, extents containing expired undo (ie, it's past its retention time); and only if none of those are available do we steal extents containing unexpired undo (within the retention time).

You can see all of this in the wonderful 9i-specific view v$undostat. There are columns called 'UNXPSTEALCNT' (unexpired steal count) and 'EXPSTEALCNT' (expired steal count). There are also columns which tell you the relevant quantities in terms of blocks, rather than counts of the times it happened. There's also a column called SSOLDERRCNT -which is our good friend 'snapshot too old error count' (proving, by the by, that these things can still happen in 9i).

And having stolen as much space as we can, and having tried to therefore keep your transaction still going as much as we can, ultimately, we may run out of extents to steal. At which point Oracle will NOT start groing the undo tablespace *unless* you've turned on automatic extentsion of the datafiles, just as in previous releases. And, just as in previous releases, automatic extension of datafiles is a nice management convenience, but it's pretty poor DBA management, and there are performance penalties to pay. So if you've done the sensible thing and turned autoextension off, your transaction might steal as much space as it can from other undo segments, but then still run out of space: something the v$UNDOSTAT view refers to as a NOSPACEERRCNT.

So, extent stealing of any sort means your undo tablespace is probably a bit on the small side. The stealing of 'expired' undo, though, means we are still able to honour your undo_retention setting, so although the odd rogue flashback query might fail when otherwise it wouldn't, it's not the end of the world. If you get any UNexpired steals happening, then although we're trying to honour your retention period, we can't... and that's definitely a sign of too small an undo tablespace. And if you then have numbers in the SSOLDERRNCT or NOSPACEERRCNT it means that despite stealing everything we could get our hands on, people's reports and transactions are being compromised through lack of undo space.

Regards
HJR
> how well does this work in practice?
Received on Mon Jan 20 2003 - 12:52:22 CST