Re: LMT advice

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Sat, 27 Sep 2003 10:41:40 +1000
Message-ID: <3f74dd7c$0$6610$afc38c87@news.optusnet.com.au>

Daniel Morgan wrote:

> Howard J. Rogers wrote:
> 

>>Daniel Morgan wrote:
>>
>>
>>
>>
>>>You've almost got me convinced. So now what is the difference in
>>>overhead between using autoallocate
>>>pushing out multiples of 64K 1000 times per hour and one large extent
>>>per hour when working with a
>>>table doing a large number of inserts? Aren't we talking about more hits
>>>on the bitmap which means
>>>writes to the datafile's header?
>>>
>>>
>>>
>>
>>
>>I don't follow. There's 64K of tablespace bitmap (ie, 8 blocks on an 8192
>>bytes blocksize systen). If you want to allocate an 8M extent to a segment
>>(and bear with me on my maths here) you simply have to flip 128 bits in
>>one of the bitmap blocks. If you want to allocate a 64K extent, you just
>>flip one bit.
>>
>>Your table still gets 8M extents, or 64M extents or whatever, as far as
>>its concerned. It's just the number of bits which get flipped to record
>>thOse different extent sizes that changes.
>>
>>The difference in workload between flipping 128 bits and 1 bit is not
>>going to be measurable. There's no real extra I/O involved in the one case
>>as compared to the other.
>>
>>Regards
>>HJR
>>
>>

> I'll buy in. So what is the downside, if any, with UNIFORM? >

None whatsoever, except that you have to decide on what the uniform size should be. I tend to recommend 64K, 1M, 8M and 64M, but 'How to stop worrying about fragmentation and start living', or whatever that Metalink whitepaper was called, decided some other sizes would be good. Who can tell what sizes are good and what aren't?

And then there'll always be someone who thinks 428K is a good uniform size!

Do you remember the bad old days of trying to get segments into a single extent size? And then worrying about the number of extents? And so on: all that hokey stuff should be consigned to the dustbin of version 5 history, and the more we can get away from worrying about things like that, and concentrate on the stuff that counts, such as crappy SQL or chronically over-normalised design, the better for everyone, I think.

Autoallocate is set-and-forget (which is always a worry, and why I was very reluctant to recommend it for a very long time... the bad taste that was PCT_INCREASE will take an extremely long time to go away!!). But lots of testing here, anyway, indicates that as automation goes, autoallocate's pretty darned sexy.

But maybe I must just get out more!

;-)
HJR Received on Fri Sep 26 2003 - 19:41:40 CDT