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/21
Message-ID: <34246efb.123516086@newshost>#1/1

On Sat, 20 Sep 1997 15:13:35 -0500, Gary England <gengland_at_hiwaay.net> wrote:

>jkstill_at_teleport.com wrote:
>>
>> On Mon, 15 Sep 1997 19:01:30 +0200, "Roald van Geleuken"
>> <roald_at_xs4all.nl> wrote:
>>
>> > I don't agree with this. I'm working on a project where we found that when
>> >the number of extents exceeded about 40 (differs per table), we got a
>> >significant performance loss. After export/import to a single extent, the
>> >performance would go up again, until again the extent count would go over
>> >the magic mark.
>> >
>> >It seems that the recordsize of the table has something to do with it. The
>> >bigger the record, the faster the number of extents will reach the magic
>> >mark. As I said, this magic mark differs per table. A quick solution is to
>> >set the next_extent to a big value, although this might give problems with
>> >available tablespace-space.
>> >
>> >Like to hear other opinions.
>> >
>> >Roald.
>> >
>>
>> I ran a quickie test on this very subject the other day.
>>
>> I created to versions of a fairly large ( 65 meg ) table.
>>
>> One version had all rows in 1 extent. The other was in
>> 85 extents. I did not take DB_FILE_MULTIBLOCK_READ
>> into account. I simply sized the extents to ensure that a
>> large number of extents was created.
>>
>> Using tkprof, I discerned a maximum performance degredation
>> of 2% on the table with 85 extents when doing full table scans.
>>
>> When I consider the enormous savings that can be gained
>> elsewhere, ( tuning SQL for example ), it's hard to justify
>> spending time defragging tables.
>
>The impact to performance is the index file. Frag the data file into
>bits and pieces and you may see a difference; break up the index and
>you'll die quickly.

No way, indexes are read (until O8 anyhow) a block at a time anyway. It doesn't matter how many extents an index is in. It reads a block, figures out what block to read next and reads that one. Pure random, scattered IO. Extents have no affect on index scans, since they are random and scattered to begin with. Even if an index is in one extent, it might read block 1 from the front of the extent, block 2 from the end, block 3 right next to block 1 and so on.

In oracle8 there does exist the ability to 'fast full scan' an index structure. It does not attempt to reconstruct the index (which would lead to scattered IO) but reads the index using multi block reads and processes it unsorted (data from a full index scan comes back unsorted). Here the considerations would be the same for tables (try to make the extent size an integral value of the multi block read count).

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 Sun Sep 21 1997 - 00:00:00 CDT

Original text of this message

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