Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: question about automatic undo management

Re: question about automatic undo management

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Tue, 21 Jan 2003 18:20:57 +1100
Message-ID: <Hn6X9.29294$jM5.75561@newsfeeds.bigpond.com>

"Ryan" <rgaffuri_at_cox.net> wrote in message news:Pv_W9.39760$I55.1076460_at_news2.east.cox.net...
>
> "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?

Well, at the risk of being corrected by superior minds, I'd say that's *exactly* it. Fundamentally, "undo" segments are just "rollback" segments. All the re-naming mallarkey is just so much Marketing nonsense. The only difference (but it's a HUGE one) is that you don't have to manage each segment any more, just the total available space.

>
> undo_retention is the parameter that tells how long to save undo right(my
> book is at work, cant remember),

Er, it's the parameter that says "even though this transaction has been committed, how long do I wait before I adjust the tail pointer?" Bit of a difference.

>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?

As I said, if you are doing a bit of DML, and we need to make space for more undo entries, we try to steal UNexpired extents first, then EXpired extents second, and then we throw an out of space error when all else has failed. The 1555 error doesn't instrinsically arise the moment I've over-written expired or unexpired undo... only when a reader needs that particular piece of undo which I've stolen and over-written does the 1555 error arise.

>
> 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?

I don't know. I'm not sure what you're suggesting here. All I know is that anyone who skimps on undo tablespace is an utter moron. There's no excuses: it's purely a question of disk space, and nothing terribly quantum, relativistic, or requiring the IQ of Jonathan Lewis. And disk space is cheap. And if it isn't, then you were a moron to sign up with that particular vendor.

;-)
HJR Received on Tue Jan 21 2003 - 01:20:57 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US