Re: undo header congestion? (8.0.5)

"Bjørn Augestad" <boa_at_metasystems.no.spam.to.me> wrote in message news:unJRb.31572> >>>

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

Not at all. I had to create 250 of the buggers once. 12 is mere peanuts! I'd go 13, though: always round up.

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

Which is classic rollback segment contention symptom, so yes, you need extra segments to deal with your peak load. It means a lot of wasted space (depending on the size of them, of course), but (cue chorus) "disk space is cheap". If you do some strenuous activity in well-defined times, you can keep the spare rollback segments offline, and bring them on-line when needed. But if you can't predict when the batch activity happens, then it's best to keep all of them available all of the time.

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

Ooops. Sorry... I should have read ahead, and then I needn't have tried teaching you to suck eggs. We are of the same mind, then.

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

-- 
------------------------------------
Oracle insights at www.dizwell.com
------------------------------------