Is there a right size for data files?
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?
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).
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
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
thank you for your reply! I will talk about the size of disk segemnts with our administrator for storage devices.
Click Here to Expand Forum to Full Width