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: Multiple Datafiles and performance?

Re: Multiple Datafiles and performance?

From: Tim Gorman <tim_at_sagelogix.com>
Date: Thu, 07 Aug 2003 06:44:37 -0800
Message-ID: <F001.005C975D.20030807064437@fatcity.com>


Dave,

There is little about the size of datafiles to affect the performance of SQL statements, but there is much to affect the performance of backup and restore and administration.

Uniform-sized datafiles simplify the administration of space. The speed of a backup or restore is a function of the largest datafile, so although files can now be sized in petabytes, it is not a good idea to do so. Personally, I stick to a max size of 2-8 Gbytes, depending on overall database size. Far faster to backup/restore lots of smaller files than to have one 500Gb monster holding things up.

Also, if your storage subsystem isn't already implementing RAID-0 striping, then hand-striping multiple datafiles across volumes could help performance. Again, in that situation, many smaller uniform-sized files make the job easier than a few larger odd-sized files.

Hope this helps...

-Tim

on 8/6/03 1:14 PM, Dave Phillips at dphillips_at_gasper-corp.com wrote:

> Oracle 8.1.7.4
> 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 :)
>
> TIA
>
> David Phillips
> Support DBA
> Gasper Corp.
> BAARF member #30

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: tim_at_sagelogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Thu Aug 07 2003 - 09:44:37 CDT

Original text of this message

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