Re: Any Final Verdict on Nr. of Extents?

"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

Reza, the correct answer depends on several factors.

1-Are all the extents for the index the same or different sizes? 2-Are other objects defined in the tablespace, and what extent sizes are they using?
3-If not currently using locally managed space with uniform extents is it desirable to convert to this feature
4- Are deletes done from the underlying table. If so how much of the existing index is held by deleted entries that a rebuild can reclaim (resulting in a more compact, more effecient index) 5- Are the tables/indexes involved read by full table scan/full index scans. If so is the extent size a multiple of the multi-block read size or does every extent require an extra IO to get the last block, i.e., poor sizing? (Note IO requests do not cross extents so if the multi-block is 10 and extent size is 15 then 2 IO requests are required. In this example any even multiple of 10 blocks is a desired extent size to support full scans)
6- what is the database block size, 2K, 4k, 8k?

The answers to the first two questions can identify a tablespace fragmentation problem where your system is wasting large amounts of allocated space.

Item 3 is probably the best way to manage space and appears to be Oracle's current direction on the subject.

Item 4 - If any of the indexes were built on columns that have their data value changed several times over the life of the item and there are deletes against the item then it is very possible for indexes like this to take 3X the number of extents to hold them as they need after a rebuild.

Item 6 affects how many extents can be kept tract of before a second extent management block has to be defined. I have read that one extent management block is better, but this is still alot of extents for larger block sizes.

If properly sized the number of extents will have little effect on DML statements so if the DBA's are performing the maintenace just to reduce the number of extents then yes they are probably wasting time. On the other hand if they are trying to reclaim wasted space to reduce tablespace and/or object level space fragmentation then resizing the extents during the reorgs would make sense.

So as with most things related to Oracle the answer is, it depends.

• Mark D Powell --