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: Guang Mei <gmei_at_incyte.com>
Date: Thu, 07 Aug 2003 08:24:24 -0800
Message-ID: <F001.005C9795.20030807082424@fatcity.com>


1). I don't know. But I always try to create datafile with same size (2G) and it is easier to estimate how many datafiles I can fit into one disk.

2). If all datafiles are on 1 disk, then 4 1G datafiles vs 2 2G. should not make much difference. If you could spread 4 1G datafiles into 4 different disks (mount points), then it is better.

3). I seem to read it from somewhere that Win2K has problem to handle datafile larger than 2G.

Guang

-----Original Message-----
Dave Phillips
Sent: Thursday, August 07, 2003 11:25 AM To: Multiple recipients of list ORACLE-L

...

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

Thanks for your patience and all the help. David Phillips
Support DBA
BAARF Member #30

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

Win2K. If you decide to increase the filesize, do it to more than 2G
(doesn't have to be by much). Of course, you didn't mention autoextend so
this may not be an issue.

Also, just how many physical disks do you have? Logical disks are not the issue. If you're going to get any increased performance you should be putting the second datafile on a second physical disk. Any how come you're not using some sort of RAID device (or don't you have your logical drives striped across your physical drives)?

There is no easy answer to your question without an understanding of the reality of your disk layout.

                      "Fermin Bernaus"
                      <fbernaus                To:      Multiple recipients
of list ORACLE-L <ORACLE-L_at_fatcity.com>
                      @sammic.com>             cc:
                      Sent by:                 Subject: RE: Multiple
Datafiles and  performance?
                      ml-errors


                      08/07/2003 08:05
                      AM
                      Please respond
                      to ORACLE-L







             In my experience, spreading datafiles across volumes

(specially if you are careful not to locate the a table's datafiles and its
indexes datafiles in the same drive) greatly increases performance.

             As for the file size, I can not say because I have not tested it, but I think it should have no real impact compared to splitting it. Reorganizing the database regularly is a better way to optimize performance.

..............................................
Fermín Bernaus Berraondo
Dpto. de Informática
SAMMIC, S.A.
fbernaus_at_sammic.com
http://www.sammic.com
Telf. +34 - 943 157 331
Fax +34 - 943 151 276
..............................................


-----Mensaje original-----
De: Dave Phillips [mailto:dphillips_at_gasper-corp.com] Enviado el: miércoles, 06 de agosto de 2003 22:14 Para: Multiple recipients of list ORACLE-L Asunto: Multiple Datafiles and performance?

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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus INET: fbernaus_at_sammic.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: Thomas Day INET: tday6_at_csc.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: gmei_at_incyte.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 - 11:24:24 CDT

Original text of this message

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