Re: NOARCHIVELOG and rollback segments

"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.

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

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

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.

Regards
HJR
> John
Received on Thu Nov 06 2003 - 02:31:09 CST