Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Is there a performance limit of extents?

Re: Is there a performance limit of extents?

From: Brian Peasland <peasland_at_edcmail.cr.usgs.gov>
Date: 2000/04/20
Message-ID: <38FF0339.FEC137C@edcmail.cr.usgs.gov>#1/1

> 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US