| 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.n-o-s-p-a-m.demon.co.uk>
(if that email address didn't require changing)
On Sat, 20 Feb 1999 14:05:15 +0000, you wrote:
>Hmm...
>
>I don't think you can equate those 'extra I/O' operations with a 6% loss in
>performace. The assumptions made in the calculations are far to simplistic, and
>don't mirror the true performance characteristics of modern disk drives. A large
>number of fragmented extents across a drive has much more impact than just the extra
>I/O's required to implement Oracle's multi-block I/O reads.
>
>For instance, a latest generation Seagate 50G Barracuda drive takes *7 times* longer
>to perform average track seeks over simple track-to-track seeks. Seeking across the
>entire span of a single platter could take *15 times longer* than adjacent block
>seeks. It's not just the number of I/O's that matter, it's the cost of executing
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.
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.
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).
>A contiguous series of extents, with a high degree of locallity, could easily
>outperform a full scan of a 10,000 scattered blocks by a large factor - much, much
where are you getting 10,000 scattered blocks from? I had 10,000 extents of 1meg+2k all contigous.... my blocks *are* contigous (they are in a badly sized extent), it only took 1 extra io per extent to read? no seeking in a 'dust free laboratory' environment where I am the only user logged in and have complete control over every faucet of the disk arrays.
while I agree that 10,000 extents would be a thing you would want to look at, i've let to see someone with a table in 10,000 extents (many hundreds -- yes, low thousands, not yet but I'm sure there is one out there somewhere).....
>greater than 6% (in fact probably 10 x or more). It's for reasons like these that
>many LVM's actually let you choose exactly where you want disk partitions
>allocating, to make the best use of the drives fastest tracks. And it's why
>applications (and OS's) so quickly degrade in performance once fragmentation starts
>to bite. Oracle is not immune to this.
>
>But sure, the paranoria over 'two extents is already one extent too many' is crazy.
>Still I wouldn't be happy with extent fragmentation that ran over many, many extents
>unncesasarily. Like my other reply on this matter, the extent policy you use has a
>lot more to take into account than just raw I/O performance.
>
>Regards,
>
>Steve Phelan.
>
>(Oracle 7 & 8 OCP)
>
>
>>
>> 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....
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |