Opinions about no extents for a segment

[fraze]

I'm looking for opinions on how many extents a segment has before performance starts to degrade. I want to move our dict managed tablespaces to locally managed and I'm thinking of choosing a uniform extent size that keeps the largest segment in the tablespace below 1000 extents. Any opinions?

[oravijay]

Hi,

Actually the performance does not depend upon the total no. of extents. It depends
upon whether the segment has fragmented or not. It also depends upon the
distribution of the data.

Vijay.

[ovidius]

Yes, but more extents increase the risk/measure of fragmentation...
It is good to make an equilibrium between space-wastage, finesse of allocation and performance/risk of performance decrease. And this could be done when you have an idea of what amount of data will you have in a segment and how dynamic your data will be.
So, absolute numbers will tell you nothing. This is a relative issue.