Re: Deadly sins againts database performance/scalability

Comments in-line.

Howard J. Rogers wrote:

> "Daniel Morgan" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
>
> > > 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.

I apparently missed the change. A year ago you say. Hmmm. Well I've been 
 building most OLTP systems with 24 hour undo retention and have yet to 
find a real-world penalty. Thanks for the heads-up. I will keep my eyes 
open a bit more.

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

My use of undo retention at 24 hours has been based on the backup cycle 
rather than specifically addressing ORA-01555 which usually requires not 
more than 30 minutes.

> 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

Not doubting you in any manner I went back to the Google archives and 
found this:

===================================
From: Howard J. Rogers ([EMAIL PROTECTED])
Subject: Re: How to calculate the optimal size for undo segments
View: Complete Thread (6 articles)
Original Format
Newsgroups: comp.databases.oracle.server
Date: 2003-03-07 04:17:01 PST

Of course: you can't measure these things until you've already got an 
undo tablespace, so what you are supposed to do is to create a massive
tablespace that you reckon will contain the worst case you can possible
imagine, and let your users do their thing.... that populates 
v$undostat, and you take it from there, sizing the tablespace down or up 
as the case may be.
===================================

It was in that thread that you wrote:

"So there's a balance to be struck between oodles of undo space and a
working buffer cache."

So being a Yank I went for "massive" as I didn't understand what an 
oodle is. Thanks for the clarification.  ;-)

--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
[EMAIL PROTECTED]
(replace 'x' with a 'u' to reply)