Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Is there a performance limit of extents?
> Hmmm, that's not my experience, Sy.
> In almost all cases I've seen of "better
> performance after reorg" since V7, it's been
> because chaining was gone, indexes had been
Not to mention row migration. This is even worse than chaining.
> defragged or free space in table compacted.
To help with the indexe fragmentation, I like to use the ALTER INDEX VALIDATE STRUCTURE and rebuild indexes on occasion. This is much easier than reorg'ing a db. Compacting free space helps performance in those db's that need to allocate new extents. But you can remove this problem if you have uniform extents sizes throughout the tablespace. Another things that is improved when reorg'ing (that has nothing to do with the number of extents) is doing full table scans. A full table scan will read up to the high water mark for that table. If lots of rows have been deleted, then the full table scan will be reading "empty" blocks. With a reorg, the high water mark is reset.
> There might be a veeery slight performance
> advantage in less extents, but IMHO not worth the
> trouble of the frequent, religious reorgs.
I agree.
> I have run up to 90 extents in NT boxes (not noted
> for their performance feats...) without the
> sightest problem. Reorganizing didn't achieve any
> changes in performance whatsoever. Data wasn't
> chained or free-holed.
I've had tables with more than 5,000 extents that didn't seem to suffer from performance problems. If Oracle was really worried about this, they would permanently modify the sql.bsq file. On almost every system that I have the SYS.SOURCE$ table has many extents. Of course I can modify the sql.bsq file to change the allocation for this table at database creation time, but why?
> Having said that, I don't recommend THOUSANDS of
> extents!! But since V8, I don't bother until
Like I said, I've had tables with 1000's of extents. This happens frequently when working with Spatial Data in my shop.
HTH,
Brian
-- ======================================== Brian Peasland Raytheons Systems at USGS EROS Data Center These opinions are my own and do not necessarily reflect the opinions of my company! ========================================Received on Thu Apr 20 2000 - 00:00:00 CDT