Re: Multiple Datafiles and performance?

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).