Re: planing tablespaces - please comment

I like it; it's just how I do it. Take care you create the tablespaces 'locally managed' and 'uniform extentsize' plan the extent-sizes carefully though. Database-block-size * multiblock-read-count is a good start for small. Any number of this (e.g. 30 times this value) is sufficient for medium and large. Dependent on your UNIX installation there may be an optimal small-size by disk-buffers, track-read size etc. Consult your UNIX sysadmin for this.

You don't have to create just 3 tablespaces, Creating more, and dividing tables across them gives you the opportunity to load balance the datafiles over disks/controllers. Also dividing the indexes over more tablespaces, and placing them on different disks from the corresponding table-datafiles is an option.

Placing a data_big together with an index_small on one disk should be no problem, as long as the load between the disks is balanced.

Moving indexes is fairly easy (don't forget the storage clauses if you don't use uniform extens), moving tables tends to be a bigger problem. Try to prevent it as much as possible. If you have data about the data-increase rate, use it. A table now sized as small but estiated medium within the year should be placed in med now.

Don't forget TEMP, USER, ROLLBACK etc!

Eric Lansu

• Original Message ----- From: "Rahul" <rahul_at_ratelindo.co.id> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, 05 October 2000 12:16 Subject: planing tablespaces - please comment

> List,
> please comment
>
> i do the following when planing tablespaces for the schema
>
> 1) divide all tables into three categories small, medium, large
> according to the estimated data volume
>
> 2) create 3 tablespaces to hold the above 3 kinds of tables.
> data_small,
> data_med,
> data_large
>
> all tablespaces are created with uniform extent size (8i)...
> example:...
>
> data_small extent uniform size 32K
> data_med extent uniform size 1M
> data_large extent uniform size 25M
>
> i keep the max number of extents to around 30, if a table exceeds that
> limit then i move the table to the higher level tablespace
>
> i apply the same logic to indexes also...
>
> is the right way to plan tablespaces ?
>
> TIA
> Rahul
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Rahul
> INET: rahul_at_ratelindo.co.id
>
> 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
Received on Thu Oct 05 2000 - 05:35:22 CDT