Re: Large initial extent after import

Jurij Modic <jmodic_at_src.si> wrote in article <364caace.43003489_at_news.siol.net>...
> On Fri, 13 Nov 1998 10:25:49 -0000, "Neil Hulin"
> <nospam_at_litech.freeserve.co.uk> wrote:
> >
> >I have experience of 13GB tables and decided on 13 x 1GB extents
to reduce
> >waste space to an average of 500Mb per table. Previously these
tables were
> >up to 11,000 extents and operations would take in excess of 8
hours on a Sun
> >ES10000 with 38 processors, 26GB RAM and 1.5TB mirrored EMC
frames. After
> >the reorg to reduce the number of extents we had most operations
down to
> >four minutes.
>
> From 8 hours to 4 minutes? And you belive this improvement was
achived
> exclusivelly by reducing the number of extents? Well, don't belive
it!
>
> Although 11.000 extents is realy an overkill I am 100% certain the
> poor performance was caused by many other factors, not exclusivelly
by
> the large number of extents.

This is an interesting one isn't it. Like Jurij I would be inclined to doubt that
the extraordinary performance gain would be due to simply reducing the
number of extents - especially since your initial extent size would appear to
have been just over the 1 MB size.

However, you clearly had EXTENTS UNLIMITED - so perhaps there is some bug-ridden code in that feature; alternatively 11,000 extents would be a lot
of entries needed simultaneously in the dictionary cache: possibly a

shared pool that was too small could cause thrashing on dc_used_extents
with subsequent disk thrashing on the SYSTEM tablespace.

Jonathan Lewis Received on Sat Nov 14 1998 - 13:30:12 CST