Re: Datafile size - Is bigger better?
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: jaykash@xxxxxxxxxxx
- Date: Tue, 14 Nov 2006 16:49:41 -0800
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
systems
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
Other related posts:
