Hi Jay, comments below: On 11/14/06, JayDBA <jaykash@xxxxxxxxxxx> wrote:
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
Possibly. It depends on how your backups are created. If your database is backed up with RMAN and filesperset is default (60?) and the backup piece size (can't recall parameter) is 60Gig, at recovery time will it really matter if your backup file is 2 gig or 20 gig? The entire 60gig backup piece will be read. A solution to that is to set filesperset =1 when the backup is mode. Although this will improve recovery times where a single file is needed ( (it does happen you know), there is no free lunch. Setting Filesperset=1 will have a very noticiable effect on your backup times if going to tape. Keeping tape streaming is key to reducing backup times. If using virtual tape that is really disk, it probably won't matter so much at backup time if Filesperset = 1. 2. Easy to load balance
Forget about it. S.A.M.E. Striper and Mirror Everything. It really does work. Cons:
1. How often do we have to recover?
Ask how long is the longest acceptable time to do a recovery. 2. Is load balancing on the database level really an option for raided
See SAME above. 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
Seems kind of silly to have a 2Gig file that can only have 15 usable extents, doesn't it? 4. CKPT having to work more since it has to update many more smaller files.
Good point. Personally, I've become enchanted with the idea of setting datafiles to autoextend unlimited for application tablespaces. These are COTs apps, so it is very unlikely that anyone will extend the files to fill up the drive. This does not include SYSTEM, TEMP and UNDO tablespaces. -- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist