Re: Does the number of extents affect the performance?

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. Received on Sat Sep 20 1997 - 00:00:00 CDT