Re: Reorg Oracle 8 Partition tables

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 *all* the I/O's for a full scan - and properly sized, localised extents will always be the best choice for performance.

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 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....
Received on Sat Feb 20 1999 - 08:05:15 CST