Re: switching to locally managed tablespaces

"Dusan Bolek" <pagesflames_at_usa.net> wrote in message news:1e8276d6.0207022257.445e12b7_at_posting.google.com...
> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
news:<aft2jm$ahm$1_at_lust.ihug.co.nz>...
> > If you do the conversion, remember to create a range of extent-sized
> > tablespaces (64K, 512K, 1M, 8M, 64M), and move the right table(s) into
the
> > right tablespace.
>
> I haven't fully confidence in this. When I have begun to use LMTs I
> did some tests. I've measured performance while using different
> extent-sized. I've learned that even with 8k extent size and 300MB
> object the performance difference against 512k extent size was just
> few percent.
> So I have decided to use uniform extent size 64K for *ALL* objects in
> our databases. The biggest tables has about 500 - 700 MB. My idea is
> to has only as few tablespaces as needed, because of administration
> overhead.

I don't fundamentally disagree, because I don't think several thousand extents is a particular problem. Jonathan has reported issues with numbers approaching that, however, and I think he's also reported a bug or three regarding enormous numbers of extents.

Your method has the advantage of utter simplicity. My suggestion means that *if* there are nasties regarding huge numbers of extents (5000+, for example), they can be avoided.

There remains the small matter of a single read of the extent map for a segment, and that's still limited by the size of your block -which means that for an 8K block, 505 is still the ideal maximum number of extents. I agree though that measuring degradation beyond that number is tricky.

> I want to run new tests to find out the number of extents where
> performance penalty will be significant. This time I will also
> simulate multiuser environment (100 - 200 session) to check for any
> contestion. I hope that I will learn that maybe just two extent sizes
> (for objects under GB and above) will deliver sufficient performance,
> so there will be no need to use five or more data tablespaces for
> objects of various size.
>

Looking forward to hearing the results.

Regards
HJR
> --
> _________________________________________
>
> Dusan Bolek, Ing.
> Oracle team leader
>
> Note: pagesflames_at_usa.net has been cancelled due to changes (maybe we
> can call it an overture to bankruptcy) on that server. I'm still using
> this email to prevent SPAM. Maybe one day I will change it and have a
> proper mail even for news, but right now I can be reached by this
> email.
Received on Wed Jul 03 2002 - 02:55:58 CDT