Re: Database Rebuild

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 - 16:39:51 CDT