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: Wolfgang Breitling <>
Date: Thu, 07 Aug 2003 08:09:30 -0800
Message-ID: <>

The benefits of spreading the data over as many physical access paths ( ~ disks ) using multiple datafiles notwithstanding, there is always the case of too much. Keep in mind that at checkpoint time the DBWR need to visit the header of every ( non read-only ) datafile. That's unlikely to be an issue for a few dozen datafiles, but if you are getting into hundreds of them, keep that in mind. If you can get the striping done without multiple datafiles you get the best of both worlds. I am just suffering that exact issue on a test system for an upgrade with an extremely poor IO subsystem where bottlenecks like this get magnified.

At 07:24 AM 8/7/2003 -0800, you wrote:
>Gee, that question sounded a whole lot better when I wrote it yesterday
>than it did this morning when I saw it. :) Maybe I should be a little more
>The problem is there are a couple of things I am trying to accomplish. We
>have clients that use our application that have specific performance
>issues which I am working to improve. The other issue is to provide
>recommendation to development/tech staff on initial setup of
>database/tablespaces/datafiles etc.., along with hardware recommendations
>for our application.
>So, that being said, I'll try and ask better questions.
>The environment is W2K, Oracle or higher
>All tablespaces are LMT
>Most disk config's are 1 (or 2) Raid 1 along with a Raid 5 for basic
>Most operate application 24/7
>1) Is there any advantage to uniform datafile sizes?
>2) Is there any advantage/disadvantage for say 4 1G datafiles vs 2 2G.
>(Other than time to recover from datafile loss)
>It is probably safe to assume that the datafiles exist on a RAID 5. (for
>3) Why the recommendation to take a Win2k datafile to just over 2G?
>For future apps I am pushing for optimal recommendations that go for more
>raid 1 sets or raid 10 over the Raid 5. This should allow for more
>flexibility for spreading out the i/o.

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation

Please see the official ORACLE-L FAQ:
Author: Wolfgang Breitling

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 - 11:09:30 CDT

Original text of this message