Re: Slowness on insert statemente

Reply embedded . . .

On Fri, 20 Aug 2004 09:39:41 +0200, Frank van Bortel <fvanbortel_at_netscape.net> wrote:

>Ed Stevens wrote:
>
>> On 18 Aug 2004 18:07:52 -0700, joel-garry_at_home.com (Joel Garry) wrote:
>>
>>
>>>Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1092753065.997815_at_yasure>...
>>>
>[deletia]
>>>
>>>Also, there is the possibility that there are many more extents being
>>>in one than the other, and the extents are too small, and every time
>>>there is an insert those dictionary tables that track the extents are
>>>thrashing. Use larger extents, or LMT.
>>>
>>>jg
>>
>> So, are you saying that the number of extents is a performance impact?
>> I thought that had been put to bed.
>>
>
>That is not what I read; I read about inserts, small extents and
>a dictionary managed tablespace, that extends on about every insert.
>Some reading between the lines required :)
>Wouldn't you agree that the overhead of space management in a
>dictionary based system impacts performance?
>

Absolutely. And after reading Joel's statement a bit more thoroughly and in context, I see nothing to contradict or clarify. I just kind of jumped at "...many more extents...", "... extents are too small...", and "Use larger extents, or LMT." These are hot buttons with me because I'm still having a running battle with my co-worker on these issues. Left on his own, he'd still be doing regular exp/imp re-orgs of tabels, with compress=y on export.

Besides, challenging others' statements exposes oneself to being challenged in return, which in itself is educational and leads to clarification of one's own understanding. That's a risk/benefit I'm increasingly willing to actively seek, even if it makes me look like a poseur.

>> Components of rowid do not reference the extent, so I can'
>> t see where number or size of extents has any impact at all on access
>> to existing rows. It appears to me that the performance impact of
>> 'improper' extent sizing -- many small vs. few large extents -- would
>> come from the overhead incurred at the time a new extent is allocated.
>> True enough, if that were the case and extents were severely
>> undersized, an insert heavy app would perform slower from have to stop
>> and acquire new extents more often. Whch, since the op was asking
>> about insert performance might be something to look at.
>>
>> All of which, perhaps, you were inferring and I just felt like
>> clarifying. Or maybe my understanding is still way off base and I
>> need to be set straight -- always a distinct possibility that I try to
>> stay aware of.
Received on Fri Aug 20 2004 - 07:34:59 CDT