Re: Best/Only way to change RBS initial/minextents

Howard J. Rogers wrote:
>
> "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
> news:YWLm9.45044$g9.128427_at_newsfeeds.bigpond.com...
> > "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> > news:miLm9.45024$g9.128459_at_newsfeeds.bigpond.com...
> >
> > ...snip...
> >
> > >
> > > You're doing it hard. What you want to do is to avoid segment growth or
> > > shrinkage. Optimal encourages shrinkage (hence, don't set it). And
> trying
> > to
> > > create pretty, perfectly-formed, small rollback segments is likewise an
> > > encouragement to growth. If you can spare the disk space, create them
> all
> > > quite large: say, 6 extents of 8Mb each. If you want your OCP, then 20
> > > extents is the right number -and I'd still say they should be each 8Mb
> > big.
> >
> > ... Snip...
> >
> > > Question 5: What's 1Gb divided by 6 extents? Er, er, er... damn I wished
> I
> > > passed A level Maths!... er, 180K. Or thereabouts.
> > >
> > > Therefore: create rollback segement RBS01 storage (initial 180K next
> 180K
> > > minextents 6) tablespace RBS;
> > >
> >
> > Hi Howard,
> >
> > I agree pretty well agree with what you've said/recommended except I have
> a
> > question about the above 6 extents suggestion.
> >
> > Why 6 ? Why not 5, or 8, or indeed 20 extents ?
>
> For the simple reason that extents are recorded in a cluster in the data
> dictionary that expects no more than 5 extents. Read your sql.bsq.
>
> >
> > Personally I usually go for 10 in that 10 is an easier number to use in
> any
> > calculations, so 10 x 100M (as my maths is always questionable at the
> best
> > of times ;) It also gives the RBS plenty of time to work it's way around
> in
> > case of blocking transactions (900M) and increasing in nice healthy chucks
> > (100M) to avoid excessive allocations if necessary.
> >
> > Just curious where you come up with 6 ?
>
> Fair enough. If I was being strict, I should say 5 (see the SIZE parameter
> for the segment that holds the inserts for UET$ in sql.bsq).
>
> But you're right: in LMT, whatever the size of the UET$ cluster is
> irrelevant anyway. And besides, the "5" rule only kicks in in the worst
> possible scenario. If you are the only one doing extent acquisitions on a
> single segment, then about 180 or so is possible before you start chaining
> on the UET$ cluster (thank you, Jonathan).
>
> I wouldn't spit the dummy if you said 10.
>
> Anyway, the simple answer as to where I got "6" from is: The Site of Steve
> Adams (praised be his name). The truth is that it can be a deal more
> flexible than that, but it doesn't need to be.
>
> However, Connor has different ideas.
>
> Connor????????
>
> Regards
> HJR
>
> >
> > Cheers
> >
> > Richard
> >
> >

6 means an extension adds roughly 17% to the size of the segment, whereas 20 would add 5% to the size. Assuming an LMT where the penalty for extension is negligible, then I'd rather add in 5% increments. Admittedly I normal present this as an argument against "minextent 2" where you get clobbered with a 50% jump in segment size - so I presume the cutoff point depends on what your favourite percentage figure is :-)

Connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."