Re: NOARCHIVELOG and rollback segments

Hi Howard,

Comments embedded.

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:3faa06d2$0$9913$afc38c87_at_news.optusnet.com.au...
>
> "John" <jbradshaw777_at_yahoo.com> wrote in message
> news:f2f59d82.0311052253.787a9121_at_posting.google.com...
> > "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> news:<3fa9829d$0$9607> >
> > >
> > Each undo segment has a segment header block. Therefore,
> > > huge tablespace means lots of undo segment header blocks clogging up
my
> > > buffer cache.
> > >
> > Can you clarify a bit? Does ORACLE only cache the rbs header block?
>
>
> No, of course not. All rollback segment blocks get cached. But the segment
> header gets touched by everyone who wants to make use of the segment, so
> they tend to stick around in the cache longer than an 'ordinary' block.
>
> So if I had just one rollback segment, I'd have only one block constantly
> being touched, and the cache can get on with storing the blocks that are
> actually used to store data/undo... a small overhead, in other words,
> compared to the number of 'working' undo blocks. But if you have hundreds
of
> segments, each one only hosting one transaction's rollback, the rollback
> could only have gotten into the segments by the segment header blocks
being
> touched (ie, loaded into the cache) first. And that would mean arelatively
> few "working" undo blocks compared to the number of segment header blocks.
> Lots of overhead, therefore. The LRU list will eventually age such
> rarely-used segment header blocks out of the cache, of course, but the hit
> is nevertheless there to start with.
>

I would say the issue you describe above (and an issue with AUM) is not so much with respect to the segment headers but with the number of undo blocks in general. By having massive numbers of undo segments, you hence dramatically reduce the likelihood of these undo blocks being reused by subsequent transactions. And what happens to dirty blocks as they aged out of cache ? They get written to disk, generating potentially expensive I/O overheads. *Every undo block not reused within the period it is either checkpointed or aged via the LRU algorithm generates an additional I/O*. And unless the undo block is subsequently required for read consistency or recovery, it's a "wasted" I/O as the undo block is hence *never* re-read from disk.

This is one the reason why AUM has implicit inefficiencies. This is one reason why small, few undo/rollback segments has positive performance implications.

> >
> > >
> > > >It's been around for more than a couple of years,
> > >
> > > Not really. I seem to recall teaching one of the first 9i courses in
Oz
> > > around about August last year. So just over a year and a bit. It's
still
> not
> > > totally mature, I think.
> > >
> > Really? I thought it has been around since the inception of 9i? Hasn't
> > 9i been around for more than a couple of years now?
>
> As I said, I taught one of the first 9i courses in Australia in about
August
> last year.

9i came out in late 2001. I actually taught the first Oz 9i New Features course in Feb 2002. So 2 years is just about right.

>
> > > > I would assume most of the kinks would have been fixed by now.
> > >
> > > Assuming anything is always risky! Particularly with AUM. It's pretty
> close,
> > > and I've never encountered any disasters with it, but check Metalink
> very,
> > > very carefully.
> > >
> > I actually did search Metalink and did not see any complaints about
> > AUM. One guy claimed he loaded 37 millions rows of data without
> > problems. I actually had this idea of setting UNDO_RETENTION to very,
> > very high and create a huge undo tbs. The idea is to force ORACLE to
> > use up the entire undo tbs, which will take a long time, before it
> > starts recycling the the segments. I figured this would basically cure
> > the ORA-01555. I was just not quite sure what would happen if ORACLE
> > indeed uses up a huge undo tbs. Is the flooded buffer cache your
> > personal experience or your intelligent guess?

John, the problem is of course is that this massive undo tablespace of yours and all the undo blocks associated with it has to be cycled through the buffer cache with all these undo blocks having to be written to disk. The likelihood of any of these blocks being reused in memory by subsequent transactions is low. This puts stress on the buffer cache, puts stress on the DBWRs and puts stress on the I/O sub-system.

For what ?

To prevent ORA-01555s that may not exist even with a dramtically reduced undo storage. Impacting performance by preventing problems that may not even exist is a somwhat questionable way to go ...

>
> Personal experience, having learnt it the hard way.
>
> >From your description,
> > fewer long transactions is better suited for AUM than lots of shorter
> > transactions, right?
>
> I wouldn't say that.
>

I would say that in OLTP environments, where transactions are generally short, many and (hopefully) fast, the *focus* should be on ensuring undo/rollback segments are suitable sized from the performance perspective. If (say) banks want to run 2 hour queries during lunchtimes, the implications of such requirements need to be carefully considered.

Do you want happy customers or do you want happy office clerks with upto the minute reports ?

Cheers ;)

Richard Received on Thu Nov 06 2003 - 06:12:31 CST