how does having alot of extents hide fragmentation?
"Antoine BRUNEL" <antoinebrunel/yahoo.fr> wrote in message
news:3ecaa0a8$0$22241$79c14f64_at_nan-newsreader-01.noos.net...
> Hi from Paris
>
> there is a performance myth about having large number of extents for a
given
> object... this is even falser with locally managed tablespace, in the the
> fact blocks are being managed with bitmap, and no more with SYS fixed
> tables.
>
> One true thing, is that having many extents can hide a real fragmentation
> problem on tables/indexes: rows fragmentation (chained / migrated). This
is
> why peoples who had rebuilded objects into 1 extent might have seen a
> performance increasement, but this was only because migrated rows were
> restored.
>
> Believe my experience, performance impact of many extents is really,
really,
> really, ... not an important thing (unless, as said, you have several
> thousand extents in a dictionnary based tablespace, and even yet....)
>
>
>
>
> "Ryan" <rgaffuri_at_cox.net> a écrit dans le message de
> news:_Owya.46953$823.34870_at_news1.east.cox.net...
> >
> > "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> > news:iwvya.38822$1s1.549713_at_newsfeeds.bigpond.com...
> > >
> > > "ocp8" <n_stimely_at_yahoo.com> wrote in message
> > > news:4714c80f.0305200514.76f545c4_at_posting.google.com...
> > > > Hello..
> > > >
> > > > I am looking to rebuild my 80 gig database with an 8k block in the
> > > > next couple of weeks and doing a reorg at the same time. I am
> > > > implementing locally managed tablespaces with a uniform extent size.
> > > > I would like to use the advice I found in the article "How to Stop
> > > > Defragmenting and Start Living: The Definitive Word on
Fragmentation".
> > > > My question is what extent sizes to use? The article recommends 3
> > > > sizes: 128k, 4m & 128m. If I have a table that is 9 gig in size it
> > > > would literally use thousands of extents using an initial and next
of
> > > > 128m. What sizes would be the best to use? I have tables ranging
> > > > from 260k to 9g. Thank you for your advice!!
> > >
> > > You'll get conflicting advice on this one, I suspect. There is still
> very
> > > much an 'old school' out there, who somehow seem to feel deprived of
> > > something if they don't fine-tune their extent sizes, and accordingly
> > > recommend such wondrous extent sizes as 8K. They also have a curious
> > > reluctance to accept that, except when you get into the
multi-hundreds,
> > the
> > > number of extents an object acquires is of absolutely no concern
> > whatsoever
> > > these days (with practically unmeasurable performance impact, at
least).
> > >
> > > This is, however, a sad approach to take, since it completely negates
> the
> > > entire purpose of LMT, which when all is said and done was to free
DBAs
> up
> > > from having to worry about space allocation at too-fine-grained a
level.
> > >
> > > If you look at Oracle's own auto-allocate policy for LMTs, it uses
> extent
> > > sizes of 64K, 1M, 8M, 64M and 256M... and I'd suggest you use those
> > > yourself, even if you don't go all-out and simply use autoallocated
> LMTs.
> > > I'd use autoallocate in 9iR2, but I'm not convinced it was
bullet-proof
> > > before then... and I"d not whole-heartedly recommend it even in 9iR2,
> > simply
> > > because one can't be sure it's bullet-proof even now. The code is
clever
> > and
> > > mysterious, and as a result, could be doing strange things!
> > >
> > > That would imply 36 extents of 256M for your 9GB table, which is a
> > perfectly
> > > healthy number. Even at 64M, you'd only need 141 extents, and that's
not
> a
> > > concern either. At 8M, you'd be looking at 1125 extents, and that's
> > starting
> > > to be a little nerve-wrackingly high... but still won't cause the sort
> of
> > > problems the same number of extents in DMT would have done.
> > >
> > > But, put basically, what was good enough for Oracle itself is probably
> > good
> > > enough for any of us, and so I'd run with that set of extent sizes.
> > >
> > > Regards
> > > HJR
> > >
> > >
> >
> > I have segments with 800+ extents. This is done so we can keep the
> datafile
> > as small as possible. This is because we have to copy the datafile
several
> > times in order to transport the tablespace and we need to trim time off
of
> > it.
> >
> > All we do with it are batch loads and then the users can do queries. I
> read
> > somewhere that as long as we keep Extents under 1024 its ok. I know you
> can
> > get UET$ and FET$ conflicts if you are on a high transaction database.
> > Anything else I should worry about?
> >
> >
>
>
Received on Tue May 20 2003 - 20:09:17 CDT
