Is there a right size for data files?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Is there a right size for data files?

  1. #1
    Join Date
    Oct 2001
    Location
    Berlin, Germany
    Posts
    97

    Is there a right size for data files?

    Hello,

    we got a 1TB Oracle 11g Database with RAID5 for data file storage.
    Yes, I/O is our bottleneck but we have no chance to change the RAID.

    We got one TBS wiht a size of 250GB and circa 60 files each 4GB in size.
    Is there a gain, to have such a lot of files or does it not matter for I/O?
    I would prefer to have only 5 files with 50GB in size.
    It would be much easier to manage!

    What would you prefer?

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Cool

    IMHO, 4G file size seems too small, my personal preference for terabyte size db's is 32G file size. We did have a terabyte db with 64G file size and had many issues with the backups (this was 10g).

    Each expert will have it's own opinion about setting an "ideal" file size, but you may want to take into consideration the size of the physical cylinders/tracks/partitions of the raid array. On a SAN you would consider the size of the disk segments (logical partitions).

    Good luck!
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,014
    There was a time when I did scripted backups where I would put each table space in backup mode, copy the data files and gzip the copies. For that you don't want to let the data files go much above 8GB. But for a 1TB database with either 10g or 11g, I would think that 32GB would be a reasonable size for a data file.

    There is no perfect size, but if you are trying to reduce the overhead of physical I/O, look at your indexes. If you have indexes that are not being used, then drop them. Cleaning up your indexes and really thinking about what to index and how to index will lower the overall cost of every insert, update and delete that happens in that database.

    If you can boost your SGA you can reduce the amount of Physical I/O in favor of logical I/O, but tuning your queries to use less I/O will also make a big improvement.

    Ultimately if you data has value, and your SAN is not up to the task, someone will have to buy more capacity. but tune first and see how much you can improve the performance of your database.
    this space intentionally left blank

  4. #4
    Join Date
    Oct 2001
    Location
    Berlin, Germany
    Posts
    97
    Hello LKBrwn_DBA,

    thank you for your reply! I will talk about the size of disk segemnts with our administrator for storage devices.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width