Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Does the number of extents affect the performance?

Re: Does the number of extents affect the performance?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/09/10
Message-ID: <341b15d8.10422166@newshost>#1/1

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



Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Sep 10 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US