Re: Slowness on insert statemente

Ed Stevens <nospam_at_noway.nohow> wrote in message news:<v4rbi0tnijh8djh865jaopgdgl1t6g751s_at_4ax.com>...
> 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.

No worries, it all came out right in the end. I need to work on being more clear, I tend to be overly terse on usenet. I posted what I did because I was reading between Daniel's lines an assumption of database modernity that I didn't see justified in the OP. That is, nowhere does the thread specify a version, reference to netapp filer notwithstanding.

I also have customers doing exp/imp with compress=y and DMT, very difficult to keep my mouth closed about that beyond the ocassional "hey, we really should be doing some proper DBA work..." Difficult with a not broke don't fix mentality, very frustrating when you know it is an afternoon's work. But as things migrate to 9 it gets fixed in the normal course of events, so why push.

jg

--
@home.com is bogus.
Can't believe the FAQ still recommends OPTIMAL: 
http://www.jlcomp.demon.co.uk/faq/rollback.html