Re: question about automatic undo management

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:WpXW9.28930$jM5.74108_at_newsfeeds.bigpond.com...
> "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 msensible 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'reanagement, and there are performance penalties to pay. So
> if you've done the
> 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?
>
>

So the main advantage of automatic undo management is that you can stick all your undo into a seperate tablespace instead of managing rollback segments correct? This way you only have to manage the size of the tablespace instead of all your rollback segments correct?

undo_retention is the parameter that tells how long to save undo right(my book is at work, cant remember), so if you are in automatic undo management and have 1 tablespace for undo and that tablespace is full, will you get a 'snapshot too old' or will it attempt to steal extents from other tablespaces?

Also, in a perfect universe your undo retention would be so big that it would keep until you do a cold backup and shutdown the database(yes I know this is unrealistic), but that is the goal to shoot for? Received on Mon Jan 20 2003 - 16:13:03 CST