Re: Database Rebuild

"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 Received on Tue May 20 2003 - 14:51:54 CDT