Re: How many extents is too many?

In article <329330A4.4138_at_att.com> Allen Kirby <akirby_at_att.com> writes:

> Actually, they do hurt performance even if they are contiguous. We had
> a table that grew continuously (Oracle could never explain why) and
> never reused blocks. Full table scans went from a few seconds to
> several minutes, and nothing else was in the tablespace. Oracle
> doesn't know that extents are contiguous - it basically has to stop the
> block scan, close the previous extent, open the new extent and
> initialize all the internal stuff for the new extent before it can
> continue with the scan. Extensive extents is an indication of poor
> design or unexpected growth and should be corrected if possible.

I don't think that this has anything to do with the number of extents. I did some tests for my diploma thesis on this topic with Version 7.3.2 on Sun Solaris. My results were that multiple extents do NOT hurt the perfomance of Full Table Scans if the extents where physically contiguous. Your problems might be caused by block fragmentation (you should adjust PCTFREE and PCTUSED) or a false setting of the high water mark of your table.

For further details, you should take a look at Cary V. Millsapp's paper ORACLE Server Space Management at
http://tiburon.us.oracle.com/odp/public/library/cr/html/cr_white.html

Axel Schlueter
Universitaet Hannover, Germany Received on Mon Nov 25 1996 - 00:00:00 CET