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

Comments in-line
On Tue, 12 Oct 2004 20:32:17 -0700, Yen, Eric <eyen_at_sonypictures.com> wrote:
> 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?

My stock reaction to this would be no - Mark has outlined why. Certainly I'd be looking for some sort of proof that

1. it made a difference. and
2. if the reorg is simply to resize objects that the benefit was greater than the cost of the reorg. I think I have done 1 reorg since 1999 - and that was because of vendor upgrade script that moved objects from their existing tablespaces to a default tablespace (grrr).

> 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?

Now this statement worries me as it implies, but does not say, that you are considering having different sized extents in the same tablespace. This is a very good way to ensure employment as it will guarantee that you have to monitor free space fragmentation. It isn't the smartest strategy in the world though. Apologies if this is just a misplaced rant.

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com

P.S. There is one addendum to mark's list of when a higher rate of
extent aquisition can be harmful to performance and that is when you
use quotas. I don't *believe* the effect to be significant though.
--
http://www.freelists.org/webpage/oracle-l