Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Datafile size - Is bigger better?

Re: Datafile size - Is bigger better?

From: Jared Still <>
Date: Tue, 14 Nov 2006 16:49:41 -0800
Message-ID: <>

Hi Jay, comments below:

On 11/14/06, JayDBA <> 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.

> 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

Received on Tue Nov 14 2006 - 18:49:41 CST

Original text of this message