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: Bob <>
Date: Tue, 14 Nov 2006 18:37:52 -0500
Message-ID: <>

Hi Jay, without spending allot of time on the philosophy of datafile size here are some thoughts.

How is recovery quicker? if you have 10 - 2 gb files or 1 - 20 gb file in a tablespace or your entire database is make up of 100 - 2 gb files or 10 - 20gb files?

10 files to me seems like less moving parts ...

As far as load balancing I think the current mindset is the way storage is presented to us we "really" dont know where the datafile is going to land. So the datafile / disk relation is only a logical representation to the dba.

My question to you is, how much do your table spaces grow? If you use 2 gb per 6 months than 2 gb sounds reasonable, but if you use 40 gb per month... you will be adding allot of datafiles ( probably in the middle of the night)

We use 2,5,10,20 increments with no auto w\extend, that way we dont have to worry about maxing the mount point. For us that layout works best.

I believe having 1 size fits all is poor design, unless you have a very small database

My opinion


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
> 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

"Oracle error messages being what they are, do not
highlight the correct cause of fault, but will identify
some other error located close to where the real fault lies."

Received on Tue Nov 14 2006 - 17:37:52 CST

Original text of this message