Re: Few big or many small datafiles per tablespace?
Date: Wed, 9 Jul 2008 07:40:09 -0700 (PDT)
Message-ID: <c65574e2-8f49-4d26-b435-4ba158a1fb2e@a70g2000hsh.googlegroups.com>
On Jul 8, 8:54 pm, Michael Austin <maus..._at_firstdbasource.com> wrote:
> Ronny wrote:
> > We are on 10.2.0.4 on Linux SLES-10 (64-bit) and we are analyzing the
> > IO setup of an OLAP/DWH system (about 800GB in total). We have a large
> > table of about 50 GB in size and it is stored in its own tablespace.
>
> > Now the question comes up whether few big (say 2 x 25GB) or many small
> > (say 25 x 2GB) datafiles are more beneficial. We have currently
> > DB_WRITER_PROCESSES=8.
>
> > Can there be only one file handle to access a datafile? Or can a
> > datafile be accessed by more than one db writer process? So dbw0 could
> > read at the beginning of a datafile, and dbw1 could read at the end at
> > the same time.
>
> > Any suggestions for a good datafile size independently from
> > DB_WRITER_PROCESSES?
>
> > Thanks for your comments,
> > Ronny
>
> How many spindles (or logical volumes - and hopefully not just
> partitions of the same devices).
>
> And yes there can be more than one process accessing datafiles at the
> same time. (parallel read/writes).
>
> Google/Metalink search for db_block_lru_latches and db_writer_processes.
>
> And 50G is NOT a big datafile.- Hide quoted text -
>
> - Show quoted text -
Actually depending on the Oracle bock size 50G may well exceed the largest file size that Oracle can support on a small file tablespace. With an 8K block I believe the limit is 32G while if you use a bigfile tablespace you can get up somewhere aroung 128T.
In my opinion fewer larger files would be better. During a checkpoint operation every file header has to be updated. During startup before opening the database Oracle has to check every file header. Generally speaker you want to use as few files as practical for housing your database. What is practical depends on your OS limitations which vary by version, the Oracle version, your backup startegy, your tape backup system limitations, and so on.
HTH -- Mark D Powell -- Received on Wed Jul 09 2008 - 09:40:09 CDT