Re: Does the number of extents affect the performance?

Thomas Kyte wrote:
>
> 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

Undoubetly you are right. But, every time I've had a performance problem in the last 12 years, I have found an index had overflowed it extent. When I dropped the index, expanded the size, and recreated it my performance problems have gone away. And your explanation for this is ...? Received on Sat Sep 20 1997 - 00:00:00 CDT