Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Multiple Datafiles and performance?

RE: Multiple Datafiles and performance?

From: Stephen Lee <Stephen.Lee_at_DTAG.Com>
Date: Thu, 07 Aug 2003 07:14:24 -0800
Message-ID: <>

If by "multiple datafiles that ..." you mean for a single tablespace then:

It's possible to stripe a tablespace across drives "by hand", but it is no substitute for real, genuine hardware or software striping. I'm a little foggy on the "by hand" process since (I think) it is considered an archaic practice; but I'll give it try here. Through a process of creating a datafile, then creating a segment, then creating another datafile, then creating another segment or adding extents, etc. try to force the data to be as equally distributed as you can get it. It's a labor intensive process and requires constant fiddling to keep things in balance if the amount data in the database is constantly changing; and it works only if the data is accessed in a completely random manner. You must manually distribute the data equally among the data files and keep it equally distributed as things change. So *NOBODY* does it.

If by "multiple datafiles that ..." you mean multiple tablespaces with tables distributed among the tablespaces in such a way as to achieve balanced I/O (or reasonable facsimile thereof) then:

Hell yes!

Busting up tablespaces into multiple data files just to be busting them up results in a database that is a big turd to maintain; and you end up with your free space scattered all over hell and half of Texas.

And right now I have
in the CD drive. And it's on track 5:
Gay Gordons / Camerons/ Brown Haired Maiden / SCOTLAND THE BRAVE. I'm ready for a fight now. Anybody want to fight?!!

(No I'm not Scottish; but if it isn't Scottish it's still CRAP)

> Oracle
> Win2k
> What is the consensus on datafile sizing and the impact/overhead
> multiple datafiles have on performance?
> For example, if I have one 2.5g datafile, and three 1g
> datafiles, and I
> need more space, would it be better to increase the size of
> the 1g to
> 2g or add another 1g datafile?.
> Is it better to keep them all uniform in size?
> I would think having multiple datafiles that could be spread across
> drive volumes would be beneficial, am I wrong? (Wouldn't be the first
> time :)

Please see the official ORACLE-L FAQ:
Author: Stephen Lee
  INET: Stephen.Lee_at_DTAG.Com

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
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 Aug 07 2003 - 10:14:24 CDT

Original text of this message