Re: Tutorial about STORAGE parameters

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Sun, 02 Mar 2003 11:34:12 +1100
Message-ID: <pan.2003.03.02.00.34.11.891139@yahoo.com.au>

On Sat, 01 Mar 2003 14:29:52 +0100, Frank wrote:

> Howard J. Rogers wrote:

>> An excellent suggesiton from Norman, but I would want to qualify that
>> advice by saying that the extent sizes proposed in the paper he
>> references are distinctly odd, and you'd be much better off creating
>> extent sizes following the same sort of algorithm that Oracle itself
>> uses with auto-allocated locally managed tablespaces... namely, 64K
>> etxents, 1MB extents, 8MB and 64MB for the really big tables.
>>
>> I can't see any possible need for any other extent sizes (though I have
>> occasionally been known to ignore my own advice and slot a 512K extent
>> tablespace in between the 64K and 1M ones).
>>
>> Regards
>> HJR

> But there is a logical relation between the sizes: they all are a factor
> 32 apart; your 64k - 1M - 8M - 64M is odd in that respect: 16 - 8 - 8 -
> 8.

Not my sizes: it's what autoallocate does!

> Following the rule, described in the article mentioned would result in
> 64k/2M/64M. For most of the databases, the 64k and 2M will be enough;
> the 64M tablespace would harbour segments over 2GB.
> 
> The document suggests extent sizes of 128k - 4M - 128M for 8.0 onwards,
> fail to see what is odd about that.

"Odd", meaning that 128K is going to be rather large for many segments. Obviously, you have to start somewhere, but I like 64K because it's not *too* big for the small segments, but is big enough to not cause huge numbers of extents for moderately large segments, either.

Likewise the leap from 4M to 128M is a big one. 1M to 8M is (as you point out) much smaller... and the finer granularity means less wasted space for particular segments.

The issue is that ultimately, any such approach is going to be a 'one size fits all' policy, with a compromise to be struck between ease of configuration and excessive space wastage for those segments where the 'one size' isn't quite appropriate. I like the autoallocate sizes because I think they strike the right balance. I don't like the paper's proposed sizes because I think they over-do it, and result in space wastage worse than any degree of fragmentation.

But it's not critical, I guess.

Regards
HJR > Regards, Frank van Bortel Received on Sat Mar 01 2003 - 18:34:12 CST