Re: Why is NEXT_EXTENT changing all the time?

> Daniel A. Morgan <dmorgan_at_exesolutions.com> wrote in message
> news:3A6FBBA2.4F17BAF2_at_exesolutions.com...
> > > I am trying (hard!) to get same size extents throughout my tablespace,
 but
> > > Oracle *8.0.5.1) won't let me do it.
> > >
> > > NEXT_EXTENT keeps changing for about 20 tables (out of 1500). Please
 someone
> > > have a look at the following sequence and tell me what I do wrong or do
 not
> > > understand!
> > > Note: I picked a bad example maybe (numbers are look alike)
> > > I want extent size = 160 (one-sixty) and Oracle changes for 168
> > > (one-sixty-eight)
> > > a) table created with extent=160k
> > > b) import data
> > > c) extent now 168
> > > d) truncate table
> > > e) extent back to 160
> > >
> >
> > I can not imagine why it is happening unless you have pctincrease set to a
> > non-zero value.. But I can not imagine why you would want the EXTENT sizes
 to be
> > the same. That is pretty much the definition of a bad design.
> >
> > Reasonable people will disagree on this but for myself, initial extents
 should
> > be sized to hold, as near as possible, 1 to 3 years worth of data
 depending on
> > the application. Next extents are generally sized, again depending upon
> > anticipated table growth, at between 25% and 100% of initial extent size.
 And
> > please please set PCTINCREASE to ZERO for all tables and indexes.
> >
> > Daniel A. Morgan
> >
>
> This is terrible advice, sorry Daniel (and I do try to be reasonable most of
> the time). Your initial extents will be largely fresh air for much of your
> three years, and you are practically guaranteeing fragmentation... As one
> who has shares in Seagate, perhaps I should encourage you to keep up the
> good work! But apart from anything else, if you look which way the wind is
> going, by about Oracle 10 (OK, maybe Oracle 15) you won't even be able to
> specify odd extent sizes -locally managed tablespaces with uniform extent
> sizes clearly being the path that Oracle is mapping out for the future.

First off it is impossible for it to cause fragmentation. An extent is part of a single segment and can not be part of multiple segments. In fact it is only the creation of multiple extents, and their subsequent emptying that can cause fragmentation.

With respect to wasted DASD you are correct. Lots of it. For the first month. Less for the second. And none by the end of the designated period. But DASD is cheap when compared to the cost of a DBA having to go in and defragment, reorganize tablespaces, and do all the other things that are later required. I know my billing rate and you can buy a lot of disk array for the cost of me doing that work.

Daniel A. Morgan Received on Fri Jan 26 2001 - 00:09:35 CST