RE: Datafile size - Is bigger better?

Jay,    

    I'd say it sorta depends on the application(s) that your hosting and the hardware your using. Many a company is still not using any type of SAN/disk management system for one reason or another. Most of them don't make a lot of sense except that they just don't want to spend the money. Personally I think the day of the 2GB file max limit is a dinosaur that just won't lay down & die, like the 90+% cache hit ratio thing. On the other hand there are some good reasons to limit and not limit file size. I don't know if too many people are aware that Oracle imposes a limit of 1024 files per tablespace. If your like a client of ours that becomes a problem. They decided for various reasons that are history, that they'd limit files to 1GB. Well their SAP tablespace grew & grew till it hit that 1024 limit causing a significant "Keystone Cops" situation. Also Oracle imposes a limit of 2147483645 blocks per data file.which limits you to 16TB in an 8KB, 8TB with 4K and 4TB in a 2K system. Granted that still gives one a LOT of space to grow into, but their out there waiting to bite you at an in-opportune moment as noted above. Personally I find the argument about how big is your backup bucket much more compelling. Tapes aren't exactly cheap and the more of them that you have to search through the longer that recovery is going to take & the more heat your going to get.     

Dick Goulet, Senior Oracle DBA

45 Bartlett St Marlborough, Ma 01752, USA Tel.: 508.573.1978 |Fax: 508.229.2019 | Cell:508.742.5795

RGoulet_at_kanbay.com
: POWERING TRANSFORMATION  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of JayDBA Sent: Tuesday, November 14, 2006 5:57 PM To: oracle-l_at_freelists.org
Subject: Datafile size - Is bigger better?

Can I trigger a discussion on the pros and cons of datafile max-size. I am in a shop where the datafile size is limited to 2gb on a 64-bit platform (Raided) and I am trying to weigh the pros and cons of having a larger file limit.

Pros:
1. Quicker recovery
2. Easy to load balance  

Cons:
1. How often do we have to recover?
2. Is load balancing on the database level really an option for raided systems
3. On locally managed files, we loose header space equal to the extent size. E.g. on a datafile with uniform extent sizing of 128M a 2gb file would waste 6% space / file. This number can run into gigabytes on systems with 100's of 1000's of files
4. CKPT having to work more since it has to update many more smaller files.  

Regards    

--
http://www.freelists.org/webpage/oracle-l