Re: Few big or many small datafiles per tablespace?

From: Mark D Powell <>
Date: Fri, 11 Jul 2008 08:37:03 -0700 (PDT)
Message-ID: <>

On Jul 11, 9:44 am, Ronny <> wrote:
> Thanks for your comments so far.
> For my question I want to disregard aspects like
> - any sort of partitioning
> - managebility / administrability
> - backup and recovery
> - IO paths / physical disks / number of spindels
> (although these aspects are of course worth to be discussed in
> different threads).
> I want to set the focus just on performance. If I look at your
> comments I conclude that it is nearly the same (few big vs. many
> small). Maybe there's a bit of an advantage for fewer larger files due
> to checkpoint operations with the need to update every file header.
> But isn't it a mostly theoretical point of view? Is this time
> difference really measurable?
> Ronny

How measurable the time difference is for a checkpoint operation due to the number of database datafiles in use would depend on how big a difference exists between the number of files and how frequently you checkpoint on the two databases being compared. The storage unit characteristics would also effect the result.

Also using 4G files will not reduce recovery time in many cases as you will end up losing multiple datafiles instead of just a few larger files such as in the case where you lose the disk and its mirror or two disk in a RAID-5 stripe at one time. You will have to recover the entire stripe so recovering 6 4G files or one 24G file is going to take about the same amount of time.

Now in the case of human error where someone just removed the file the 4G would win, but very few people (only System Admin and DBA) should have access to the database server so this should never happen.

4G could be a very good choice for a maximum file size but the total database size 20G, 100G, 500G, etc .... and the maximum object size in the database 1G, 2G, or 40G should be taken into account.

To summarize, in my opinion, you want to make a choice based on the specific database total size, object size, and backup resources rather than having a set preference in chosing database file sizes which in turn determines due to total data space requirements how many datafiles your database has.

HTH -- Mark D Powell -- Received on Fri Jul 11 2008 - 10:37:03 CDT

Original text of this message