Re: Does the number of extents affect the performance?

There are two main preventative measures to take:

1. To prevent freespace fragmentation, make all extents in a tablespace the same size or at least a multiple of a standard size.
2. Set each table's PCTFREE big enough to prevent row migration.

Roald van Geleuken wrote:
>
> Seem to have stirred up an ants nest, or so it seems. Lots of explanations,
> but how to cope with the problem? It seems to me the fastest way of
> resolving ALL problems is exporting/importing, but I'm open to suggestions.
>
> What really bugs me is that Oracle doesn't come up with any solutions
> integrated in it's products. It's very easy to say 'O, do an exp/imp, and
> it's ok', but I've had problems with that too (large table, fragmented free
> space). There must be solutions which can be built into the system, without
> the need for user intervention. Anyone for setting up a brainstorming
> session?
>
> Roald
>
> roald_at_xs4all.nl
>
> Gary England wrote in article <34247E2E.4A30_at_hiwaay.net>...
>
> >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 ...?
 

-- 
John P. Higgins      Voice:    (309)765-7868
Deere & Company      Fax:      (309)765-7800
John Deere Road      Internet: jh33378nospam_at_deere.com
Moline, IL 61265     Opinions: My Own