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

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Fri, 4 Oct 2002 12:14:43 +1000
Message-ID: <1F6n9.45784$g9.130292@newsfeeds.bigpond.com>

Comments at end.

"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message news:3D9CADA0.4803_at_yahoo.com...
> 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..."

Hi Connor,

The other advantage of more/smaller extents vs. fewer larger extents (while assuming the same overall size) is that it reduces the likelihood of a blocking transaction causing a growth in the first place.

Using Howard's 6 and your 20.

With 6 extents, it's 5/6 or (wait while I turn on the calculator) 83.3% of the RBS before Oracle questions whether it can overwrite a extent. And it only requires one transaction in 1/6 or in 16.7% of the RBS for Oracle to be forced to grow the RBS.

With 20 extents, it's 19/20 or 95% of the RBS before Oracle questions whether it can overwrite an extent. Meaning it gives transactions longer to complete (by 11.7% of the size of the RBS). Also it now requires one transaction in 1/20 or 5% of the RBS for Oracle to be forced to grow the RBS. Therefore the 20 extents RBS, although the same size as the 6 extents has a reduced likelihood of extending by the above.

Still like 10 extents so I don't need to use the calculator as much ;)

And I prefer to size them as small as possible if I can.

Cheers

Richard Received on Thu Oct 03 2002 - 21:14:43 CDT