Re: NOARCHIVELOG and rollback segments

"John" <jbradshaw777_at_yahoo.com> wrote in message news:f2f59d82.0311061629.28b135be_at_posting.google.com...
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
news:<3faa06d2$0$9913
>
> Is the flooded buffer cache your
> > > personal experience or your intelligent guess?
> >
> > Personal experience, having learnt it the hard way.
> >
>
> Man, going through the combined input from Richard Foote and HJR is no
> small task. What you guys said make a lot of sense. Still, can I pry?
> I enjoy listening to stories. Eaxtly what happened in your case? It's
> Ok if you don't wish to go into the details.

Oh, there's no particular secret:. When flashback first made an appearance, I thought it was a "really good thing" (I would have killed for it back in the early 90's for one particular application I had a hand in designing). So I tended to recommend humungously large undo tablespaces, just in case you fancied flashing back to the way the data used to be, say, a couple of days ago. But I'd forgotten the first rule of Oracle: nothing is free. When there's a benefit to be had, there's usually a cost to be incurred too. I should have known better.

I think it was Jonathan Lewis who first mentioned on this very newsgroup that that might not be the soundest piece of advice I've ever come up with. So I went back and checked. And sure enough, a large proportion of my buffer cache was full of undo blocks and segment headers (I can't remember the exact quantity, but it was getting on for 11% of the entire cache, I seem to recall). So I shrunk the thing back to reasonable size, and the overhead came down accordingly (testing after a week revealed about 1-2%, IIRC). That's a lot of memory to kiss goodbye to just for Oracle's internal housekeeping.

Of course, it's a price you might be prepared to pay if flashback is truly key to your application needs.

Stupidly-configured automatic undo combined with ASSM.... time to buy some new RAM!!

Regards
HJR Received on Thu Nov 06 2003 - 18:45:06 CST