I agree completely with Ken! We use Uniform Extents and don't reorg unless we get ~1000 extents. We use size 128K, 4M and 128M extent_size tablespaces. We have seen some improvement in performance, but mostly we save because we don't have "unusable" space in our tablespaces. Everything is reused.
I'd be more concerned about SPACE Utilisation and Administration TIME rather than PERFORMANCE. The more fragmented your tablesapce, the more space you'll need to store the segments it contains, and the higher the change of processes falling over therfore increase time to fix the issue.
I have a case in which I have > 2000 tables truncated and reloaded each week and their associated indexes (of course are rebuilt). Not all together mind, about 400 each week day. Therefore, truncating and recreating indexes that are different sizes can can cause the process to fail because the associated index with different sized extents live in the same tablespace. Even coalescing doesn't soemtime return enough free space to recreate the object. Therefore leaving the only optioon to expand the tablespace again.
Obviously, maintaining uniform extents is the answer, but I'm just pointing out that the issue with fragmentation is more than just a PERFOMANCE issue. It is also a SPACE and TIME factor in which additional administration is required for fragmentated tablespaces... and thereby elevating my hate of teh COMPRESS=Y optiin in Exports.
I believe that changes to more recent versions have made the # of extents relatively a non-issue.
I'm with you .. I remember having a rule that we configure our tables and indexes to have no more than 5 extents over 2 years of use. I was deathly afraid of having a bunch of extents. It only makes sense to lessen the number of hops from extent to extent, right? That's got to have some additional overhead, right?
I've got a better technical explanation in a white paper somewhere around here. I'll dig through my stuff and post the details when I find them.
The performance impact of multiple extents is historic, not a myth. The amount of time to move from extent a to extent b is nothing today. Fifteen, twenty, twenty-five years ago that was not the case; the amount of time required to move the head and await the rotational delay could be the difference between acceptable and unacceptable.
I can tell you from experience, if you've gotten your burnt by something it's real difficult to 'put it in the past'.
FYI. Try uniform extents, it's a lot of work up front, but saves a huge amount of work later.
15 years ago (when I started with Oracle) the emphasis was not on transactions but on reports. Yes, we could do transactions, but the 'paradigm' was report generation.
Large numbers of small extents (the modern tendency) gives better transaction performance, but 'worse' report performance. Small numbers of large extents give better report performance but waste a lot of time and resources for small transactions.
Joseph R.P. Maloney, CSP,CDP,CCP
'The answer is 42'