Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Tablespace layout

Re: Tablespace layout

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Wed, 29 Aug 2001 06:55:50 -0700
Message-ID: <F001.0037AF60.20010829061601@fatcity.com>

Cherie,
 I created a test database for our developers that contains all of the production tables and data. The production is Oracle 7.3.4 on Novell and the test is 8.1.7 on Linux. Being that I am limited to the number if disks that I have available and I needed to use all available space efficiently, I opted to use LMT for the data and indexes. Partitioning is used on the tables that contain a lot of data and span a large date range 1993 to sysdate. Determining the size of the other tables and their time frame for usage I was able to determine the best uniform size for the LMT. All tables that are static and small reside in 1 table spaces determined by their usage( all of the time, infrequent). Tables that contain daily updated, weekly updated,and monthly updated are of different size and volume therefore the LMT extents were tailored differently. The large tables that contain current and historic data are partitioned by date range and have different LMT extents as needed. The time and effort spent in laying out the tablespaces and datafiles was well worth it. The free space is at a minimum and as the data grows or shrinks the extents are reused with the LMT and uniform sizing for each tablespace. The original database has 31 tablespaces with approx 200 tables. The test database has 153 tablespaces that handle the 200 tables.The free space is considerably less on the LMT uniform extent server as like size requirements are combined where ever possible. The LMT and partitioning has also allowed me to remove some views that joined current and historic tables to present to the user. Not using the view decreased data access time and allowed the use of the indexes on the partitions. The partitioned tablespaces are smaller as the data is broken down by year (Megs as compared to Gigs) as compared to current( 6mo) and historic (8 years).
  I feel that if you have the option and the time to manage your tablespaces then you will have better control over your disk space. I know that it takes time to set everything up and get it implemented but your space usage and access time should improve noticeable.
ROR mm

>>> Cherie_Machler_at_gelco.com 08/28/01 02:26PM >>>

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

MED_IDX01
MED_IDX02
LG_IDX01
LG_IDX02 I think that theoretically, all of the tables in each of the tablespaces were supposed to have the same sized extents when they were originally created. However, over time, there are multiple sizes of extents in the large and medium tablespaces. Even though we show significant free space, it is fragmented and coalescing can not put together enough contiguous space to reuse a lot of the available space.

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
--------------------------------------------------------------------
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: Ron Rogers
  INET: RROGERS_at_galottery.org

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 - 08:55:50 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US