RE: Multiple Datafiles and performance?

Thanks for the knowledge dump everyone!
Based on your responses, I'll make the recommendation that our client go with 3 2.5G datafiles. They currently are 1 2.5G and 2 1G datafiles for the tablespace in question.
Most of our clients have less than 30 datafiles, and I doubt will find any over 50.
(slightly less than 1150 ...whew!) Index files are separated from data. The datafiles in question were data, and not index files, but they are on RAID5.

We got them add 2 more RAID1's, so they are up to 4 RAID1's and 1 RAID5.

We have the Indexes on a RAID1, the OS/Oracle on a RAID1, the remaining were on the RAID5, including RBS and REDO. We are moving the Redo Groups to the other RAID1's (one group on each). We could move the RBS to the OS/Oracle drive should contention is still an issue.

I've been walking around with "ORACLE 101 - Performance Tuning" duct taped to the back of my head for about 4 weeks now trying to absorb as much as I can. It's got me looking at every change we make and it performance consequences. The number/size of datafiles was one thing I couldn't track down much info about in relation to performance. We've changed the app to increase use of bind variables, so we're making progress. (It helps that we have an Oracle DBA on each development team). We're making a lot of progress, an the info from this list and the recommended reading has played a big part in helping me get, not just changes made, but the right changes made. Now, if I could just learn to make more focused and cohesive sentences and yeh,uh what I just said.

Once again, thanks for the help

David Phillips
Support DBA

-----Original Message-----
Sent: Thursday, August 07, 2003 10:45 AM To: Multiple recipients of list ORACLE-L

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Dave Phillips
  INET: dphillips_at_gasper-corp.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).