| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Tablespace layout
Don,
By uniform extents, do you mean all extents in the same tablespace being
the same size? Or do you mean having
a choice of say three different sizes in on tablespace but sticking to
those sizes? That seems to be the crux of
the disagreement amongst our team.
Thanks for your comprehensive reply.
Cherie
"Don Granaman"
<granaman_at_home To: Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
.com> cc:
Sent by: Subject: Re: Tablespace layout
root_at_fatcity.c
om
08/29/01 02:10
AM
Please respond
to ORACLE-L
There is no advantage at all in making extent sizes a function of db_block_buffers.
I agree, to a certain extent (no pun intended), with the idea of using
"dedicated" tablespaces. I prefer to use dedicated tablespaces for all
"significantly large" tables, where the value for "significantly large" is
subjective. For example, in a very critical 200 GB OLTP database, it might
mean dedicated tablespaces for tables of 200 MB or larger, a few
tablespaces - with uniform extent sizes - for smaller tables, and dedicated
tablespaces - also with uniform extent sizes - for indexes on larger
tables.
For example, ACCT_DATA, ACCT_INDX, CUST_DATA, CUST_INDX, ORDR_DATA,
ORDR_INDX, STAT_DATA, STAT_INDX, etc. Another consideration is in how the
contents of the tablespace are used. STAT_DATA, for example, might be
reserved for largely static tables. There might be another tablespace for
similarly sized tables that are very volatile.
In general, my main considerations are:
Object size
uniform extents - ironclad rule for everything except SYSTEM, in all
but
most rare circumstances
only a few different extent sizes for all app data and indexes in the entire database
ignore the "multiple extents kills performance" myth (but keep it "reasonable"!)
Volatility
read-only data?
objects continually dropped/truncated and recreated/reloaded
intensely transactional?
etc...
I/O distribution
a single non-dedicated tablespace should not contain objects likely to be in contention
separate indexes and data (I/O effect is debatable, but index rebuilds, etc. favor)
tablespace design so as to support reasonably even I/O distribution across disks/stripe sets
ability to (relatively) easily move datafiles to rebalance I/O
Recoverability
Preference for many tablespaces over very large multi-table tablespaces
Preference for more smaller datafiles over very large datafiles (files
>
2 GB are very rarely needed)
Partitioning
For large partitioned objects - a tablespace for each partition Rolling temporal partitions - as above. Age off or move data by partition exchange
Transportable tablespaces
What, if anything , needs to be moved? (e.g to a warehouse staging area)
At what frequency / data volume?
Parallel server
Access patterns, PCM lock allocation (pre-9i at least), etc.
This works best with something like only three or four different extent sizes for everything - except perhaps rollback, system, and temp, and perhaps only three or four different datafile sizes for everything except perhaps control files and redo logs.
The above is an "off the top of the head" list. I don't pretend that it is comprehensive. However, it might generate some interesting discussion. It seems that space management is sometimes almost a religious topic. I converted to the "uniform extents" philosophy early - about 1990 - and confess to being an evangelist.
-Don Granaman
[certifiable OraSaurus]
I agree completely with Lisa. Several years ago I had a chance to talk with an Oracle tester in IBM's benchmark lab. They had a one table/one tablespace/one datafile layout, mostly for recoverability. Once you document it fully (and name it logically) administration is not that difficult.
For those of you who know Oracle internals, is there any advantage in making the uniform extents a multiple of the db_block_buffers area in the SQA? Or is that just a feeling left over from when I had to align strings on full-words while programming assembler?
"Koivu, Lisa"
<lisa.koivu_at_efair To: Multiple recipients of
list ORACLE-L
field.com> <ORACLE-L_at_fatcity.com>
Sent by: cc:
root_at_fatcity.com Subject: RE: Tablespace
layout
08/28/2001 05:07
PM
Please respond to
ORACLE-L
Well Satar you didn't even read Cherie's email.
Way back before I took that db to the tablespace layout you refer to (straight out of HOw to Stop Defragmenting and Start Living white paper) I had created a separate tablespace for each partition. It was an awful pain back then and the number of tablespaces I had was ridiculous. I can see your point with mttr and keeping your tablespaces fairly small in comparison to what it's become. I guess it's a tradeoff with pros and cons either way.
However, I suggest you take that paper (above) and throw it their faces. I disagree about different sized extents in the same tablespace vehemently. I think they should be uniform, period, for the same reason you state - if disk is so precious, then use it wisely !! Plus, when your partition starts looking for another 100MB extent, you are out of luck.
What's your feeling on partitioning? I think if you are dropping data monthly, use partitions wherever you can. That way you are more assured of reusing your disk - however, then you are looking at a different tablespace per partition.
I believe at one point I had 2-3 partitions in each tablespace, rotating them (part1 in ts1, part2 in ts2, part3 in ts1, part4 in ts2, etc.) Maybe that will provide the compromise in functionality, disk space management and recoverability you seek.
I do know your pain. Good Luck.
Lisa Koivu
Certified Monkey and DBA
Ft. Lauderdale, FL, USA
-----Original Message-----
From: Satar Naghshineh [SMTP:Satar.Naghshineh_at_irvine.mellesgriot.com]
Sent: Tuesday, August 28, 2001 4:30 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Tablespace layout
Hi Cherie,
Just shove all your data on a RAID 5 (great for data Warehouses) and forget about it. If that is not possible, then stick with what your DBA team has stated about everything being ok as long as the extents are multiples of one another.
Regards,
Satar Naghshineh
-----Original Message-----
From: Cherie_Machler_at_gelco.com [SMTP:Cherie_Machler_at_gelco.com]
Sent: Tuesday, August 28, 2001 7:27 PM
To: Multiple recipients of list ORACLE-L
Subject: Tablespace layout
I've been thinking a lot about our tablespace layout for our data warehouse. Our warehouse is approaching 200Gig and is almost out of space on disk. I'm getting another 75 Gig this weekend and am trying to plan the best use for that 75Gig.
Our current data tablespace layout is that we have two tablespaces for small, medium, and large data tables and the same for indexes. So it's something like this:
SM_DATA01
SM_DATA02
MED_DATA01
MED_DATA02
LG_DATA01 LG_DATA02 SM_IDX01 SM_IDX02
Our largest tables are partitioned. However the partitions are not
split
out into separate tablespaces but go into the same medium and large
tablespaces as non-partitioned tables. Theoretically, I suppose that
this
is not a problem if, when partitions are dropped every month, the
resulting
space is reused 100%. I'm not sure if it is.
Here is my question. My DBA team members feel that it's fine to have
tables with a variety of extent sizes in the same tablespace as long as
they are all multiples of each other (50, 100, 400, 2000, etc.). My
concern
is that this setup is fine when the smaller tables need to extend but
when
the larger tables need to extend, they can't pull together enough
contiguous
space and I keep having to add more. I'd prefer to have only one size
of
extent in each tablespace and keep it very pure that way. Then I know
every single extent can be reused. So I am considering increasing the
number of tablespaces so we have something more like this:
1kdata
10kdata
100kdata
1mdata
10mdata
100mdata
1000mdata
1kidx
10kidx
100kidx
1midx
10midx
100midx
1000midx
I would also probably split them out into at least two tablespaces for
each
level.
Maybe not for the smaller sizes, but for the larger sizes.
I have several issues I'm trying to keep in mind. One is the ease of
maintenance
for initial creation and ongoing upkeep. I don't want to have too many
tablespaces
if I don't need to. Another issue is mean time to recover. If we
lose a
single tablespace,
I'd prefer to have to recover fewer files. The maximum file size we are
using is 2Gig.
We need to keep our recovery time under four hours total.
Probably the biggest issue I'm facing now is the sheer size of the large
tablespaces.
They are so big and bulky that it's almost impossible to reorg them or
even
just
clean them up. I think that if I had more smaller tablespaces, I would
have more
options. This database is still at 8.0.4 and it's going to be a while
before it can be
upgraded so that limits my options for reorging as well. All cleanup
has
to be done
in a series short Sunday windows. I don't have the luxury of a tool for
doing this
reorg so have to do it manually.
Another issue is partitions. We are dropping the old partitions on the
main fact
table once a month. We are not currently planning on dropping any of
the
other partitions.
The tables have a variety of partition names and schemes. Some are
partitioned yearly,
monthly, quarterly, half-yearly. There is no consistency. I'm
debating
whether I should
split each partition out into it's own tablespace. That would be
almost a
hundred tablespaces.
Or just the table that we're dropping partitions on monthly. That
would
be about 50 tablespaces.
Or should I just leave them all in the same tablespaces as
non-partitioned
tables?
We are using Sun Solaris 2.6 on an E10K. We have EMC disk and Veritas
file manager.
Using version 8.0.4 of Oracle, as I said. Using RMAN and Veritas for
backups.
Any feedback, ideas, suggestions, things to watch out for, think about,
etc. would be greatly
appreciated. This is going to take a lot of time and effort to do and
I
don't want to get all the
work done and find out it doesn't work as well as I hoped and have to
redo
everything.
Thanks for your time,
Cherie Machler
Oracle DBA
Gelco Information Network
--
Please see the official ORACLE-L FAQ: <http://www.orafaq.com>
--
Author:
INET: Cherie_Machler_at_gelco.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: tday6_at_csc.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman INET: granaman_at_home.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Cherie_Machler_at_gelco.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Aug 29 2001 - 07:09:06 CDT
 |
 |