Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Does the number of extents affect the performance?

Re: Does the number of extents affect the performance?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/09/15
Message-ID: <341f9065.21657351@newshost>#1/1

On many databases, you can run export with compress=y and see a measurable performance improvement in the resulting imported database. The serious flaw with using this data point as a "proof" that few extents is good is that you can use compress=n and achieve identical performance gains. The performance gains come not from reduction in the number of extents in database segments, but from the block packing and row fragmentation removal implicitly in any import.

Have you, when you hit 40 extents, exported and imported back into 40 extents? When you imported you fixed

among other things. The fact that the magic number varies by table indicates that something other then the physical number of extents is at play.

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.
>
>
>roald_at_xs4all.nl
>
>
>
>Nuno Souto wrote in article <341D1B62.2607_at_acay.com.au>...
>
>>
>>>But hasn't Mohamed demonstrated the pathological worst case ? Is this
>>>typical ?
>>>
>>>We still want to answer the original question of "As far as performance
>>>goes, do extents matter ?". And the answer seems to be "It -almost-
>>>never is an issue." Shouldn't we ?
>>>
>>
>>In most cases they don't. Like anything, there are pathological cases.
>>To me, going through all the trouble of religiously de-fragging every
>>night just to make sure the worst case doesn't happen is the equivalent
>>of changing the oil on your car every time it rains: we all know water
>>can enter the oil and stuff up the engine, but do you change the oil as
>>soon as it finishes raining? Or do you just check the dipstick?
>>
>>Same here: why go through all the trouble when there are ways of
>>checking if fragmentation is a problem? If it is, fix it. And correct
>>the allocation so that it doesn't happen again at least on that object.
>>If it is not, then why bother? There are much better ways of using your
>>knowledge and time as a DBA than to do repetitive drone work every night
>>just because on a blue moon and if the wind is blowing from the NNE, the
>>performance might drop.
>>
>>
>>
>>--
>>--
>>Nuno Souto
>>nsouto_at_FOSPAMacay.com.au
>

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 Received on Mon Sep 15 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US