Re: Any Final Verdict on Nr. of Extents?

It doesn't make a hell of a lot of difference, but there are qualifications to that.

Extents are recorded in some data dictionary tables. They are sized to expect around half a dozen extents per segment. Any more than that, and you induce chaining on the data dictionary (assuming that you are not using locally managed tablespace). That can start to slow data dictionary operations down (particularly drops and truncates).

The extent map is also held by each segment in a block. For 2K blocks, that means 121 extents can be recorded in the one block (249-ish for 4K blocks, and 505 for 8K blocks). Any more than that, and a read of the extent map for a segment has to be accomplished by reading extra extent map blocks. That's additional I/O, and is a performance degradation (but I doubt you'd notice it until you got into the thousands of extents range).

There is zero benefit in having a single large extent. The fact that extents are not contiguous *might* have an impact on full table scans, but if your access to those tables is via an index, there is zero performance impact.

So: 1 is OK, but don't strive officiously to achieve it. 6 or so is good. 121 (or 249 or 505, depending on block size) should be considered an absolute upper limit.

Unless you are using locally managed tablespaces, in which case we honestly couldn't give a monkeys about the number of extents.

And fragmentation has absolutely nothing to do with the number of extents, nor whether they are contiguous within the tablespace. Fragmentation is simply the presence of lots of small pieces of free space within the tablespace, none of which can be used by existing segments, because their NEXT EXTENT settings are of the wrong size. Fragmentation is prevented by having everything within a tablespace come in the same sized extents. It's not an issue of the *number* of those extents at all.

Regards
HJR "Reza" <reza_at_digital-dispatch.co.uk> wrote in message news:999771696.4333.0.nnrp-09.d4f07ab4_at_news.demon.co.uk...
> Hi guys
> I've been having a hot debate with our new say DBAs, on effect of Number
> extents in an object like a table or index on performance.
> Basically I didnot agree to take a 7x24 system down for 6 hours to just
> reduce the number of extents on few huge tables and their indexes.
> I would personally believed few years ago that lower number of extents
will
> be better performance and even remember arguing this with some Oracle
Tutors
> in their lectures. Since then I have somehow revised my view due to
working
> with few production databases, and also hearing more from other DBAs in
> favor of ineffectiveness of Nr. of extents in Performance.
>
> Anyway I would appreciate if anyone could direct me to a definitive
article
> or somehow share their view on this so I can supprt my argument better or
at
> list to revise it again.
> I think somehow fragmentation problem might come to this argument as well,
> if the extents are not contiguse in the Objects table space. Any view on
> this aspect.
>
> May thanks for any help or feedback.
> Best Regards
> Reza Oskouie
>
>
>
Received on Thu Sep 06 2001 - 15:49:09 CDT