Re: Number of extents related with performance?

Hi David.

I don't know if this is related to the disparity, but just because a table is newly created does not mean that it isn't fragmented. If a table has many extents, these can be dotted all around the tablespace. This is more to do with fragmentation of the tablespace which the table sits in.

If you then recreate the same table with less extents, more of it will be in contiguous blocks. Then table scans may run quicker as Oracle is able to pick up more contiguous blocks at once from physical reads (check your db_file_multiblock_read_count).

You should check to see whether more physical reads are done when the table is in many extents. Also, check the performance after rerunning your query on the table with many extents. It may then run much faster due to caching.

I don't think that having many extents IN ITSELF causes a significant performance degradation (at least not in more recent versions of Oracle).

HTH, Dave.

Sent via Deja.com
http://www.deja.com/ Received on Thu Dec 28 2000 - 11:58:15 CST