Re: Database Rebuild

"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:08:10 CDT