| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Reorg Oracle 8 Partition tables
A copy of this was sent to Steve Phelan <stevep_at_toneline.demon.co.uk>
(if that email address didn't require changing)
On Fri, 19 Feb 1999 20:58:15 +0000, you wrote:
>So 10,000 extents, all just a bit under the multi-block I/O count * block
>size, on a partition that is predominantly full scanned is not a problem,
>then?
>
well, they never said "10,000"....Just said "increasing in extents" I guess the followup should have said:
as long as the extents are even multiples of your multi block read, don't worry about having lots of extents. Lots of extents affect the performance of certain DDL operations in 8.0 (like drop but how many times do you drop a table?) and below (but not so in 8i with locally managed extents, a new feature) but they don't affect:
I guess if I have a table in 10,000 extents and lets say the extents are small, say 1meg+2k, and my multi block read is 64k (max on most OS's), it'll take 16 IO's (16 ios * 64k = 1024k = 1m) to read an extent PLUS 1 extra IO to pick up that nasty 2k 'extra' piece.
My object is 10,000 meg + 10,000*2k in size or 10,020 meg apparently.
So, it would take 10,000*16 + 10,000 = 170,000 ios to full scan it.
If I had taken the time to right size, and made the object with perfect 1meg extents, it would take 160,320 ios to read 10,020 meg of data.
So in a worst case scenario, it might add 9,680 ios which sounds like alot but its only a 6% difference (160,320 is 94% of 170, 170,000 is 106% of 160,320) so, maybe its not that horrible after all (i mean, taking the time to fix this problem would only ever increase full scan performance by 6%)?
As the size of the extents go up, the percentage overhead this "slip up" adds gets smaller...
Yes, if you exagerate the example and make the extent size really small -- the percentage goes up. Just trying to point out that on a table in 10,000 extents, 10,000 extra ios just might be noise....
>Or is that just 'predominant thinking'?
>
the predominant thinking, and I see it lots, is that you have to constantly be striving to have objects in a single extent for some reason. thats wrong. Its not true. Its a myth. Ignore it. Let them grow.
I agree with Peter. The original poster should really ask themselves "why am i doing this" to see if it *really* needs to be done.
>Steve Phelan.
>
>(Oracle 7 & 8 OCP)
>
>
>Peter Sharman wrote:
>
>> Kevin
>>
>> Before re-orging I would have to ask why are you doing this. Having an
>> object in multiple extents is NOT a problem, regardless of the predominant
>> thinking that it is.
>>
>> HTH.
>>
>> Pete
>>
>> Lo wrote:
>>
>> > Help,
>> >
>> > My partitions are increasing in extents, and I will need to re-organise
>> > them. I need to know which is the easiest way to do it.
>> >
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |