Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Does the number of extents affect the performance?
On Tue, 09 Sep 1997 22:25:37 -0700, Mohamed El-Mallah <mie_at_mainruis.sannet.gov> wrote:
>
>The number of extents in table does not affect the performance of full
>table scans as long as
>the size of the extents is a multiple of the sizeof each read performed
>(DB_FILE_MULTIBLOCK_READ_COUNT * DB_BLOCK_SIZE)
>Mohamed El-Mallah
>SDDPC
And if it isn't, it still won't affect the performance of a full table scan of a
table in multiple extents. We are talking micro-milli seconds if we don't get a
full db_file_multiblock_read_count number of blocks in a read.
Let your tables go into as many extents as you want. It will not affect the performance of inserts/updates/deletes/selects.
The ONLY statements that are affected by a table in many extents are certain DDL command (eg: it takes longer to drop a table with 200 extents then 1 extent becase we need to remove 200 rows from uet$ instead of just 1).
>
>Thomas Kyte 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.
>> >
>>
>> No, performance for a 1 extent table will be no different then a 100
>> extent
>> table. this (few extents = better performance) is a rumor.
>>
>> On many databases, you can run export with compress=y and see a
>> measurable
>> performance improvement in the resulting imported database. The
>> serious flaw
>> with using this data point as a "proof" that few extents is good
>> is that you can use compress=n and achieve identical performance
>> gains. The
>> performance gains come not from reduction in the number of extents in
>> database
>> segments, but from the block packing and row fragmentation removal
>> implicitly in
>> any import.
>
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities