restructing tables in new tablespaces - advice
Steven
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??
Thanks,
Steve.
--
-----------------------------------------------------------------
Holidays in Berchtesgaden, Germany:
http://www.sonnenkoepfl.de
http://unterkunft-berchtesgaden.de
http://pension-berchtesgaden.de
Chuck
news:bh8f0u$v69sf$1...@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.
Additionally you may want to separate by other factors. For example, some
people still like to separate tables from their indexes even though
recent evidence suggests it really doesn't yield much performance benefit
on SANs.
I also like to separate apps and logically separate parts of apps into
different tablespaces. If I need to offline or recover a tablespace I can
do it with minimal impact.
quarkman
I think you missed the essential point of tablespaces in that reply (though
it was otherwise sound): separate things out which are likely to contend
for I/O. It's no good just saying "all these tables are small, so I'll
stick 'em in the 64K-extent-sized tablespace" if, in the process, you
happen to co-house half a dozen tables which are forever being hammered to
death simultaneously. Sure, separate by size... but then make sure you
haven't introduced I/O hotspots in the process.
~QM
Daniel Morgan
Where's Howard Rogers when we need him?
Everyone is correct ... it is a very complex topic ... and everything must be
considered.
I am of the belief that tablespaces should be size and application specific.
And the I/O problem best solved by SAME or creating arrays involving multiple
disks that spead out the I/O. To try to manually do it on a
tablespace-by-tablespace basis is a losing proposition.
--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damo...@x.washington.edu
(replace 'x' with a 'u' to reply)
Richard Foote
news:Xns93D47FFC912A0...@130.133.1.4...
> "Steven" <SPAMB...@gmx.net> wrote in
> news:bh8f0u$v69sf$1...@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.
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" !!
There's a thought ;)
Cheers
Richard
Chuck
news:xY2_a.29623$bo1....@news-server.bigpond.net.au:
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.