Re: Deadly sins againts database performance/scalability

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Sun, 30 Nov 2003 13:47:43 -0800
Message-ID: <1070228896.855674@yasure>

Comments in-line.

Howard J. Rogers wrote:

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

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 (howardjr2000_at_yahoo.com.au) 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
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Sun Nov 30 2003 - 15:47:43 CST