Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Optimal size of datafiles ?

Re: Optimal size of datafiles ?

From: Eric Lansu <>
Date: Thu, 27 Jul 2000 15:58:25 +0200
Message-Id: <>

Hello Kirsten,

There is no rule of thumb for this. Corey and others say; don't go over 1G for many UNIX - backup systems can't cope. On top of that, you have the ability to spread the datafiles over more disks, so load-balancing is easy. I, speaking for myself, don't like this. I spread tables over different tablespaces, and put the indexes in others. 1 tablespace is in 1 datafile. This way I can use the auto-extend option, and I know what disk / controller is busy doing a query. Load balancing can be done by moving these tablespaces around. I use many, relativly small disks to do this. I am implementing a database on 20 9Gb disks right now. I use max. 2 datafiles per disk. 1 light-use and 1 heavy-use.
I have seen systems perform poorly because the datafiles where spread over different disks/controllers. It looks nice, spread the table over different disks, and they can work together to retrieve data. Untill you have 60 or more users on your system, and nobody gets access anymore for all disks are busy.... Performance drops like a brick from a skyscraper ;-)

The moral of the story is;

There is no rule, look at your own situation.

Eric Lansu

> Hi,
> Question :
> I know that maximum size of my datafiles is 2000M (Oracle 7.2.3 on HP-UX
> 10.20). But is there something as an optimal size ?
> I've heard some say not to make 'm bigger than 1000M, some say it should
> a multiple of 512K ...
> So, once and for all : what's the best way to size datafiles ?
> Greets,
> Kirsten
> --
> Author: Weerd de E.C. Kirsten
> 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: (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 Thu Jul 27 2000 - 08:58:25 CDT

Original text of this message