Re: Does the number of extents affect the performance?

 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 ...?
Received on Sun Sep 21 1997 - 00:00:00 CDT