Re: restructing tables in new tablespaces - advice

From: Chuck <chuckh_at_softhome.net>
Date: 12 Aug 2003 14:30:51 GMT
Message-ID: <Xns93D56AF401C65chuckhsofthomenet@130.133.1.4>

"Richard Foote" <richard.foote_at_bigpond.com> wrote in news:xY2_a.29623$bo1.27787_at_news-server.bigpond.net.au:

> "Chuck" <chuckh_at_softhome.net> wrote in message
> news:Xns93D47FFC912A0chuckhsofthomenet_at_130.133.1.4...

>> "Steven" <SPAMBLOCKER_at_gmx.net> wrote in
>> news:bh8f0u$v69sf$1_at_ID-82797.news.uni-berlin.de:
>>
>> > Hi,
>> >
>> > I have to restructure the tables in our database and am wondering
>> > the best way to structure the tables.  Current the tables are
>> > ordered by application in separate tablespaces.  But I now want to
>> > use UNIFORM tablespaces.  Thus I am wondering if I should separate
>> > the tables by size as some tables are 1-2 MB and other 80-300MB. 
>> > does it make sense or not worth the effort??
>> >
>> > Does anyone have any advice on this??
>> >
>>
>> Definetely separate by size. I typically create tablespaces with
>> names that reflect the extent size like data32k, data1m, data50m,
>> index1m, etc. If you size your extents based on how fast the segment
>> grows, it will also help you predict when the tablespace will need to
>> be extended. If I have a segment that grows at 1m per day, I would
>> make the extent size at least 4m. That way I can run nightly reports
>> showing which segments cannot extend and when that segment appears on
>> the report I know I've got at least 4 days before it's going to fail.
>>

>
> Hi Chuck,
>
> Separating segments by size as you suggest made sense with DMT but in
> the not so new world of LMT, it's kinda redundant. But it's still a
> legacy that refuses to go away, probably because it's still being
> hailed as necessary by (so-called) experts and probably because of
> Oracle itself with regard to how it manages LMT with the autoallocate
> option using a similar algorithm. Having a tablespace with a uniform
> extent of 1M and a tablespace with a uniform size of 50M suggests that
> somewhat over 50 extents is an issue (why else separate them and why
> else go to the considerable trouble of moving a segment from one
> tablespace to the other when you get it wrong). And of course having
> an issue with 50 extents is a nonsense, as it is with 500 extents, as
> it is with 5000 extents...
>
> In fact if you can show me a segment that has an issue with the
> *number* of extents, I'll show you a segment that should either be
> partitioned (preferably) or a segment that should be stored in it's
> own tablespace.

Reread my post. The reason I suggest separating into tablespaces with different size extents has nothing to do with the # of extents you end up with in your segments. It has everything to do with making it easier to predict when a tablespace will need to be extended based on the growth rate of the segments within it. What could be easier than "Is there any free space in the tablespace?". If there isn't you know you have 4 days to react.

> From a reporting point of view, it's not too hard to monitor segments
> that have unexpected growth characteristics or tablespaces that have
> space issues or to write a report exactly as you describe. It really
> isn't. Not if the tablespaces themselves are reasonably sized.
>
> It really is time someone wrote a paper called "How to Stop
> Fragmentation and Start Living In the 21st Century" !!

I think it already exists. Received on Tue Aug 12 2003 - 09:30:51 CDT