Re: Defragmentation

From: Howard J. Rogers <howardjr_at_www.com>
Date: 2000/06/13
Message-ID: <39461f41@news.iprimus.com.au>#1/1

"Niall Litchfield" <niall.litchfield_at_doial.pipex.com> wrote in message

news:8i4tcu$4va$1_at_soap.pipex.net...

> "Howard J. Rogers" <howardjr_at_www.com> wrote in message
> news:39449619$1_at_news.iprimus.com.au...
> >
> > ""marco pinzuti"" <mpinzuti_at_virgilio.it> wrote in message
> > news:A5B2E7DDB0044D1178C40005B83A896E_at_mpinzuti.virgilio.it...
> > If you have at least Oracle 8, you can set MINIMUM EXTENT for a
> > tablespace -nothing you specify at segment level can override it, and
 hence
> > you are at least assured of a few standard extent sizes within the
> > tablespace, rather than a whole bunch of completely random sizes.
>
> IIRC (and I can't be bothered to go to the docs because we just run with
 std
> extent sizes).doesn't MINIMUM EXTENT just specify the min value for
 INITIAL
> & NEXT and so will cause random sizes above a certain value. IMO you
 should
> not be specifiying storage at the segment level but at the tablespace
 level
> (with INITIAL=NEXT and PCTINCREASE 0).
>
>

Agree with your last sentence. I always advise against segment-level specification of extent sizes. However MINIMUM EXTENT is *not* as you describe. If you set MINIMUM EXTENT to, say, 500K, then if you were to create a segment specifying INITIAL=40K, you would have a 500K extent allocated regardless (because it is designed NOT to be overridden by segments). However, if you specified an INITIAL of, say, 600K you will actually be allocated an extent of 1M. And if you asked for an extent of 1200K, you would be given one of 1500K.

In other words, you always get extents of size MINIMUM EXTENT *or multiples thereof*, and the system always rounds up to the next multiple -not the nearest multiple.

What you end up with, as I said earlier, is a limited variety of extent sizes (500, 1000 and 1500K and so on) -which is at least better than a whole bunch of -eg- 43K, 57K, 150K, 325K, 73K, 64K and so on and on and on ad infinitum.

Nothing random about the extent sizes at all, in other words.

Incidentally, what does IIRC mean?

Regards
HJR
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
>
>
Received on Tue Jun 13 2000 - 00:00:00 CDT