Re: undo header congestion? (8.0.5)

From: Bjørn Augestad <boa_at_metasystems.no.spam.to.me>
Date: Wed, 28 Jan 2004 07:18:50 GMT
Message-ID: <unJRb.31572$BD3.7011381@juliett.dax.net>

Howard J. Rogers wrote:

> "Bjørn Augestad" <boa_at_metasystems.no.spam.to.me> wrote in message
> news:2ofPb.30977$BD3.6794887_at_juliett.dax.net...
>

>>Sybrand Bakker wrote:
>>
>>
>>>On Tue, 20 Jan 2004 15:47:40 +0100, Bjorn Augestad
>>><Bjorn.Augestad_at_bokklubbene.no> wrote:
>>>
>>>
>>>
>>>>Some additional info: instance 1 has two rollback segments, and instance
>>>>2 has just one (+ system).
>>>
>>>
>>>
>>>The number of rollback segments should be set according to the rule of
>>>four: per 4 users 1 rollback segments, with a minimum of 4 rollback
>>>segments, and rounded up.
>>>Consequently both instances have too few rollback segments, which is
>>>confirmed by the number of waits on the undo header block.
>>>
>>
>>Thank you for answering. :-)
>>
>>Would you(or anyone else) consider it a serious case of misconfiguration
>>to set up an instance with just one/two rollback segments, assuming
>>25-50 users?
>>
>>Regards,
>>Bjørn
>>

>
>
> No, because whoever told you the rule was 'per 4 users 1 rollback segment'
> has got it slightly, but horribly, wrong. The number of users is completely
> irrelevant. It's the number of concurrent transactions that is important. So
> instead of doing a select count(*) from v$session, you would be better off
> doing a select count(*) from v$transaction. Whatever that number is, divide
> by four and that's your starting position for an OK-ish number of rollback
> segments. If the number of 'proposed' segments is incredibly high, feel free
> to degrade the ratio a little, down to 1 maybe per 10 concurrent
> transactions.

My mistake, sorry. The actual number of concurrent users is supposed to be around 400, and we want to be able to handle approx. 50 concurrent transactions.

This means that we need 12 RBS instead of one/two. Not incredibly high, is it?

>
> But back to your question: 50 users, four or five of them concurrently doing
> DML? Sounds like 1 or 2 rollback segments would do just fine.

Statspack indicates that 1 or 2 RBS is fine most of the time, but from time to time we spend a lot of time waiting for "undo header".

One of the instances is a backend db server for a web site. The load tends to spike a lot during the day/week/year. I'd therefore rather have too many rollback segments than too few, even if it wastes some disk space. We also have issues with e.g. blocking transactions and clients hanging, and I want to be able to rule out RBS shortage as the cause for those problems.

Regards
Bjørn

>
> Regards
> HJR
Received on Wed Jan 28 2004 - 01:18:50 CST