Re: Reorg Oracle 8 Partition tables

Thomas Kyte wrote:

> but steve, in my simplistic example -- I had MORE adjacent blocks they you would
> (i had 10,000 extents -- each extent is contigous, a correctly sized extent
> would have have MORE extents and extents are contigous so in theory, given you
> data -- perhaps you are right -- 6% is wrong, my example might go FASTER even
> though the extent size was NOT a multiple of the multi block read count :)
>
> >*all* the I/O's for a full scan - and properly sized, localised extents will always
> >be the best choice for performance.
> >
> disagree with the localized, agree with properly sized.
>

Sorry, you've lost me completely here! :-)

The only blocks (hence disk sectors) that are guaranteed contiguous are those blocks of a *single extent*. If I had one, correctly sized extent (or a smallish number thereof), then that gives me minimal head movement on sector to sector disk reads. Each allocation of our - admittedly extreme - 10,000 extent example could be quite some distance away from it's predecessor. Hence excessive fragmentation becoming a problem.

Sure, for a *single multi-block read examined in isolation* this makes no difference, but when you consider that 10,000 extents could result in 10,000 multi-block reads (and even this assumes the extents were 'perfectly' sized from the multi-block read count perspective), you still have the potential overhead of moving to 10,000 widely dispersed disk sectors which mark the start of each extent. This, of course, assumes that the object grew dynamically over a longish period of time as data was slowly added to it.

In the case where the 'min extents' parameter was set to 10,000 and the allocation occurred as the object was first created then the effect of 'extent fragmentation' could be absolutely minimal, as your example suggests, but only as long as the containing tablespace was not already heavily fragmented, forcing dispersed allocation of extents to this newly created object.

It's still worth reiterating that all of this applies only to large sequential scanning, which may be a complete irrelevance to most systems. My 'take' is a little biased, as I work with 1TB worth of Oracle datawarehouses, where we spend most of our time with very few users but very, very large sequential scans (partition, multi-partition or full). We have seen very poor performance from objects which have fragmented over 1000's of extents because the objects were never designed correctly by developers.

>
> you are forgetting parallel query (would want many extents all over the place in
> order to force the io to get spread out). I want many extents, i want them all
> over the place.

Not necessarily. See my note above on DW style databases. Sequentially allocated extents which have been striped at the hardware level across multiple disk drives will give you the best performance (as long as you don't start flooding adapters, CPU, etc). And you could take the alternative route that Oracle recommends if you don't have any hardware for striping, which is to allocate a *small number* of *large contiguous extents* specifically sized to overspill into datafiles placed on different disk drives. I would only suggest (or use) this as a last resort, though, as the micro-management of disk space allocated in this manner is burdensome in production.

>
>
> you are forgetting that in virtually every case (at least in my experience),
> there is more then one person logged into the system at some point (you don't
> own the heads, they are all over the place anyway. as soon as you have more
> then 1 user, disk contention is a fact of life).
>
> with the wide spread use of raid and other technologies (striping, solid state,
> etc), this kind of fine tuning returns faster and faster diminishing marginal
> returns (thankfully).

Again, this is true for the majority of non-DW style database systems. But it pays to be careful nonetheless, and real-world testing of your own system is the only way of getting true results, I'm sure we'd both agree.

Still, an interesting and fun debate, and one that more Oracle database 'designers' should note for future reference. As you said in your initial reply, there still exists a paranoia over squeezing objects down into one (or less than 5) extents, which is futile. I was merely trying to add the point that people should not automatically jump to the opposite extreme and believe that any number of extents is always too few. As in most things, moderation, not abstinence or indulgence, is the key. :-)

Regards,

Steve Phelan

(Oralce 7 & 8 OCP) Received on Sun Feb 21 1999 - 05:44:23 CST