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