Re: Deadly sins againts database performance/scalability

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1070217768.930535_at_yasure...
> >
> >
> > As one who frequently quotes the 'disk space is cheap' mantra, just for
> > laughs, let me state that "proper" disk space is *not* cheap... EMC
aren't
> > exactly bargain basement material.
> >
> > Furthermore, RAM is definitely not cheap, and the more disk space you
have,
> > the more blocks there are which need a home in the buffer cache.
> >
> > So I'm not agreeing or disagreeing with you... just saying things aren't
as
> > simple as that, and there are subtleties to be considered.
> >
> >
> >>it makes no sense not to just assign the equivalent of 1 or
> >>more drives, often 40+GB to UNDO and let it run to comletion.
> >
> >
> > Bear in mind the Oracle automatic undo algorithm: the more space you
assign
> > to the undo tablespace, the more undo segments you end up acquiring. The
> > more undo segments, the more undo segment header blocks. The more undo
> > segment header blocks, the more Oracle overhead there is to manage the
> > thing. And the more undo blocks in general, the bigger your buffer cache
had
> > better be.
> >
> > The "sense" in automatic undo is that left to its own devices (ie,
> > implemented poorly) it can consume resources at a rate of knots.
>
> A fascinating phrase. Please explain "rate of knots." It is new to me.

Speedily.

>
> I'm not writing some new nonsense mythology that goes "NEVER commit in a
> loop." I'm meerly arguing that it should be done if required. And "if
> required" means you've tried it without and can't make it work. Lets
> remember that almost every post on UNDO, including yours, have advocated
> pushing the undo retention out as far as possible to enable using
> DBMS_FLASHBACK for recovery.

That's actually not true. I did, for a time, recommend huge undo tablespaces precisely because I liked the idea of being able to flashback almost as far in the past as you could ever want to. But I quickly stopped suggesting that when Jonathan (I think) pointed out the impact of vast amounts of empty undo space on the 'allocate this new transaction to a new undo segment' algorithm. A bit of testing confirmed that I had just made my biggest-ever mistake in understanding Oracle. For the past year and more, I have therefore been saying that automatic undo needs to be set rather carefully, because otherwise you will swamp your buffer cache with Oracle overhead (ie undo segment headers for starters). Obviously, there is a tension between 'minimal but adequate' and 'I'd like to do flashback' (which is one reason why I think you'll find Jonathan doesn't like flashback very much). I actually like flashback a lot, and would arrange things accordingly... but like everything good in Oracle, it comes with a cost, and that cost needs to be monitored and tuned for.

> In the end ... the compromise to be made ... is between the cost of
> hardware and the cost of an ORA-01555. I'll continue to argue that the
> hardware is far less expensive in almost every situation.

But ORA-1555 can be solved with a much more modest and realistic setting of undo_retention than you suggest I've been recommending in the past.... how long is your longest query (answerable from v$undostat) should govern undo_retention. Not "push it out as far as possible" or throw entire hard disks at it.

In short, over-doing the undo tablespace can limit scalability and performance because you can kiss goodbye to rather a lot of your buffer cache in the process. And I've been consistent in saying that for a very, very long time.

Regards
HJR Received on Sun Nov 30 2003 - 13:16:43 CST