RE: Number of extents . . . does it matter?
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 13 Oct 2004 03:03:30 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Yen, Eric
Sent: Tuesday, October 12, 2004 11:32 PM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
