Re: Few big or many small datafiles per tablespace?

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Tue, 08 Jul 2008 19:54:47 -0500
Message-ID: <kZTck.15067$N87.7651@nlpi068.nbdc.sbc.com>


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. Received on Tue Jul 08 2008 - 19:54:47 CDT

Original text of this message