Re: Does the number of extents affect the performance?

>Donna L. Matthews <donnam_at_ep.anl.gov> wrote in article
 <340F32D2.519B_at_ep.anl.gov>...
>>
>> dtang_at_minn.net wrote:
>> > After reading several articles, I get an imprsssion that the fewer
>> > extents in a table's data segment, the better the performance would
>> > be. But I can't find this statement in any official document.
>
> The book Oracle DBA Handbook 7.3 Edition by Oracle Press covers this
> issue. Page 258 states "How the database actually stores data has an
> effect on the performance of queries. If the data is fragmented into
> multiple extents, then resolving a query may cause the database to look
> in several physical locations for related rows....."
>

I stand by that statement; it does have an effect, but it is not significant for
performance of most queries. The greater factor is the sizing of the extents.
The extents should be sized to be multiples of the size read during a single
multiblock read during a full table scan. If block size is 4K, and db_file_multiblock_read_count is 16, then your extent sizes should be multiples of 64K. If they are, then having multiple extents will not impact
the performance of your full table scans. The same number of reads will be required whether the table is in 1 extent or many.

see pg 86-91 of Advanced Oracle tuning & Admin for a fuller explanation. Note that having multiple extents can *help* you, particularly if you're using the parallel query option.

Kevin Loney Received on Fri Sep 05 1997 - 00:00:00 CDT