Re: What datafile size is best?

There are as many schools of thought here as there are DBAs. There used to be a

small limit on the number of files in a database (its' now 2**32 or something like that).
There also used to be a low limit on the number of extents for and individual segment
(table index etc). The limit now is "unlimited" (2**32).

Given these two items, the size of data files is mostly irrelavant. It depends on your O/S,
the size of your database, the size of the tables. I personally follow a scheme that was
first documented at Oracle OpenWorld (1995 I think). The presentation was called
"How To Stop Defragmenting and Start Living: The Definitive Word o n Fragmentation".
It was available through MetaLink last I checked.

Since I read this document (and did some testing), I use ONLY 3 different extents sizes.
They are 160K for small tables (under 160M), 5M for Medium sized tables (160M to

4GB and 160M for larger tables.

Although it's not from that article, I have settled on a few file sizes. 64008K, 128008K,
2560078K and 512008K. These two things allow all tables to fit exactly within a

tablespace, with no tablespace fragmentation or wastage EVER. I've been using this
technique for about 3 years on databases that range in size from 100M to 100+G.

The small file size allows me to move files around very easily and distribute I/O load if
neessary. This is less important now with faster disk drives and striping.

There is a very slight theoretical performance hit for this. However, I've never been
able to measure it.

"Randi Wølner" wrote:

> Someone told me that I should rather have a number of "small" datafiles than
> to have one big.
> I have planned to have one datafile for a tablespace of 8 G (I use Oracle
> 8.1.5 for Windows NT - but will move
> to Unix platform soon).
> Should I instead have 4 datafiles of 2 G each, or 8 files of 1 G??
>
> And what about tablespaces - I have planned to keep alle my application's
> data in one tablespace (the one of 8 G),
> would it be better to divide them into more tablespaces?
>
> Can anyone give me some recommendation on what is best (and why)?
>
> Thanks a lot,
> Randi Wølner
Received on Thu Jan 18 2001 - 21:50:38 CST