RE: Number of extents . . . does it matter?

Except for very small tables, extreme rates of dynamic extension, and online drops of dictionary managed objects with large numbers of extents, the number of extents has nearly no impact on performance.

Consider your case: What is the probability that the boundaries between 20 extents will interrupt what otherwise would have been a multiblock read on a 500MB table assuming a 64K multiblock read size?

Now if you had a 64K table in four extents, then you would slightly degrade reading that table. Since the time to rebuild such a small object is so small, this may be a legitimate chance to give your Compulsive Tuning Disorder (CTD) a fix.

If your next extents are so undersized that you have a high rate of dynamic extension during operations, that is useless work being done. No rebuild is required though. Just bump up the next size. (In your version's context you don't have to worry about uniform extents being set to a pathologically low size.)

Cary wrote a nice paper about the pathology of dropping dictionary managed objects with large number of extents. It is probably somewhere on www.hotsos.com.
The workaround leave them alone, or if you're on a version where you're approaching the maximum number of extents you can have, then copy the entire tablespace to a new tablespace and do an offline drop of the tablespace you do not need any longer.

If I've forgotten one of the oddball situations where number of extents is significant, I'm sure someone will chime in.

Now if you have tables with a lot of free space at the beginning, or very low density overall in heavily read tables, or indexes deeper than they need to be or a lot of emptied out leaves, rebuilding those objects for performance may be justified. But it almost always has nothing to do with number of extents.

Regards,

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Yen, Eric Sent: Tuesday, October 12, 2004 11:32 PM To: oracle-l_at_freelists.org
Subject: Number of extents . . . does it matter?

I am working with an Oracle8i database.
Yes I should upgrade and we have plans to in the near future we are vendor locked right now.
We are planning a ReOrg and I am wondering if the number of extents really makes a difference?

For example a 500MB table with Initial Extent of 25MB and Next Extent of 25MB would have 20 extents.
Would changing the Initial and Next Extent to 50MB and having 10 extents increase performance?

I have read that the number of extents does not really matter because of access method either by index or full table scan.

Does anyone have any experience that they are willing to pass along?

Regards,

Eric Yen
http://www.sonypictures.com

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Wed Oct 13 2004 - 02:00:46 CDT