Re: size of data files

kal121_at_yahoo.com wrote:
>
> Hello,
>
> What are some general guidelines for sizing datafiles? I've heard
> datafiles should not exceed 1GB in size. What are the pros and cons for
> having large vs. small datafiles, relatively speaking?

Fewer files make life a *little* easier for the DBA since backup scripts, reports etc. simply have fewer OS-level objects to manage, track etc. However, making files large makes life a little more difficult if you have to move a file to another device (may not have enough space) and if you lose a file, or a disk, it may take more time to recover a large file than a small one (all things being equal -- tho' of course recovery time also depends on how much activity against that file since the last backup etc.) We try to choose tablespace file sizes from a small set of options -- say, 50MB, 100MB, 300MB, 500MB, 750MB.

> Performance
> considerations? For example, if Oracle is searching for single row,
> will it find it faster in a smaller datafile?

Generally speaking performance has little to do with file size -- though again it depends. Indexed reads are done at the *block* level so the size of the file, or the number of files, is not relevant to a *single* read. Full table scans are done in groups of blocks, so again file size is not really relevant (it's more important to make extents an even multiple of init.ora parameter DB_FILE_MULTIBLOCK_READ_COUNT so that multiblock reads fall on extent boundaries.) However, individual transaction, and aggregate system, response times & throughput can be affected by how much I/O concurrency your database is achieving at the OS level. The trick here is to try to spread the total I/O load across as many physical I/O devices (disks, controllers, etc.) as possible. That may mean using more OS files (and so smaller ones) than otherwise so that they can be spread across multiple disks. --

Vincent Ventrone    |  The MITRE Corp.
DBA, Dept. R101     |  M/S C020
vav_at_mitre.org       |  202 Burlington Rd.
(781) 271-7048      |  Bedford, MA 01730