Re: Does the number of extents affect the performance?

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