Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: switching to locally managed tablespaces

Re: switching to locally managed tablespaces

From: Howard J. Rogers <>
Date: Wed, 3 Jul 2002 17:55:58 +1000
Message-ID: <afuaol$ijs$>

"Dusan Bolek" <> wrote in message
> "Howard J. Rogers" <> wrote in message
> > 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
> > 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.

> --
> _________________________________________
> Dusan Bolek, Ing.
> Oracle team leader
> Note: 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

Original text of this message