Re: Performance and number of extents

The number of extents an object is in can have a dramatic effect on performance, but there are a great many variable that come into play. The Oracle press book 'Advanced Oracle Tuning and Administration' has a chapter that goes into the details of when extents hurt and when they do not. Basically, if your extent size is an even multiple of the init.ora parameter, multi_block_read_count, then the number of extents does not harm performance. Parallel query, Os level stripping, the stripe size, the number of disks, and the I/O distribution all have an effect on performance.

Also indexes are often read in a sequential manner duing a range scan. Small table access can benefit greatly from being in a single extent where that extent is equal to or less than the multi_block_read_count and this parameter should be set to the largest I/O that the OS will access in one request. (Usually 64k or 128k on Unix).

Every system needs to be adjusted for the platform restrictions and the application behavior.

Mark Powell -- The only advise that counts is the advise that you follow so follow your own advise Received on Tue Aug 19 1997 - 00:00:00 CDT