Re: Does the number of extents affect the performance?

In article <340F32D2.519B_at_ep.anl.gov>, "Donna L. Matthews" <donnam_at_ep.anl.gov> wrote:
>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.
>> So I want to ask the experienced DBA about the relation between the
>> size of extent and the performance.
 

>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....."

The amazing and excellent Cary Millsap, author of a white paper on Oracle7 database sizing, comments:
<quote>
[Donna's reply] is an accurate observation and analysis, but there is an alternative recommendation. [...] Extent fragmentation wasn’t the problem [...] it was that the extents were sized not to mesh with the setting of db_file_multiblock_read_count. Even for small tables, careful selection of extent sizes removes the performance risk we’ve seen identified here. [...]

So, for any access other than a full-table scan, multi-block reads are irrelevant to our decision making process. This means that for indexes, rollback segments, and tables that are never read sequentially, there is no need to worry about extent sizing for multi-block I/O.

Our focus [...] is on tables that are full-table scanned by an application (and also on temporary segments[...]). For those segments, if you do choose extent sizes that are integral [multiples] of db_file_multiblock_read_count, you put the whole issue to rest.

Conveniently, most administrators use db_file_multiblock_read_count values like 8, 16, 32, 64, and so on (powers of two) that -- guess what -- work perfectly with extents whose sizes are chosen from the set 2k, 4k, 8k, 16k. </quote>

IMHO, this is really the complete answer and should be placed in every Oracle7 FAQ. Summary of Cary's Rules: For indexed selects, extent sizing is irrelevant. For scans, extent sizing is not a performance issue when extent sizes are a multiple of multi-block read count parameter.

--
Dave Mausner, Consulting Manager, Braun Technology Group, Chicago.