Re: Database Rebuild

ocp8 wrote:
>
> 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!!

If you think you need literally thousands of 128Mb extents to make 9Gb you might want to change your calculator.

I personally look to use no more than about 20 extents on any object when I build it, but if it rises I don't worry unless it goes over a few hundred and even then I'm not going to panic. My only bad experience with extents was when trying to drop an accidentally created object with 48,000 extents in a dictionary managed tablespace, because it took 12 hours.

I think guides like use 128K, 4Mb and 128M extents are about as useful as Oracle's default sizes for small, medium and large databases. They're far too general to be useful.

I'd be looking at partitioning 9Gb tables over more than one tablespace because 9Gb datafiles are hefty to back up. If you only use one tablespace you can still tailor the extent size to that object. So use big extents just for that.

If you've got loads of 8K lookup tables maybe group those together into an 8K extent tablespace.

You need to look at the objects you've got on your database, group them up and make a decision that's right for you. But the bit that says "Start Living" is right. It really doesn't make that much difference if you use a tiny bit of common sense. Received on Tue May 20 2003 - 08:40:22 CDT