Re: 2GB or not 2GB (datafile limit)? That is the question.

Rich,

I don't know about OS issues, but we also stick to a max 2GB datafile size for a different reason. Almost all our servers (Linux and HPUX) are on one of our SANs and we've found that the OSs tend to give each mount point the same (on average) fraction of I/O bandwidth to the SANs. This was startlingly obvious when, at the recommendation of one of our SysAdmins, we put a Production database all under 1 mount point and its performance suffered greatly - no detailed analysis, it was just obvious. When we spread that same database across multiple mount points on the same server and SAN, performance improved dramatically.

So, we typically ask for about 16 mount points from about 14GB to 50GB each, depending on the server and SAN, and spread or databases evenly across those. Having 2GB datafiles makes it easy to move files around if we need to, as well as add datafiles evenly to keep the available space about the same on all mount points.

The 2GB limit is, as you've pointed out, due to past problems with larger file sizes on some platforms. We've stuck with it for the newer reasons I've outlined above, but there's no magic in the 2GB size other than it works nicely for us.

Actually, there's another reason. We frequently clone our Production databases back to Dev and Test on other servers. With the mount points on each server sized equally, we can copy the zipped up datafiles in parallel from Prod to their target mount points - saves time. Our LAN/WAN is all fibre, so network bandwidth is rarely an issue.

These may not fit your environment, but they're important to us.

Jack C. Applewhite - Database Administrator Austin (Texas) Independent School District 512.414.9715 (wk) / 512.935.5929 (pager)

 I feel so unnecessary. -- Rufus Thomas

               ( "Do the Funky Chicken")

"Jesse, Rich" <Rich.Jesse_at_qg.com>
Sent by: oracle-l-bounce_at_freelists.org
09/08/2006 09:18 AM
Please respond to
Rich.Jesse_at_qg.com

To
<oracle-l_at_freelists.org>
cc

Subject
2GB or not 2GB (datafile limit)? That is the question.

Many moons ago, way back in the 32-bit era when Y2K was a looming nightmare, I had instituted a policy that no Oracle datafile would be setup to grow larger than 2GB. This was due to some known bugs with files larger than 2GB on many platforms/filesystems at the time.

As I'm now looking at a vendor's ERP installation, I was about to reduce their max datafile size from 32GB to 2GB when I asked myself "Why?". Is there any valid sane reason to do this anymore? I do not expect the DB size to grow beyond a modest 100GB in the next two years. The server is an IBM P5 blade running AIX5.3 and using JFS filesystems. Other similar servers with other DBs (e.g. Sybase) currently handle db files in the 100's of GB with no problem.

I don't see any need to limit the datafile size to 2GB anymore. Anyone else?

TIA,
Rich
--

--

http://www.freelists.org/webpage/oracle-l Received on Fri Sep 08 2006 - 09:52:05 CDT