Re: Deadly sins againts database performance/scalability

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Sun, 30 Nov 2003 10:42:18 -0800
Message-ID: <1070217768.930535@yasure>

Howard J. Rogers wrote:

> "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1070150069.551425_at_yasure...
>

>>Mark D Powell wrote:
>>
>>>You can disagree, but I think your are failing to consider how much
>>>real world work is done in batch type processing.
>>>
>>>-- Mark D Powell --
>>
>>And I will. I remember the first time I was a Boeing and wrote code that
>>did that. Seemed reasonable at the time as I was bringing in gigabytes
>>every weekend. But now with UNDO I'd argue just the opposite. Disk is so
>>inexpensive

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

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.

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.

-- 
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 - 12:42:18 CST